-1

I am selecting some columns from a table, then matching these columns' fields with another table to retrieve some data. The code(with the indexes on respective tables) is as follows:

CREATE CLUSTERED INDEX CI_#Table1
ON Table1(
             VarcharCol1
            ,VarcharCol2    
            ,VarcharCol3
            ,VarcharCol4
            ,NVarcharCol
         )

CREATE CLUSTERED INDEX CI_#Table3
ON Table3(
              date
            ,VarcharCol1
            ,VarcharCol2    
            ,VarcharCol3
            ,VarcharCol4
            ,NVarcharCol
            ,DecCol
         )

DECLARE CUR1 CURSOR
FOR SELECT   VarcharCol1
            ,VarcharCol2    
            ,VarcharCol3
            ,VarcharCol4
            ,SUM(IntCol) IntCol
            ,SUM(DecCol) DecCol
            ,NVarcharCol
    FROM Table1 WITH (READUNCOMMITTED)
    WHERE date= @date
    AND IntCol IS NOT NULL
    GROUP BY VarcharCol1,VarcharCol2,VarcharCol3,VarcharCol4,NVarcharCol
OPEN CUR1

FETCH NEXT FROM  CUR1
    INTO @VarcharCol1,@VarcharCol2,@VarcharCol3,@VarcharCol4,@IntCol,@DecCol,@NVarcharCol

WHILE (@@FETCH_STATUS = 0)
BEGIN
            INSERT INTO Table2
            SELECT TOP (@IntCol) *
            FROM Table3 a WITH(READUNCOMMITTED)
            WHERE date      = @date
            AND VarcharCol1 = @VarcharCol1
            AND VarcharCol2 = @VarcharCol2              
            AND VarcharCol3 = @VarcharCol3
            AND VarcharCol4 = ISNULL(@VarcharCol4,'')
            AND NVarcharCol = ISNULL(@NVarcharCol,NVarcharCol)
            ORDER BY DecCol DESC

    FETCH NEXT FROM  CUR1
    INTO @VarcharCol1,@VarcharCol2,@VarcharCol3,@VarcharCol4,@IntCol,@DecCol,@NVarcharCol
END

CLOSE CUR1
DEALLOCATE CUR1

Now, the query that is inserting data into Table2 from Table3 above is causing a CXPacket wait type. I have optimized and optimized and find myself befuddled now. Please help!

4
  • 4
    Why not just remove the cursor and change it to one set-based query instead?
    – Brent Ozar
    Jan 12, 2017 at 13:47
  • 2
    CXPACKET waits are normal for a parallel query but excessive parallelism may be an indicator of the need for index and query tuning. Besides removing the cursor, have you indexed any of the columns in the WHERE clause? Add DDL to your question.
    – Dan Guzman
    Jan 12, 2017 at 14:12
  • 2
    What evidence do you have that you need to remove CXPACKET waits? Just because there are CXPACKET waits does not mean they are a problem. Often they are just an indicator that parallelism is working. Jan 12, 2017 at 14:15
  • @BrentOzar I have implemented the change that you recommended and subsequently, the query now executes in one fourth of the time that it took before. Thanks! Jan 12, 2017 at 15:16

1 Answer 1

1

First of all, try to rework your query and avoid using cursor. I believe this will improve performance.

Also maybe it will be useful for you: try to add OPTION (MAXDOP 1) to your query. I don't know your SQL Server configuration, but you can play with this option.

1
  • 1
    How would maxdop 0 prevent parallelism? Jan 12, 2017 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.