SQL Server Performance

Slow query with cursor

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by oolafsson, Jan 2, 2005.

  1. oolafsson New Member

    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
  2. derrickleggett New Member

    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
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.

Share This Page