Low performance with dynamic queries and cursors

Question

Doing some legacy work, I found the following procedure that used to take between 1.5 to 6 hours, totally not expected when dealing with less than 2 million records. After the change it takes less than 3 minutes.

Why this script was taking too much time?

The script extracts only one value from one field, between 20 and 40 different sources, each source has from 100 to 100,000's records. Total records at the destination table is up to from half million to 2 millions. The table SOURCE_TABLE_FIELDS contains the table names, fields and criteria to build the dynamic queries.

DECLARE source_c CURSOR FOR   --cursor starts here 
SELECT table_name, field_name, condition
FROM SOURCE_TABLE_FIELDS  

OPEN source_c  

FETCH NEXT FROM source_c   
INTO @table, @field, @condition;

WHILE @@FETCH_STATUS = 0  
    BEGIN  

        PRINT 'DEBUG CHECKPOINT: START LOOP'

        declare @sql as nvarchar (1000)
        declare @sql_source as nvarchar(1000)

        set @sql_source = 'select ' + @field + ' as SOURCE_FIELD from ['  + @table + '] A '
                        + ' WHERE 1=1 ' + 
                        + @condition + 
        'EXCEPT SELECT DISTINCT DEST_FIELD FROM ' + @DESTINATION;
        set @sql = 'INSERT INTO ' + @DESTINATION + '(DEST_FIELD) ' + @sql_source;

    EXEC SP_EXECUTESQL @SQL
    PRINT 'DEBUG CHECKPOINT: END LOOP' 
    FETCH NEXT FROM source_c INTO @table, @fild, @condition;                 
    END
CLOSE source_c;  
DEALLOCATE source_c;

Look at the PRINT 'DEBUG' marks; sometimes the last line printed was from the end, sometimes the one at the start. It could take up to an hour to jump. (I checked this by printing timestamps and everything).

By sometimes I mean, usually after the first 10 loops, randomly. No matter if I sorted out the sources with small records at first or at last.

Important remarks:

• I don't have permission to access the SQL-Profiler
• I don't have access to sys.dm_exec_query_stats or any other high level stuff.
• I'm not a DBA, only a developer but with tons of SSIS work lately.

Answer 1

If you are considering using cursors in SQL Server, it may be helpful to use arguments that best fit your use case, as they may significantly change the performance of the cursor.

Conveniently, there have been many performance studies on these arguments in blog posts to look at. All possible arguments can be found for the current SQL Server version here: DECLARE CURSOR (Transact-SQL)

My favorite for cases similar to yours is:

DECLARE CURSOR LOCAL FAST_FORWARD

LOCAL tells SQL Server that the data set your working with is the only one it should care about. FAST_FORWARD provides flexibility to choose between static and dynamic SQL (though one downside is that it inhibits parallelism). One example of why these two arguments matter when determining cursor performance can be found here: Using SQL Server cursors – Advantages and disadvantages

Aside from this, large data sets tend to perform better, and are more accurate by using set based operations. This is due to two things: the nature of changing data sets/the limitations of cursors when data changes, and the inability to take advantage of parallelism which could severely cripple performance. Like Peter and I mention in the comments below this answer, fully testing and understanding cursor options, or not even using a cursor at all, could drastically change not only your query performance, but your consistency.

Answer 2

You've written the SQL using an EXCEPT DISTINCT clause, when you would likely have much better luck adding a NOT EXISTS to your WHERE clause. Essentially you are adding rows to the destination table where they do not already exist there.