Learn T-SQL With Erik: Performance Solutions For DATEDIFF Queries
Chapters
- 00:00:00 – Introduction
- 00:02:15 – Sargability and Indexing
- 00:04:45 – Rewriting Queries for Better Performance
- 00:06:57 – Practical Query Optimization Techniques
- 00:09:35 – Date Correlation Optimization
- 00:11:14 – Conclusion
Full Transcript
Erik Darling here. Suffering. Suffering in this massive heat wave. Don’t like it. Don’t enjoy it. I mean, the kind of weather… I don’t know. Where’s it always cold? I’m gonna move there. What’s Iceland like this time of year? I don’t know. Maybe Brent had the right idea in 2020. Move to Iceland. It’s never hot.
I mean, you know. I think every beer costs like $30 because they have to helicopter it in from somewhere, but it’s worth it. You never have to deal with heat. Occasionally eat shark that smells like stale urine, I guess. I don’t know. Anyway, let’s learn some more T-SQL. This is the wrong title. Screw that. It’s too hot to change it. In today’s video, we’re gonna talk about performance tuning queries that have to compare the date differences between two columns. There’s one very obvious thing that we can do in the form of the computed column, but then there are other things that we can do depending on our indexing.
We can write into the query. Again, coming back to the idea that sometimes we can align our indexes in our computed columns to our queries. And other times, we can better align our queries to our indexes. If you would like to purchase the full course, where I talk about absolute accuracy, and I talk about the data-driven, absolutely everything in great detail, and you have access to the scripts and the query plans and all the other stuff that we do in here. You can purchase it for $100 off down in the video description. It’s a great time. You can have the whole family sit and watch it, right? Put it on at dinner. Better than leave it to beaver, I think. You can also hire me for consulting. You can become a supporting member of the channel if you would like to part with $4 a month to say thank you for all the hard work that I do here. Ask me office hours questions. That is, of course, free.
So, you know, it makes me feel good. And, of course, please do like, subscribe, tell your friends. Get your children involved early. It’s never too early to start learning about databases. Ruin your childhood. If you are in the market for free, absolutely, totally, no strings attached free SQL Server performance monitoring, you can download the performance monitoring tool that I make. It’s on GitHub. You can read through everything. Down in the video description, you will find the appropriate linkage to get that and grab it. It’s a great time, and it’s only getting better. I can’t make it more free than it is because it is entirely free, but I can make it better. I can add more value to free somehow.
But now, let’s continue suffering. In this dismal weather, let’s talk about SQL Server. So, in the last video where I talked a little bit about precision and date and stuff, this one, we’re going to talk about performance a little bit more. So, we’ve got these two indexes already, right? We’ve got one on the post table in the Stack Overflow 2013 database, one on last activity date creation date, one on creation date last activity date. And a problem that we identified was that whenever we want to date these two columns, SQL Server has no choice but to…
to scan stuff, right? And that… this poses an issue for us as performance tuners because sometimes a scan really isn’t… really isn’t all that good. So, we have to scan our non-clustered index, right? It chose the index on creation date last activity date. Why? It doesn’t matter. It would have had to scan either one, right? And there’s no… there’s no difference. There’s no hidden seek plan here that would have helped SQL Server along. Now, flipping that around a little bit, right? Let’s say…
let’s say that we wanted to try to write this in a more sargable way. I don’t know. How do you describe Rob Farley? Is he a friend? Is he a foe? All I know is that he’s a magician, and you can’t trust magicians, but he is a pretty smart guy. And a long time ago, he wrote a post about sargability and called it, like, Look Both Ways, or something. And Rob had some very good points in that post.
And so, I’m going to try to expand a little bit on that in a slightly different way, because I’m not… addition. I’ll never hassle you with card tricks, but maybe some people are into that. So like if we tried to write this query in this way, so like say we wanted to remove the date diff function from the two column setup, and we said, let’s date add 10 years to one of these columns, maybe SQL Server could take advantage of an index on the other column, because we’re like saying, hey, SQL Server, you can see, you can do all the math on this one column, and then maybe you can seek to the data in this other column, because there’s no function wrapping around this column now. But SQL Server does not do that, right? SQL Server still completely scans this index on creation date, last activity date. And if we try to add in a force seek hint to say, hey, SQL Server, perhaps you would like to try seeking here instead, we get an error that the query processor could not produce a plan of that variety.
And so we are stuck. We were stuck scanning this. Now, one way that you can get around this is you can choose to write your query in a slightly different way and give SQL Server some boundaries, right? So like the whole point here is that we’re looking for places where there are a 10-year difference between creation date and last activity date.
So the first thing that we might do is try to find a maximum value to start with, right? That’s what we’re doing in here, right? We’re saying, give me the max. And this works in either direction, going max or minimum. So we’re going to say where creation date is less than this whole date add construct in here, where we find the max last activity date. And then we will do our final calculation in here like this, right? And this basically mimics the last query, except it gives us a place to seek into, right? And we can see that we do sort of find some stuff in here, right?
We find the max last activity date in this portion of the plan. And then we seek into one of our indexes here for the rows that we care about, right? So we get down to that one row max, and then we return stuff out. And this is a market performance improvement. It’s a little hard to tell because the last query just said 563 milliseconds. And it was very easy to see.
For this query, we have to go and find the max last activity date in this portion of the plan. And then we go into the query time stats to see this 69 finished in 69 milliseconds. So this was a pretty good strategy for this one. And you can even flip that if you need to find like mins too, right? So if we wanted to flip the order of this, and like rather than saying where creation date is less than or equal to subtracting nine years from the max, and then the last activity date is greater than or equal to adding 10 years to creation date, we could flip that to do a min on creation date and last activity date and kind of do that in here.
In this way. But my memory serves, this is basically the same plan. But yeah, so but well, actually, I guess this one is 54 milliseconds, but well, that may be 55. Let’s see. Let’s see what query time stats tells us. Ah, 56 milliseconds. So not so I would consider that 13 milliseconds, maybe some noise. Maybe even if that’s consistent, is 13 milliseconds worth all that flipping around? I don’t know. If you’re having the robots do it, probably it’s nothing happens. Anyway, your company’s paying for it. So again, coming back to the sort of ivory tower way of looking at things, store data, the way you query it, query data, the way you store it, otherwise, you’re gonna have to write some pretty weird queries to find performance satisfaction with things, right? At one point, Microsoft did make an attempt at this sort of thing. They added that date.
Date correlation optimization setting around 2005. But it never really took off. And the basic idea was that if you had two date time columns, and one of them happens to be unique and has a unique constraint or index on it, which I know you people, right? And you obey all the ANSI settings rules applicable for filtered indexes, computed columns, index views, and you create a foreign key between your unique date time column and your non-unique date time.
Then the optimizer would be able to figure some additional stuff out when you’re joining two tables together. So if you wrote a query like this that has all that stuff applied to it, SQL Server would turn it into a query that looks like this, right? It would add this date correlation stuff to it.
And it would say, oh, well, not only do I look this way, but I’ll also look this way. But the problem is that it did that by creating an indexed view for you in the background. And so it’s not terribly surprising why this didn’t go very far and get much traction with the general public. I think Fabiano Emerim is the only person who ever saw, like, really blog about it. But I guess the obvious thing, and this is something that we’ve talked about in many other videos, would be just to simply store data the way that you’re querying it, right? And just assuming that we don’t care so much about all the precision stuff that I talked about in other places, we would just create a computed column. And notice that computed column is just a column that does not have to be persisted in order for us to index it. And because all it produces is an integer, maybe, probably an integer, date diff result, it is deterministic out of the box. We don’t have to do any weird entangling of things in order to make this deterministic. And then all of a sudden, our queries magically pick that up, right? And we just say, hey, I know who you are, right? Unfortunately, I don’t think either of the fancier queries that we wrote pick up on that computed column, but maybe. Let’s just go and have a look-see. I don’t think it happens, but yeah, there we go. Yep, they just use the regular indexes there, which makes sense because the expressions that are in use here are certainly not anywhere near the expression that was used in our computed column, right? It would have to be far more precise than that. But our query picks up on it down here, uses our computed column. We don’t have to worry about the data we care about, and all is generally much better. All right. I hope you enjoyed yourselves. I hope you learned something. This is the Thursday video, so I will see you next Tuesday for office hours. And again, if you want to purchase the full course material that all of this stuff stems from, the link is down in the video below with a coupon for 100 bucks off. All right. Thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.