Slow query with cursor | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Slow query with cursor

The 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
set @P1=316
declare @P2 int
set @P2=180150345
declare @P3 int
set @P3=4
declare @P4 int
set @P4=8
declare @P5 int
set @P5=-1
exec sp_cursorprepexec @P1 output, @P2 output, NULL,
N’SELECT
ALLOC_INVN_DTL.ALLOC_INVN_DTL_ID,
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
Omar Olafsson
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.
]]>