performance problem sql server 2005 update sentence

Question

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

Answer 1

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.

Answer 2

Is there any way you can do this without a cursor? Removing the iteration should help it considerably.