I have a table "OFICIAL3" with 500k rows. and 30 columns. and table INSIS with 150k rows and 20 columns. OFICIAL3.NUMERO_TITULO has an index. INSIS.NumeroDocumento has an index too. update sentence开发者_如何转开发 take long time. this process will take 9 hours in my machine my machine is a core 2 duo 2.GHZ and 2GB RAM
ALTER PROCEDURE [dbo].[CompletarDatos] AS
declare @cantidad int;
declare @CONTADOR int;
declare @NRO_TITULO VARCHAR(600);
declare @POYECTO VARCHAR(200);
DECLARE @I_PROYECTO VARCHAR(500);
DECLARE @I_AREA_INT VARCHAR(500);
SET NOCOUNT ON
BEGIN
SET @cantidad =(select count(*) from OFICIAL3)
SET @CONTADOR=1
declare CURSORITO cursor for
select NUMERO_TITULO from OFICIAL3
open CURSORITO
fetch next from CURSORITO
into @NRO_TITULO
while @@fetch_status = 0
begin
SET @CONTADOR=@CONTADOR+1
PRINT 'ROW='+CONVERT(NVARCHAR(30),@CONTADOR)+' NRO TITULO='+@NRO_TITULO
SET @I_PROYECTO = (SELECT PROYECTO FROM INSIS WHERE NumeroDocumento=@NRO_TITULO)
SET @I_AREA_INT = (SELECT I_AREA_INTERVENCION FROM INSIS WHERE NumeroDocumento=@NRO_TITULO)
UPDATE OFICIAL3 SET PROYECT=@I_PROYECTO , COD_AREA=@I_AREA_INT WHERE NUMERO_TITULO=@NRO_TITULO
fetch next from CURSORITO into @NRO_TITULO
end
-- cerramos el cursor
close CURSORITO
deallocate CURSORITO
END
Assuming OFICIAL4 is a typo, this should work as a single update:
UPDATE o
SET PROYECT = i.PROYECTO,
COD_AREA = i.I_AREA_INTERVENCION
FROM OFICIAL3 o
INNER JOIN
INSIS i
ON o.NUMERO_TITULO = i.NumeroDocumento
As others have commented, an approach that avoids the CURSOR is vastly preferable from a performance point of view. Another thought is that a covering index on `INSIS (NumeroDocumento, PROYECTO, I_AREA_INTERVENCION) would speed things up further for this query.
Is there any way you can do this without a cursor? Removing the iteration should help it considerably.
精彩评论