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

Question

Post reply

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

don-357257

Mr or Mrs. 500

Points: 572
More actions
September 29, 2022 at 4:00 pm

#4093492
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 2 years, 1 month ago by don-357257.
- This topic was modified 2 years, 1 month ago by don-357257.

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

don-357257 Mr or Mrs. 500 Points: 572 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

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)

don-357257 Mr or Mrs. 500 Points: 572 More actions · Answer 4

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?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

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)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

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)

don-357257 Mr or Mrs. 500 Points: 572 More actions · Answer 7

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.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

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)

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 9

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

DBCC FREEPROCCACHE