If you’re not aware of the performance problems scalar valued functions can (and often do) cause, well, uh… click here. We’ll talk in a few days.
If you are, and you’re worried about them crapping on Adaptive Joins, follow along.
The big question I had is if various uses of scalar valued functions would inhibit Adaptive Joins, and it turns out they’re a lot like non-SARGable queries.
Starting with a simple function that doesn’t touch anything.
|
1 2 3 4 5 6 7 8 |
CREATE FUNCTION [dbo].[ScalarID] ( @Id INT ) RETURNS INT AS BEGIN RETURN 2147483647 END; |
We can all agree that it doesn’t access any data and just returns the INT max. Now some queries that call it!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SELECT u.Id, p.Score, dbo.ScalarID(u.Id) --In the SELECT on the Users table FROM dbo.Users_cx AS u JOIN dbo.Posts AS p ON p.OwnerUserId = u.Id WHERE u.LastAccessDate >= '2016-12-01' SELECT u.Id, p.Score FROM dbo.Users_cx AS u JOIN dbo.Posts AS p ON p.OwnerUserId = u.Id WHERE dbo.ScalarID(u.Id) = u.Id --In the WHERE clause on the Users table SELECT u.Id, p.Score, dbo.ScalarID(p.Id) --In the SELECT on the Posts table FROM dbo.Users_cx AS u JOIN dbo.Posts AS p ON p.OwnerUserId = u.Id WHERE u.LastAccessDate >= '2016-12-01' SELECT u.Id, p.Score FROM dbo.Users_cx AS u JOIN dbo.Posts AS p ON p.OwnerUserId = u.Id WHERE dbo.ScalarID(p.Id) = p.Id --In the WHERE clause on the Posts table |
If you’re the kind of monster who puts scalar functions in WHERE clauses, you deserve whatever you get. That’s like squatting in high heels.
Not that I’ve ever squatted in high heels.
Alright look, what’s that Brent says? I was young and I needed the money?
Let’s forget about last week.
Query Plans!
By this point, you’ve seen enough pictures of Adaptive Join plans. I’ll skip right to the plan that doesn’t use one.
It’s for the last query we ran, with the scalar function in the WHERE clause with a predicate on the Posts table.
See, this isn’t SARGable either (and no, SCHEMABINDING doesn’t change this). When a predicate isn’t SARGable, you take away an index seek as an access choice. You don’t see too many Nested Loops with an index scan on the other end, do you?
No.
So there you go. It’s not the function itself that bops our Adaptive Join on the head, but the lack of SARGability.
Thanks for reading!
