05-24-10, 13:28 #1Registered User
- Join Date
- May 2010
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.
declare @P1 int
declare @P2 int
declare @P3 int
declare @P4 int
declare @P5 int
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
05-24-10, 13:57 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
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
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.