Post reply

Recursive Code without using SP

  • November 7, 2007 at 6:15 am

    #749061

    (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)
    Intro to Tally Tables and Functions

  • November 7, 2007 at 11:31 am

    #749181

    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.

  • May 27, 2008 at 4:07 am

    #820434

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

    SELECT EXP(SUM(LOG(Number)))

    FROM Tally

    WHERE Number BETWEEN 1 AND 170

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

  • May 27, 2008 at 6:29 am

    #820501

    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

  • May 27, 2008 at 6:32 am

    #820503

    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"

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

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