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.


    SELECT Tmp_NroOdt
    FROM #Tmp_Reclamos
    Where Tmp_Asignado > 0

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

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


    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 <> ''
    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


    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