SQL Server Performance

Cursor issue

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Alfonso Calderon, Mar 23, 2007.

  1. Alfonso Calderon New Member

    I have Invoices table and a Quotes table, each record in the quotes table has a fixed amount quote, I have a cursor that updates each quote depending of the total of the invoice amount, and if one quotes has to be splitted in order for it to, grouped with the other ones, sums the total of the invoice amount, it has to be done, the process works fine but it takes a long time in the execution.
    Here is the code:
    DECLARE TCUR CURSOR FOR
    SELECT IDCUOTA, CUOTA
    FROM CUOTAST WHERE IDSTATUS=1 AND IDBIDS=@IDBIDS AND IDFRANJA=@IDFRANJA

    OPEN TCUR
    FETCH NEXT FROM TCUR INTO @IDCUOTA, @CUOTAT

    WHILE @@FETCH_STATUS=0 BEGIN

    IF @TOTFACT<@CUOTAT BEGIN
    SET @DIF=@TOTFACT

    UPDATE CUOTAST SET IDFACTURA=@IDFACTURA, IDSTATUS=3 WHERE IDBIDS=@IDBIDS AND IDFACTURA IS NULL AND IDCUOTA=@IDCUOTA AND IDSTATUS=1

    INSERT INTO CUOTAST
    SELECT @IDCUOTA + 'A', IDUNIDAD, FECHAAFP, IDMARCA, IDBIDS, IDFRANJA, IDTIPOVEHICULO, @IDCUOTA, @DIF, IDFACTURA, 2, COMENTARIOS, IDMODIFICACION
    FROM CUOTAST WHERE IDCUOTA=@IDCUOTA


    INSERT INTO CUOTAST
    SELECT @IDCUOTA + 'A', IDUNIDAD, FECHAAFP, IDMARCA, IDBIDS, IDFRANJA, IDTIPOVEHICULO, @IDCUOTA, @CUOTAT - @DIF, NULL, 1, COMENTARIOS, IDMODIFICACION FROM CUOTAST WHERE IDCUOTA=@IDCUOTA
    BREAK
    END
    IF @TOTFACT> @CUOTAT BEGIN
    IF @SUBT < @TOTFACT BEGIN
    IF @DIF > @CUOTAT BEGIN
    UPDATE CUOTAST SET IDFACTURA=@IDFACTURA, IDSTATUS=2 WHERE IDBIDS=@IDBIDS AND IDFACTURA IS NULL
    AND IDCUOTA=@IDCUOTAAND IDSTATUS=1
    SET @SUBT=@SUBT + @CUOTAT
    SET @DIF=@TOTFACT-@SUBT
    END ELSE
    IF @DIF < @CUOTAT BEGIN
    UPDATE CUOTAST SET IDFACTURA=@IDFACTURA, IDSTATUS=3 WHERE IDBIDS=@IDBIDS AND IDFACTURA IS NULL
    AND IDCUOTA=@IDCUOTA AND IDSTATUS=1
    INSERT INTO CUOTAST
    SELECT @IDCUOTA + 'A', IDUNIDAD, FECHAAFP, IDMARCA, IDBIDS, IDFRANJA, IDTIPOVEHICULO, @IDCUOTA,
    @DIF, IDFACTURA, 2, COMENTARIOS, IDMODIFICACION
    FROM CUOTAST WHERE IDCUOTA=@IDCUOTA

    INSERT INTO CUOTAST
    SELECT @IDCUOTA + 'A', IDUNIDAD, FECHAAFP, IDMARCA, IDBIDS, IDFRANJA, IDTIPOVEHICULO, @IDCUOTA, @CUOTAT - @DIF, NULL, 1, COMENTARIOS, IDMODIFICACION
    FROM CUOTAST WHERE IDCUOTA=@IDCUOTA
    BREAK
    END
    END
    END
    FETCH NEXT FROM TCUR INTO @IDCUOTA, @CUOTAT
    END
    CLOSE TCUR
    DEALLOCATE TCUR

    Can some one show some pointers in doing without the cursor?
  2. MohammedU New Member

    Try using inserting your CURSOR select into temp table and join the temp table for update and insert
    OR

    Try to figure it out the all condions and change your WHERE clause in your update and insert...like the following once...
    UPDATE CUOTAST SET IDFACTURA=@IDFACTURA, IDSTATUS=3
    WHERE IDBIDS=@IDBIDS AND IDFACTURA IS NULL
    AND CUOTA> @TOTFACT AND IDSTATUS=1
    AND IDFRANJA=@IDFRANJA

    INSERT INTO CUOTAST
    SELECT IDCUOTA + 'A', IDUNIDAD, FECHAAFP, IDMARCA, IDBIDS, IDFRANJA,
    IDTIPOVEHICULO, @IDCUOTA, @TOTFACT, IDFACTURA, 2, COMENTARIOS, IDMODIFICACION
    FROM CUOTAST WHERE IDFRANJA=@IDFRANJA AND CUOTA> @TOTFACT AND IDFRANJA=@IDFRANJA


    MohammedU.
    Moderator
    SQL-Server-Performance.com

Share This Page