Vryjdfkk

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.

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.