SQL Server Performance Forum – Threads Archive
Slow query with cursorThe following cursor when sp_cursorfetch is executed gives Duration of more than 160000 ms and > 70 million Read in profiler, the tables involved have less than 2000 records each. Any Idea ? declare @P1 int
declare @P2 int
declare @P3 int
declare @P4 int
declare @P5 int
exec sp_cursorprepexec @P1 output, @P2 output, NULL,
CARTON_HDR.CARTON_NBR FROM LOCN_HDR WITH (NOLOCK),
CARTON_HDR WITH (NOLOCK),
ALLOC_INVN_DTL WITH (NOLOCK)
WHERE ( ( ( ( ALLOC_INVN_DTL.INVN_NEED_TYPE = 50 )
AND ( ALLOC_INVN_DTL.CARTON_NBR = CARTON_HDR.CARTON_NBR ) )
AND ( ALLOC_INVN_DTL.PULL_LOCN_ID = LOCN_HDR.LOCN_ID ) )
AND ( ( ALLOC_INVN_DTL.WHSE = ”ADF” )
AND ( ( ALLOC_INVN_DTL.STAT_CODE = 0 )
AND ( ( ( ALLOC_INVN_DTL.TASK_GENRTN_REF_NBR = ”200303060752” )
AND ( ALLOC_INVN_DTL.TASK_GENRTN_REF_CODE = ”4” ) )
OR ( ( ALLOC_INVN_DTL.TASK_GENRTN_REF_NBR = ”200303060752” )
AND ( ALLOC_INVN_DTL.TASK_GENRTN_REF_CODE = ”1” ) ) ) ) ) )
ORDER BY CARTON_HDR.CARTON_NBR ASC, LOCN_HDR.LOCN_PICK_SEQ ASC
OPTION (FAST 1)’, @P3 output, @P4 output, @P5 output
select @P1, @P2, @P3, @P4, @P5
1. You are using a cursor. SQL is a set-based language.
2. You have an OR clause in the WHERE clause which will slow things down considerable.
3. Have you looked at the execution plan to see if any table scans are being done? My guess is you are. They OR statement is an automatic table scan. You probably have a few more besides that.
4. You are using dynamic SQL. All in all, it’s probably about the slowest way you could run this particular query. If you post CREATE TABLE and INSERT statements, then tell us what you want for outcome, maybe we can help you out. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.