tsql - Improving recursive SQL looping -

i trying solve performance issue on inherited system appears when have significant amount of data.

we have table contains 2 fields "itemid" , "parentitemid". "parentitemid" field relates row in same talbe "itemid" field matches row's "parentitemid" field.

this relationship can many, many rows deep in places.

the following query being run , looks cause of slowdown:

while 1=1 begin     select @parentid = parentitemid items itemid = @lastparentid     if @parentid null     begin         break     end     else     begin         set @lastparentid = @parentid     end end 

is there better way of doing sort of recursive search?

note: not allowed make table changes @ point, adding "rootitemid" column not possible (i've asked, solve problem outright!)

you use common table expression this:

with antecedents (itemid, parentitemid, level) (     -- anchor member definition     select itemid, parentitemid, 0 level items itemid = @startingid      union      select itemid, parentitemid, antecedents.level + 1 level      items      inner join antecedents       on antecedents.parentitemid = items.itemid  )  select top 1 @lastparentid = itemid  antecedents order level desc 

more info on recursive cte's here:

http://msdn.microsoft.com/en-us/library/ms186243.aspx