Início / dba / Perguntas / 39208
Accepted
RK Kuppala
Asked: 2013-04-04 23:11:50 +0800 CST

Precisa de ajuda para converter este cursor em uma operação baseada em conjunto

  • 772

Eu tenho um procedimento armazenado que é executado como parte de uma atualização de banco de dados de relatório todas as noites, que leva cerca de 2 horas para ser concluída. O objeto B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP_T2tem 8 milhões de linhas. Você pode sugerir como posso converter esse cursor em uma abordagem baseada em conjunto para melhor desempenho? Um CTE pode ser usado?

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
sql-server t-sql

1 respostas

  1. Best Answer

    Por que você precisa DROP e RECRIAR a tabela toda vez? Como você está fazendo um DROP-CREATE, ele já deve existir. Portanto, fora do SP, recrie-o da seguinte maneira:

    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!

    Como tudo o que você precisa é apenas numerar os registros dentro de um CASE de 1 a N, você pode usar 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
    • 6

relate perguntas