>I want to bounce the idea off of all of you and possibly get some pointers for improving performance.
>
>I wrote a Windows Service that host a Windows Workflow application which is responsible for archiving 10 million records. This job is performed as a schedule every so often and although 10 million records is not the norm' it is a good possibility that we could have as many to archive.
>
>So the application goes through and marks the archive status field from "not archived" to "archiving". The query used to determine which records are ready for archiving takes waaaaaaaaaayyy too long to try to update all 10 million at once. And in addition, this would be running on production data which would begin to potentially create dead-locks. So I've setup to update "Top(n)". At first, the update was updating 40 to 50 thousand records every 30 seconds - as the process moved along the records being updated began to dwindle down to about 10 thousand records every 30 seconds. I started with updating TOP(10,000) records but soon enough i began running into timeouts within my code. I'm down to updating TOP(1,000) at a time which has completed successfully but lasted 8 hours to process.
>
>The query used to determine records to mark for "archiving":
>
>SET @sqlCmd = 'UPDATE TOP(1000) z SET ArchiveState = ' + CAST(@ArchiveState AS VARCHAR(5))
>SET @sqlCmd = @sqlCmd + ' FROM ' + @MatchDb + '..msb'
>SET @sqlCmd = @sqlCmd + ' INNER JOIN ' + @CoreDb + '..b on msb.BatchId = b.BatchId '
>SET @sqlCmd = @sqlCmd + ' INNER JOIN ' + @MatchDb + '..mmm on mmm.MatchSetId = msb.MatchSetId and mmm.DisplayCol = msb.DisplayCol'
>SET @sqlCmd = @sqlCmd + ' INNER JOIN ' + @MatchDb + '..mmd on mmm.MatchmadeId = mmd.MatchmadeId'
>SET @sqlCmd = @sqlCmd + ' INNER JOIN ' + @MatchDb + '..' + @BatchTableName + ' z on z.Match_TxnId = mmm.TxnId'
>SET @sqlCmd = @sqlCmd + ' WHERE msb.MatchSetId = ' + CAST(@MatchSetId as varchar(5))
>SET @sqlCmd = @sqlCmd + ' AND msb.DisplayCol = ' + CAST(@DisplayCol as varchar(5))
>SET @sqlCmd = @sqlCmd + ' AND z.ArchiveState = 0'
>SET @sqlCmd = @sqlCmd + ' AND mmd.DateOrig > ''' + CONVERT(CHAR(10), @AgedDate, 101) + ''''
>
>EXEC(@sqlCmd)
>
>Thanks in advance for your help.

Hi Victor,

It may be not very helpful, but instead of constructing your whole string you may try to use sp_executeSQL and pass all your original parameters as is.