You May Find Yourself
SQL Server 2019 has been a bit of a roller coaster ride. In particular, UDF inlining started as I think the most interesting addition to the product. Big brain stuff, for sure.
It has been nerfed quite a bit, with seemingly more and more restrictions added to every cumulative update. Hopefully some of these can be lifted at the feature matures, but I understand how difficult all this is.
People program absolute bloodbaths into functions.
Today, I want to look at one restriction that has a fairly simple workaround: Calling
Let’s use a simple function that figures out if fewer than 90 days exist between two dates.
CREATE OR ALTER FUNCTION dbo.sneaky ( @StartDate datetime, @EndDate datetime ) RETURNS bit WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT AS BEGIN RETURN CASE WHEN DATEDIFF ( DAY, @StartDate, ISNULL(@EndDate, GETDATE()) ) < 90 THEN 1 ELSE 0 END; END; GO
This function can’t be inlined, because we call
GETDATE() inside the function body. We can witness all the baddities that scalar UDFs cause as usual.
SELECT u.DisplayName, sneaky = dbo.sneaky(u.CreationDate, u.LastAccessDate) FROM dbo.Users AS u WHERE EXISTS ( SELECT 1/0 FROM dbo.Posts AS p JOIN dbo.Votes AS v ON v.PostId = p.Id WHERE p.OwnerUserId = u.Id );
The query won’t be allowed to use parallelism, the function will execute once per row it needs to process, etc. etc. etc.
The better option is to use a third function argument that you can pass
CREATE OR ALTER FUNCTION dbo.sneakier ( @StartDate datetime, @EndDate datetime, @FallBack datetime ) RETURNS bit WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT AS BEGIN RETURN CASE WHEN DATEDIFF ( DAY, @StartDate, ISNULL(@EndDate, @FallBack) ) < 90 THEN 1 ELSE 0 END; END; GO
You could also change the calling query to protect from NULLs and remove the check from the function, like so:
dbo.sneaky(u.CreationDate, ISNULL(u.LastAccessDate, GETDATE()))
Calling the new function like so results in a much faster query execution time:
SELECT u.DisplayName, sneaky = dbo.sneakier(u.CreationDate, u.LastAccessDate, GETDATE()) FROM dbo.Users AS u WHERE EXISTS ( SELECT 1/0 FROM dbo.Posts AS p JOIN dbo.Votes AS v ON v.PostId = p.Id WHERE p.OwnerUserId = u.Id ); GO
The plan is allowed to go parallel, and rather than the function being hidden in a Compute Scalar operator, it’s represented in the query plan by Constant Scan operators that produce the necessary rows.
These types of rewrites will probably become more common as people move to newer versions of SQL Server, and embrace higher compatibility levels where these features are allowed to maneuver.
Of course, at the rate things change, that may be a long ways off.
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 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 performance problems quickly.
4 thoughts on “Rewriting Scalar UDFs For SQL Server 2019 Inlining”
Is this good for pre or post CU11 of 2019?
Why would that matter?
I thought it might be related to the latest inline fixes
Comments are closed.