Home Forums SQL Server 2008 T-SQL (SS2K8) rCTE vs LIKE for Hierarchy RE: rCTE vs LIKE for Hierarchy

  • September 11, 2012 at 2:26 pm

    #1536492

    ariel_mlk (9/11/2012)

    1146 rows, which should not be a problem, but some procedures take up to 4 minutes to run with the like code, and that's pretty bad, my concern is that the rCTE code generates something like 100 table scans with 10k+ logical reads, and I'm a begginer to this but I don't think this much IO is ok, the LIKE code posted does something like 1 scan 68 logical reads, yet performs timewise much much worse and that's where i get lost

    I agree. Believe it or not, a While loop that does the same thing as the rCTE will work at least as fast and sometimes faster and with a heck of a lot less reads.

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