In yesterday’s post, we looked at a clever way to reduce calls to a scalar UDF using APPLY.
Today, we’re going to see if 2019 changes anything, and if our old trick still tricks.
Because, you know, what else do you do when you need to write 5 blog posts a week?
Don’t Be A Donkey
I’m going to abridge this a little bit, since all the code is referenced at the link up there.
I’m also going to show you some stuff using Plan Explorer.
Because SSMS kept opening the plan XML as XML, and that makes for crap screenshots.
Here’s the results for the plan with two function references. It runs for ~2.2 seconds.
If you remember yesterday’s post (and why wouldn’t you, hm?) the query plans didn’t show us touching other tables at all.
Just seeking into the Users table and then magically computing scalars and filtering.
One of the nice things about scalar UDF inlining: honesty.
But, you know, the two where clause references end up expanding. We’re hitting pretty big tables, here, too.
Using APPLY has a similar *ffect here. The function is only referenced and filtered once, and the duration is cut roughly in half.
Now, I know you’re probably thinking, because YOU REMEMBER YESTERDAY’S POST!
How come these queries are so much slower with the functions inlined?
Well, they’re not. With query plans turned off, the first one runs in ~900ms, and the second one runs in ~500ms.
Yesterday’s plans run for 1.6s and 600ms respectively with plans turns off.
Apparently observation has overhead. If only there were a clever phrase for that.
Not All Functions
The idea behind FROID is that it removes some restrictions around scalar valued functions.
- They can be inlined into the query, not run per-row returned
- They don’t force serial execution, so you can get a parallel plan
If your functions already run pretty quickly over a small number of rows, and the calling query doesn’t qualify for parallelism, you may not see a remarkable speedup.
That’s fine, though, because inlining has other benefits:
- Query plans are honest about the work they do
- Measuring the query will show you work that used to be hidden behind the function call(s)
Even if every query doesn’t magically finish before you run it, you’ll see pretty good gains.
Thanks for reading!