question

yogirk avatar image
yogirk asked

Help converting a cursor to a set based query

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
stored-procedurestsqlcursorset-based
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
It looks as though you'r eonly doing that CURSOR stuff because you need to get a sequence number, yes? In which case, try looking into [ROW_NUMBER()][1]. Something like SELECT ROW_NUMBER() OVER (PARTITION BY CASE_ID ORDER BY AUDITSTAMP) AS SEQ, CASE_ID, ASSIGNED_TO, AUDIT_STAMP, AUDIT_OPRID, RC_STATUS, PROVIDER_GRP_ID FROM B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP_T2 [1]: http://msdn.microsoft.com/en-GB/library/ms186734.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mz1derful avatar image mz1derful commented ·
I agree -- and it will be much quicker.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.