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_FIELDScontains 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.
- 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.