SQL Server Performance Forum – Threads Archive

With – Without cursor.

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

---

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

---

]]>