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
Best Answer
Why do you need to DROP and RECREATE the table each time? Since you're doing a DROP-CREATE, it must already exist. So, outside of the SP, recreate it as follows:
Since all you need is merely to number the records within a CASE from 1 to N, you can use ROW_NUMBER().