Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Improve running time of custom function RE: Improve running time of custom function

  • July 29, 2015 at 12:25 pm

    #1815846

    WayneS (7/29/2015)

    One thing that I note here is that you are using a Multi-Statement Table-Valued Functions, and they consist of just inserting data into the (output) table variable to be returned. Change this into an Inline Table-Valued Function (just take those select statements, and do a UNION ALL between them) instead. You won't believe the performance boost that you'll get from doing just this.

    I recently blogged about this (comparing all three T-SQL functions) here[/url].

    Absolutely agreed and great article from one of the true masters of the art but, mTVF or iTVF or not, there's a huge over-complexity in all that was posted on the original post that I don't believe is necessary especially since it appears to be a true and clean Adjacency List problem.

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