SQL Server Performance

With - Without cursor.

Discussion in 'T-SQL Performance Tuning for Developers' started by Luis Martin, Oct 31, 2003.

  1. Luis Martin Moderator

    Again from spanish forum.
    What are recomendations from performance point of view.


    CODIGO VIEJO. (OLD )

    DECLARE CurAsignados CURSOR FOR
    SELECT Tmp_NroOdt
    FROM #Tmp_Reclamos
    Where Tmp_Asignado > 0

    OPEN CurAsignados FETCH NEXT FROM CurAsignados INTO @NumeroODT
    WHILE (@@fetch_status <> -1 )
    Begin
    Select @AsigError = @AsigError + '//' + Rtrim(@NumeroODT)
    FETCH NEXT FROM CurAsignados INTO @NumeroODT
    End
    Delete From #Tmp_Reclamos Where Tmp_Asignado > 0

    Select @AsigError = '6012|' + @AsigError
    Deallocate CurAsignados



    CODIGO NUEVO. (NEW)

    SET @AsigError = ''
    -- Se reemplaza el Cursor. (Cursor replace)

    SELECT @NumeroODT = ISNULL(MIN(Tmp_NroOdt),'') -- Segundo Control de Posible Asignacion a Usuario
    FROM #Tmp_Reclamos (Nolock)
    WHERE Tmp_Asignado > 0

    WHILE @NumeroODT <> ''
    BEGIN
    Select @AsigError = @AsigError + '//' + Rtrim(@NumeroODT)

    DELETE FROM #Tmp_Reclamos
    WHERE Tmp_Asignado > 0
    AND TMP_NroODT = @NumeroODT

    SELECT @NumeroODT = ISNULL(MIN(Tmp_NroOdt),'')
    FROM #Tmp_Reclamos (Nolock)
    WHERE Tmp_Asignado > 0

    END

    IF @AsigError <> ''
    Select @AsigError = '6012|' + @AsigError



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  2. joechang New Member

    the general idea is to replace a cursor that loops through a set of rows with a single operation that operates on a set of rows.
    replacing a cursor with a while loop is still a loop, and prossibly a less efficient loop even though the plan may show a scan for the SELECT from the cursor.

    in the first example, there is 1 table operator (SELECT) in the cursor, followed by a set operation after the cursor,
    in the second, there are 2 table operations within the loop, 1 SELECT, 1 DELETE
    hence this is a step backwards in terms to replacing single row operation loops with a single SET operation

Share This Page