Post reply

How to guesstimate # of recursions?

  • July 14, 2011 at 3:15 pm

    #253768

    Hi Folks,

    I have a recursive CTE I am using to determine a hierarchy path. I have used these queries before but am a little foggy on the default MAXRECURSION limit. I have a couple of hierarchies that I do not know how deep they may go so I am trying to evaluate whether I need to change the MAXRECURSION limit. Is this limit matched based on the number of subordinate queries I must do?

    For example, if I have a hierarchy that is four levels deep, is that four recursions?

    Thanks, Mark

    Best Regards,

    Mark Tierney

  • July 14, 2011 at 8:44 pm

    #1353157

    No, the anchor query in a recursive CTE does not count towards the MAXRECURSION limit.

    As you can see for yourself in the following example, setting MAXRECURSION to 2 doesn't stop at level 2.

    declare @family Table(lvl int, name varchar(20))

    insert into @family

    select 1, 'Abraham' union all

    select 2, 'Beppo' union all

    select 3, 'Carlos' union all

    select 4, 'Donald' union all

    select 5, 'Elphaba'

    ;with familytree (level_, name) as

    (select lvl,name-- anchor

    from @family

    where lvl=1

    union all

    select lvl,f2.name-- recursor

    from @family f2

    join familytree f1

    on f2.lvl = f1.level_ + 1

    )

    select * from familytree

    OPTION (MAXRECURSION 2);

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • July 14, 2011 at 10:06 pm

    #1353174

    Mark Tierney (7/14/2011)

    Hi Folks,

    I have a recursive CTE I am using to determine a hierarchy path. I have used these queries before but am a little foggy on the default MAXRECURSION limit. I have a couple of hierarchies that I do not know how deep they may go so I am trying to evaluate whether I need to change the MAXRECURSION limit. Is this limit matched based on the number of subordinate queries I must do?

    For example, if I have a hierarchy that is four levels deep, is that four recursions?

    Thanks, Mark

    Unless you're dealing with the membership of something like a multi-million member multi-level marketing company, the default recursive limit of 100 will likely suit you just fine. Rumor has it that even a Boeing 747 has a BOM of only 18 or so levels (17 recursions) deep. There aren't many companies (even the big ones) with org-charts of more than just a couple dozen levels.

    --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)

  • July 15, 2011 at 5:52 am

    #1353344

    Thanks Jeff. Thank confirmed both my suspicion and how recursion loop is defined.

    Best Regards,

    Mark Tierney

Viewing 4 posts - 1 through 3 (of 3 total)

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