When people tell you that working with correct data types is important, it’s for a variety of very good reasons.
Not only can you avoid performance issues, but you can avoid strange query plan distractions, too.
Let’s look at an example for when you use date parameters against datetime columns.
Wrong And Mean
Index from outta nowhere pow!
CREATE INDEX pe ON dbo.Posts(LastEditDate);
The important thing about the LastEditDate column in the Posts table is that it’s nullable.
Not all posts will get edited. Especially mine. They’re always correct the first time.
Basically read only, if we’re being honest about things.
If your columns aren’t nullable, you’ll run into far fewer problems and ambiguities.
I’d like a new data type called ABYSS. Or maybe VOID.
The Problem: Wrong Data Type And NULL Checks
DECLARE @d date = '20170601'; DECLARE @sql nvarchar(MAX) = N' SELECT COUNT_BIG(*) AS records FROM dbo.Posts AS p WHERE p.LastEditDate > @d AND p.LastEditDate IS NOT NULL;' EXEC sp_executesql @sql, N'@d date', @d; GO
If we pass in a parameter that has a date datatype, rather than date time, an odd thing will happen if we add in a redundant IS NOT NULL check.
The seek predicate will only seek to the first non-NULL value, rather than immediately to the start of the range of dates we care about, which means we end up reading a lot more rows than necessary.
Note the query runtime of 743 milliseconds, and that we end up reading quite a few more rows than we return.
And here I was told Seeks are always efficient 🤔
Solution One: Stop Checking For NULLs
If we either stop checking for NULLs, we’ll get around the issue.
DECLARE @d date = '20170601'; DECLARE @sql nvarchar(MAX) = N' SELECT COUNT_BIG(*) AS records FROM dbo.Posts AS p WHERE p.LastEditDate > @d;' EXEC sp_executesql @sql, N'@d date', @d; GO
The plan for this query looks a bit different, but performance is no worse for the wear.
Note the 25 millisecond execution time. A clear improvement over the 743 milliseconds above. Though the query plan does look a bit odd.
The compute scalar gins up a date range, which is checked in the seek:
I wonder what Expr1002 is up to.
Solution Two: Just Use The Right Datatype To Begin With
In reality, this is what we should have done from the start, but the whole point of this here blog post is to show you what can happen when you Do The Wrong Thing™
When we use the right datatype, we get a simple plan that executes quickly, regardless of the redundant NULL check.
DECLARE @d date = '20170601'; DECLARE @sql nvarchar(MAX) = N' SELECT COUNT_BIG(*) AS records FROM dbo.Posts AS p WHERE p.LastEditDate > @d AND p.LastEditDate IS NOT NULL;' EXEC sp_executesql @sql, N'@d datetime', @d;
Here, the NULL check is a residual predicate rather than the Seek predicate, which results in a seek that really seeks instead of just meandering past some NULLs.
Thanks for reading!
A Word From Our Sponsors
First, a huge thank you to everyone who has bought my training so far. You all are incredible, and I owe all of you a drink.
Your support means a lot to me, and allows me to do nice stuff for other people, like give training away for free.
So far, I’ve donated $45k (!!!) worth of training to folks in need, no questions asked.
Next year, I’d like to keep doing the same thing. I’d also like to produce a whole lot more training to add value to the money you spend. In order to do that, I need to take time off from consulting, which isn’t easy to do. I’m not crying poor, but saying no to work for chunks of time isn’t easy for a one-person party.
I’m hoping that I can make enough in training bucks to make that possible.
Because this sale is extra sale-y, I’ve decided to name it after the blackest black known to man.
From today until December 31st, you can get all 25 hours of my recorded training content for just $100.00. If you click the link below to add everything to your cart, and use the discount code AllFor100 to apply a discount to your cart.
Some fine print: It only works if you add EVERYTHING. It’s a fixed amount discount code that you need to spend a certain amount to have kick in.
Thank for reading, and for your support.