2 almost identical queries (with a TVF) produce wildly different performance!

  • Come across a very 'simple' scenario which I can't explain.

    I have a Table_valued_function.

    I can perform a SELECT from it and it returns 12 rows, of 5 columns in a second.

    So is a small result set.  Fine.

    However, I then run the following two queries:

    ------------------------------------------------------------------------

    SELECT SUM(results)

    FROM my_TBF(param1,'param2')

    ------------------------------------------------------------------------

    SELECT SUM(results)

    --comment1

    --comment2

    --comment3

    --comment4

    FROM my_TBF(param1,'param2')

    ------------------------------------------------------------------------

    So.....these are essentially identical queries, BUT, the second one has a few lines of comments in it.

    AND....the second one takes about twice as long to complete!

    The first completes in around 2 seconds, and the second in around 4 seconds!

    They produce the same results.

    They quote the same QueryPlanHash.

    Execution stats state both are performing just one logical read, but wildly different CPU and elapsed times.

    Execution details appear to be the same EXCEPT for waits.

    Both are quoting SOS_SCHEDULER_YIELD, though the first is quoting 404 count of 14 Ms, and the second quoting 740 count of 1877Ms.

    Why are they so different?  Why do the comments make such a difference on essentially the same query?

    I've run the two queries repeatedly through the day AND on the database in a TEST environment.  Very similar results - the query with comments runs approx. twice as long.

    I'm guessing it has 'something' to do with the inclusion of a TVF, but can't explain why comments would have such a significant change on the performance of (I believe) the same execution plan.

    Suggestions/advice/explanation greatly appreciated 🙂

    • This topic was modified 1 month, 4 weeks ago by  don-357257.
    • This topic was modified 1 month, 4 weeks ago by  don-357257.
  • I am as puzzled as you.

    Have you tried playing around with the number of rows of comments to see how (or whether) that affects anything?

    You could also try changing the length of the comments themselves.

    I feel like I'm suggesting witchcraft here 🙂

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Yeah...certainly 'witchery' of some sort.......

    It also appears that if I enter 'more' lines of comments, the query runs longer!

    Of course, the 'executable' part of the query is identical, but SQL optimiser will presumably be seeing them as different queries.

    Still doesn't explain the wildly different execution times......

    Am sure is related to the fact that the select is from an MLTVF, but surprised by the big differences....

  • Since you're right there and have a handy test setup, I'd be curious what happens with the same query with the comments in the following form...

    SELECT SUM(results)

    /* comment1 */

    /* comment2 */

    /* comment3 */

    /* comment4 */

    FROM my_TBF(param1,'param2')

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

  • I'd already tested with all the multi-line comments in a single comment block - still runs long.

    I added your example, lots of comment blocks - still runs long.

    Here're the execution plans:

    First is after running the query with NO comments:

    select sum(result) from don_MLTVF (1200002167, '2022-09-28')

    brentozar.com/pastetheplan/?id=Hk_1hd4Gi

    Next is when running the query WITH comments:

    select sum(result)

    --wiefwufwiefb

    --qwojbquwjbwqujdb

    --qwufbqweufbeuifb

    --oquwfoquwbfquwbfuqwbf

    --qowifhbqowifqowufb

    --qwfiubqwiubqwdguv

    --wiefwufwiefb

    --qwojbquwjbwqujdb

    --qwufbqweufbeuifb

    --oquwfoquwbfquwbfuqwbf

    --qowifhbqowifqowufb

    --qwfiubqwiubqwdguv

    from don_MLTVF (1200002167, '2022-09-28')

    brentozar.com/pastetheplan/?id=H1nI2_4zj

    YES, they ARE different queries, but the only difference is the non-executable comments - I was expecting the SQL optimiser to just ignore the comments when determining an execution plan.

    As it is, the two plans 'look' identical, but the 'mystical' TVF box, shows much longer execution time for the query with comments.

    In practice, my query actually has some fields returned in place of those comments. But, it seems even if we put comments there, the execution time is long.

    Also...the more comments (or fields) I put in there....the longer the execution time 🙁

    Is it all related to the bad (?) way SQL Optimiser deals with TVFs?

  • Thanks.  That's just fascinating to me because I've never seen such a thing before.

    Any chance of you posting the TVF so I can try to make an experiment that duplicates the issue?

    Also, does the code in the TVF contain the word BEGIN anywhere?

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

  • Amazing.  I wonder if this is a "feature gone awry".

    The biggest difference in the execution plans is that the one with the comments contains "ContainsInterleavedExecutionCandidates = True" and the on without the comments doesn't even mention it.

    There's not much info that I could find in my simple search but the following did show up...

    https://techcommunity.microsoft.com/t5/sql-server-blog/introducing-interleaved-execution-for-multi-statement-table/ba-p/385417

    The thing that seems most affected is the WaitCount.

    Of course, my suggestion, even if this didn't happen, would be to avoid Mult-Statement Table Valued Functions to begin with. 😀

    Still, it's just nuts that comments would affect anything like this.  I wonder if the same number of blank lines would have such an effect.

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

  • Well I'm 'sort' of glad that it's not just me that finds it very strange!

    I'll try to provide the TVF tomorrow.

    Of course, I agree to stay away from MLTVF - but since it is a third party app, I don't have much choice and they used quite extensivley.

    Am discussing the issue with the third party too.

  • Oh lordy... I should have known.  Good luck there, Don.    And thanks for posting the original problem... this has been and continues to be a really interesting problem and, once I get a bit of time, I'm going to do a much deeper.  This "feature" is totally bonkers.

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

  • Have you tried clearing the cache and running the queries again?

    DBCC FREEPROCCACHE

     

Viewing 10 posts - 1 through 9 (of 9 total)

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