Which is better in SQL Server: Recursion or Temp tables?

re: Which is better in SQL Server: Recursion or Temp tables?

Daddy53 — Fri, 23 Oct 2009 00:54:03 GMT

Ottawa Tribe of Oklahoma v. ,

re: Which is better in SQL Server: Recursion or Temp tables?

marklar — Fri, 12 Sep 2008 19:01:38 GMT

woops nested set. no edit.

re: Which is better in SQL Server: Recursion or Temp tables?

marklar — Fri, 12 Sep 2008 18:58:22 GMT

replace the entire tree with a cached scalar table! sounds processor intensive. (in reply to modifying celko's adjacency list design)

recursion is limited to 32 levels, but alot of hierarchies don't exceed this extent.

re: Which is better in SQL Server: Recursion or Temp tables?

Sun, 13 Jun 2004 14:17:00 GMT

re: Which is better in SQL Server: Recursion or Temp tables?

rajgangina — Thu, 27 May 2004 12:06:00 GMT

USe Temptable it is better

Hierarchy structural data challenge

TrackBack — Tue, 27 Apr 2004 23:01:00 GMT

Hierarchy structural data challenge

TrackBack — Tue, 27 Apr 2004 22:56:00 GMT

re: Which is better in SQL Server: Recursion or Temp tables?

Sky — Sat, 10 Apr 2004 23:45:00 GMT

I came across your question via Ken Robertson's blog page at http://weblogs.asp.net/jezell/archive/2004/03/10/87010.aspx#111012 where I posted a summary of the different techniques that I have come across for dealing with recursion. Since you mentioned Celko's, thought I might post that there is a variation known as Farrady(sp?) Fractals. Looks interesting since it solves some of the update mess of Celko's.

Cheers!

PS: Celko's solution was a bit of a downer for me. At first blush it looked great. But then realized it is best suited for FEW updates: I've clocked sqlserver doing about 1000 straight updates per second, and for a decent tree, that's not very many nodes... -- and don't forget that you have to freeze the whole table for the update -- effectively blocking out usage of it until done.

Then again, as I write this, it just dawned on me that that one could do that in a temp table, and then replace the original, although I didn't try that when I was messing around with the theory. Hum. Maybe it aint so bad after all !

Table-Valued Function for traversing a tree

TrackBack — Sat, 20 Mar 2004 15:46:00 GMT

Excellent read on hierachy trees in SQL!

TrackBack — Wed, 10 Mar 2004 22:04:00 GMT