Results 1 to 2 of 2
  1. #1
    Join Date
    May 2010
    Posts
    6

    Unanswered: sp_cursorprepexec executes too slow

    Hi. I have store procedure which works fine in test environment but executes too slow in production. We have MS SQL sever 8.00.760, SP3 in test and 8.00.2040, SP4 in production. Find below code for reference.

    Any help!


    declare @P1 int

    set @P1=-1

    declare @P2 int

    set @P2=0

    declare @P3 int

    set @P3=28688

    declare @P4 int

    set @P4=8193

    declare @P5 int

    set @P5=102

    exec sp_cursorprepexec @P1 output, @P2 output, N'@P1 decimal(38,0),@P2 decimal(38,0)', N'SELECT PARM_SEQ, MESSAGE_PARM
    FROM PS_MESSAGE_LOGPARM (READUNCOMMITTED)
    WHERE PROCESS_INSTANCE = @P1
    AND MESSAGE_SEQ = @P2', @P3 output, @P4 output, @P5 output, 983843, 1

    select @P1, @P2, @P3, @P4, @P5


    Thanks,
    Best Regards,
    kumar.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First of all, running different versions of SQL between dev and production is emphatically NOT a good idea, especially when you are running SQL 2000.

    How big are your databases (dev versus production)? Do you regularly do maintenance such as updating statistics and rebuilding badly fragmented indicies? What version or versions of PeopleTools are you running? How many rows are in your production dbo.PS_MESSAGE_LOGPARM table?

    As a "first step" in triage, I'd recommend that you use Query Analyzer to execute:
    Code:
    UPDATE STATISTICS dbo.PS_MESSAGE_LOGPARM
    in your production database to see if that will improve your query performance.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •