question

I have a stored procedure which runs as part of a report database refresh nightly which takes about 2 hours complete. The object `B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP_T2` has 8 million rows. Can you suggest how can I convert this cursor to a set based approach for better performance? Can a CTE be used? ALTER PROCEDURE [dbo].[B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP2_SP] AS SET NOCOUNT ON DECLARE @SEQ SMALLINT, @CASEID DECIMAL, @ASSIGNED NCHAR(30), @AUDITSTAMP DATETIME, @AUDITOPRID NCHAR(60), @STATUS NCHAR(10), @LASTCASEID DECIMAL, @PROVGRPID NCHAR(20) SET @SEQ = 1 SET @LASTCASEID = @CASEID DROP TABLE B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP CREATE TABLE B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP ( SEQ_NUM SMALLINT, CASE_ID DECIMAL, ASSIGNED_TO NCHAR(30), AUDIT_STAMP DATETIME, AUDIT_OPRID NCHAR(60), RC_STATUS NCHAR(10), PROVIDER_GRP_ID NCHAR(20) ) DECLARE AUDIT CURSOR FOR SELECT CASE_ID, ASSIGNED_TO, AUDIT_STAMP, AUDIT_OPRID, RC_STATUS, PROVIDER_GRP_ID FROM B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP_T2 -- Open the cursor OPEN AUDIT FETCH NEXT FROM AUDIT INTO @CASEID, @ASSIGNED, @AUDITSTAMP, @AUDITOPRID, @STATUS, @PROVGRPID WHILE @@FETCH_STATUS = 0 begin INSERT INTO B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP SELECT @SEQ, @CASEID, @ASSIGNED, @AUDITSTAMP, @AUDITOPRID, @STATUS, @PROVGRPID SET @LASTCASEID = @CASEID FETCH NEXT FROM AUDIT INTO @CASEID, @ASSIGNED, @AUDITSTAMP, @AUDITOPRID, @STATUS, @PROVGRPID SET @SEQ = CASE WHEN @CASEID <> @LASTCASEID THEN 1 WHEN @CASEID = @LASTCASEID THEN @SEQ + 1 END end -- Close and deallocate the cursor CLOSE AUDIT DEALLOCATE AUDIT