Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
1  
RichardTheKiwi's answer looks good. However, I would check if populating a 8 million rows (temporary?) table from a 8 million rows (temporary?) table is really what you want to do. You probably want to process the results further and the temp table looks like a waste of resources. The same results can likely be achieved querying the original table(s) directly. –  spaghettidba Apr 4 '13 at 7:57
add comment

1 Answer

up vote 5 down vote accepted

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:

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),
  PRIMARY KEY(CASE_ID, SEQ_NUM)
); -- don't leave an 8 million record table in a heap!

Since all you need is merely to number the records within a CASE from 1 to N, you can use ROW_NUMBER().

ALTER PROCEDURE [dbo].[B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP2_SP]
AS
  SET NOCOUNT ON;
  TRUNCATE TABLE B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP;

  INSERT INTO B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP       
  SELECT ROW_NUMBER() OVER (PARTITION BY CASE_ID ORDER BY AUDIT_STAMP),
         CASE_ID, ASSIGNED_TO, AUDIT_STAMP, AUDIT_OPRID, RC_STATUS, PROVIDER_GRP_ID      
    FROM B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP_T2;
GO
share|improve this answer
    
Wow, completed in 4 min. Thanks a ton!! –  yogirk Apr 4 '13 at 8:07
    
That leads me to think that your source table, ..TEMP_T2 is actually also on a HEAP without a CLUSTERED KEY... –  孔夫子 Apr 4 '13 at 8:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.