Recursive Code without using SP

Question

Recursive Code without using SP

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 1

(Unless of course you can exploit Jeff's idea of storing per-prepared results in a table...)

I'm glad you brought that up, Ed... using recurrsion to come up with the same or similar answers all the time is a waste of CPU... for Hierarchies, it would be much better to use Joe Celko's "Nested Set" model instead of recurrsion to resolve the "Adjacency Model" over and over and over and ...

I believe the code that builds the tree has error in it that leaves off the right most leaf of the tree, but that's easily repaired in the following URL... and, it's nasty fast once the tree is constructed using the "Nested Set"...

http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html

There are other ways to do the same like storing the upline for each leaf in one long VARCHAR and then using LIKE to find uplines, downlines, and peers... but the answer is the same... instead of recalculated that which does not change frequently, it's better to store the answers and look them up instead of using recurrsion.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

J-440512 SSCertifiable Points: 6285 More actions · Answer 2

One of the most abused examples of recursion: Factorials.

The is the first example given to explain recursion because

it is easy to describe the problem to be solved.

Most unfortunately, this is often the only example given,

without any mention that there is a time for recursion and

a time where recursion is the worst choice.

Factorials and Fibonacci sequences are examples where

recursion is a BAD idea. It offers no real advantage

(other than didactic) over the conventional loop.

The overhead incurred in dealing with the intermediate

steps of successive calls to a function is significant.

And SQL Server (at least SQL Server 2000) places a

hard limit of 32 levels.

As a learning concept, maybe OK.

Reminds me of the infamous "bubble sort" algorithm that

was taught in my engineering course. Again, no mention

was ever made that for a small collection of items, it is

satisfactory and simple. When the number of items

increases significantly, more sophisticated algorithms

have to be used.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

Try this for fun factorials (Number has to be a positive non-zero value)

SELECTEXP(SUM(LOG(Number)))

FROMTally

WHERENumber BETWEEN 1 AND 170

N 56°04'39.16"
E 12°55'05.25"

J-440512 SSCertifiable Points: 6285 More actions · Answer 4

Peso,

This is interesting.

Running it up to 10:

[font="Courier New"]

AsIs Rounded Round_Truncate

3628800.0000000084 3628800.0 3628800[/font]

A Factorial is supposed to be an integer, right ?

And if you are creating a Tally table anyway, which not add another column which holds the results, you know, PreCalculated_Factorials.

Reagrds

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

Yes you could.

I just thought of a way to include the result as a correlated subquery or even in a view.

N 56°04'39.16"
E 12°55'05.25"