Turning And Turning
In yesterday’s post, I showed you a function rewrite from Scalar to Inline Table Valued, and gave you a couple example calls.
Now, if this is all you’re doing with a function, there’s absolutely no need to rewrite them.
SELECT cl.* FROM dbo.CountLetters('1A1A1A1A1A') AS cl; SELECT CountLetters = dbo.CountLetters_Bad('1A1A1A1A1A');
If you’re doing something like this, and maybe assigning it to a variable or using it to guide some branching logic, don’t you sweat it for one single solitary second.
You may want to make sure whatever code inside the function runs well, but changing the type of function here isn’t going to improve things.
More realistically, though, you’re going to be calling functions as part of a larger query.
Second To None
Let’s say you’re doing something a bit like this:
SELECT u.DisplayName, TotalScore = SUM(p.Score * 1.), Thing = dbo.CountLetters_Bad(u.DisplayName) FROM dbo.Users AS u JOIN dbo.Posts AS p ON p.OwnerUserId = u.Id WHERE EXISTS ( SELECT 1/0 FROM dbo.Votes AS v WHERE p.Id = v.PostId ) AND u.Reputation >= 100000 GROUP BY u.DisplayName ORDER BY TotalScore DESC;
It’s a far different scenario than just assigning the output of a Scalar UDF to a variable or using it to guide some branching logic.
A few minor syntax changes to the function and to how the query calls it can make a big difference.
SELECT u.DisplayName, TotalScore = SUM(p.Score * 1.), Thing = (SELECT * FROM dbo.CountLetters(u.DisplayName)) FROM dbo.Users AS u JOIN dbo.Posts AS p ON p.OwnerUserId = u.Id WHERE EXISTS ( SELECT 1/0 FROM dbo.Votes AS v WHERE p.Id = v.PostId ) AND u.Reputation >= 100000 GROUP BY u.DisplayName ORDER BY TotalScore DESC;
Since this is a table valued function, you have to ask for results from it like you’d ask for results from a table.
Whether you like competitive sports, good vs. evil, Great British Bake Off, or watching election results, you can appreciate the magnificence of a Flawless Victory©.
As long as someone wins who you’re rooting for. But here, no one’s rooting for scalar functions. They’re just unlovable.
I’m going to show you the very end of these plans to see the timing differences.
The Scalar UDF plan takes ~23 seconds, and the inline TVF plan takes 7.5 seconds.
And this is why testing certain linguistic elements in SQL needs to be done realistically. Just testing a single value would never reveal performance issues.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly.
- SQL Server 2022 CTP 2.1 Improvements To Parameter Sensitive Plan Optimization
- SQL Server 2022: Introduces the DATETRUNC Function, So You Can Truncate Your Dates And Stuff
- SQL Server 2017 CU 30: The Real Story With SelOnSeqPrj Fixes
- SQL Server 2022 Parameter Sensitive Plan Optimization: Does Not Care To Fix Your Local Variable Problems
4 thoughts on “Why You’re Testing SQL Server Code For Performance The Wrong Way”
Hey Erik, Yesterday’s post was the Sql Tuesday one and it didn’t have the function?
Is there a benefit to doing “SELECT Alias = Value” instead of “SELECT Value AS Alias”, or is it just personal preference?
Yeah, I rearranged things last minute to accommodate the T-SQL Tuesday post. I’ll fix it eventually.
As for the aliasing – the main benefit is to not annoy people who write queries correctly.
A bit off topic but is the 1/0 thing just for flair? or is there a reason (aside from preference/cool factor) you went that? I know the EXISTS select list isnt executed so it doesnt matter but if there is a functional reason Id love to know. Thanks
Just for kicks
Comments are closed.