Is there a way to avoid this loop in SQL Server 2008 R2?

Question

I've often seen that expert users advise to try avoid loops at database level (Reference here). I have a short block of code where i can't see other way to achieve the task without the use of a loop. The task is very simple but is there a way to avoid the loop?

DECLARE @id INT = 1
DECLARE @auxId INT

WHILE @id IS NOT NULL
BEGIN
    SET @auxId = @id
    SELECT @id = id_next_version FROM task WHERE id_task = @id
END

SELECT @aux

Explanation of the code:

I have a table where there are tasks and some rows are updates of other tasks, so I have a column where are the id of the next version. What I want is to find the id of the last version of a task.

EDIT:

Table structure

CREATE TABLE task
(
    id_task INT IDENTITY(1,1) NOT NULL,
    task NVARCHAR(50) NULL,
    id_next_version INT NULL
)

Gordon Linoff · Accepted Answer · 2015-01-07T20:24:18.933

4

You are traversing a graph -- probably a tree structure actually. You can do this with a recursive CTE:

with cte as (
    select id_task, id_next_version, 1 as lev
    from task
    where id_task = @id
    union all
    select t.id_task, t.id_next_version, cte.lev + 1
    from task t join
         cte
         on t.id_task = cte.id_next_version
   )
select top 1 *
from cte
order by lev desc;

I'm not sure that this is more elegant than your loop. It should be an iota faster because you are only passing in one query.

Here is a SQL Fiddle illustrating the code.

edited Jan 07 '15 at 20:24

answered Jan 07 '15 at 19:53

Gordon Linoff

1,242,037
58
646
786

I just run your code, but returns me nothing. Any hints? – smartdan Jan 07 '15 at 20:04
@DanielGonzalez . . . It works both in SQL Fiddle and in sample data on a local instance of SQL Server. – Gordon Linoff Jan 07 '15 at 20:13
In my experience this can be much more than an iota faster. This is essentially moving the loop into a loop join. Much tighter body of that loop. – usr Jan 07 '15 at 20:20
Correct me if I'm wrong, but this seems to return the id_next_version, and I want the id_task. – smartdan Jan 07 '15 at 20:23
@usr . . . For most queries, you are correct: doing the work inside the database is *much* faster. This type of CTE happens to be iterative; you can think of it as running a separate query just like the loop, but without having to pass information in and out of the database (and this benefit is mitigated by the additional `order by` at the end). There is no "set-based" optimization as there would be with replacing a nested loop join with `join`. – Gordon Linoff Jan 07 '15 at 20:23
@DanielGonzalez . . . The revision I just made should return what you want, I think. – Gordon Linoff Jan 07 '15 at 20:24
@GordonLinoff my mistake, I does not explain correctly what i wanted. – smartdan Jan 07 '15 at 20:24
@GordonLinoff I'm curious. What do the triple dots mean that you are prepending to all your comments? – usr Jan 07 '15 at 20:32
@usr - Sometimes the recursive CTE can be slower. [Example](http://stackoverflow.com/q/14706696/73226) though unlikely in this case as presumably each level is just doing an equality seek on at most a single row. – Martin Smith Jan 07 '15 at 20:36

Is there a way to avoid this loop in SQL Server 2008 R2?

1 Answers1