A Little About Date Math In SQL Server Queries

A Little About Date Math In SQL Server Queries

---

Video Summary

In this video, I delve into the nuances of proper date math in SQL Server queries and demonstrate how it can significantly impact query performance. After a rather unconventional breakup with BeerGut magazine, I’ve found myself in an interesting new partnership with Scott Sniffer magazine. While this venture might be more refined and elegant than my previous endeavors, it does come with its own set of challenges—like dealing with fancy Scotch sniffers who, despite their title, may or may not actually drink the stuff. Focusing on practical examples, I show how moving date math operations to the correct position in your queries can drastically improve performance by allowing SQL Server to utilize indexes more effectively and avoid unnecessary scans. This video is a reminder that writing well-structured queries isn’t just about getting results; it’s also about making sure those results come quickly and efficiently—leaving you with more time to enjoy a well-deserved glass of Scotch, or at least the virtual equivalent!

Full Transcript

Erik Darling here with Darling Data. After my prolonged, painful breakup with BeerGut magazine, I’ve been approached by Scott Sniffer magazine about forming a partnership. It’s going to be a little bit harder on my liver, but, you know, maybe a little bit more refined and elegant. Just like, just like my T-SQL and BeerGut magazine. Finally, work with a higher echelon of individual, not lowly beer drinkers. I’m going to hang out with a bunch of fancy, pinky-out Scotch sniffers. Actually, it’s unclear to me if they actually drink the Scotch or throw it in. They just sniff the Scotch and tell you what they think it smells like. So, all things I’m sure that I’ll learn during our, uh, conversations. So, uh, this video, uh, like the helpful ASCII text is telling you is, uh, where we do date math and how we do date math in our where clauses, and I guess occasionally in our join clotters, clotters, uh, to avoid those, uh, clauses matters for performance. So, right now, the only index that I have on the post table is on the ID column, and that’s a weird bit of format. What, what, what, quite what possessed me to do that. I think maybe I just got drunk and hit the wrong button. Uh, the only index that I have on the post table at the moment is on the ID column and with the owner user ID column included.

And what this query, well, I have two queries, one of them written the wrong way and one of them written the right way. Uh, in this join, I am doing date math the wrong way. Let’s frame that nicely. There we go. Let’s see, uh, I can draw a pink box around my head kind of. Ah, the things you can do with computers. So, in this one, uh, we are doing date math on the column that we care about. We have the v.creationDate wedged in the middle of, uh, our dateDiff function.

And in this query where we’re doing things the right way, uh, we are comparing the creationDate column to a date math expression here. So, I’m going to run both of these queries. And we got query plans turned on, so that’s a good thing.

And, uh, the reason why, of course, this makes a big difference is because when, uh, SQL Server has to perform date math. Um, um, on a column directly, uh, it has to do a lot more work than if it, if we, if it can do the date math on, uh, an expression and apply that to the data in our table. So, just moving, like, we don’t have, we don’t even have a useful index on the votes table right now to, uh, to, to find data in the creationDate column.

But even without that, even without that, we are able to make the, our query go nearly twice as fast. Well, actually, that’s a little bit better. The shave is about two seconds off the whole thing.

Go from 3.6 seconds when we write our query the wrong way to about 1.4 seconds when we write our query the correct way. So, there’s about a 2.2 second difference just making that change, just pushing the, uh, the, the date math from being, uh, from having the creationDate in the dateDiff column. And, uh, you know, comparing the creationDate column to this expression.

Now, if we go back and look at the query plans, I mean, aside from the fact that, you know, when you use non-sargable expressions, like when you put a column in a function and compare it to something, uh, SQL Server doesn’t bother to tell you that an index might help. Because you’re going to have to scan that entire index anyway. SQL Server’s like, well, what’s the difference?

I have to do, I have to do the same amount of work. You know, I’m just not even going to bother with that. Like, it’s not a cleanly, enough, not a cleanly written expression. So, uh, sorry, it’s not a cleanly written comparison. So, you can’t really, you don’t really get anything useful out of an index there.

Like, you might have a smaller index to scan, but it’s not going to be like a seek or anything. You’re not going to be able to seek to the data that you care about. So, you know, if you go look at this, uh, we have this predicate here where we have all sorts of stuff going on.

Uh, we, we compare, we, ooh, look at that. That’s a fun one. Uh, we have a convert implicit to date time offset three.

That’s fun, right? So that, that’s going to be part of, that’s going to contribute a bit to our, our woes. And then, uh, in the, there’s a helicopter going by.

Uh, I’ve been told by. The ever vigilant Randolph West that, uh, the, the New York City noises in my videos are part of the charm. I wonder if part of the charm is also the fact that I’m standing in front of the thing I want to show you.

So, let’s, let’s reframe that a little bit. Let’s move that over. Uh, actually, let’s bring up the tool tip first. And let’s get that where we want it, where we can see it mostly. Good enough.

Uh, nope. Stay. Stay. Good boy. Sit, Ubu, sit. Is there old enough to remember that reference? Uh, I owe you a, an Adidas t-shirt and a high five. So, in this one, even though we, we scan the scanner index in the same way, the predicate we apply doesn’t have any implicit conversions. Uh, we are able to get a bit more performance out of our query just by moving the date math a little bit.

Uh, and, uh, of course, SQL Server. And this one helpfully suggests an index on creation date that includes post ID. Right there.

Up in the pink. Oh, uh, fragile green screen. Uh, but that, that is, uh, you know, uh, sort of secondary. I mean, anyone, uh, who’s been working with, uh, SQL Server or databases in general for any amount of time could probably figure out, uh, that an index on a where clause is a useful. That’s a helpful thing.

Remember, we put data in order, it gets really easy to find. Uh, the only thing that I would probably disagree with, and another big reason why I tend to, uh, try to dissuade folks from listening to the missing index request, is because, uh, if you, we go back to the missing index request, uh, SQL Server only wants to include post ID in the index.

It does not, uh, does not think that our join column would be useful as a key column, which is, um, in most cases, a fair bit of malarkey. So, I’m going to be honest with you. Those missing index requests, not terribly valuable things.

Not terribly valuable at all. Kind of, kind of dislike them. Um, used to like them a lot. Now I hate them.

Anyway, um, you know, they’re, if they’re okay if you’re looking at the query itself, and if, you know, they’re okay if, you know, uh, you’re looking at the actual execution plan. And, uh, the operator, uh, in this case, the clustered index scan is the longest running thing in the query. And, you know, uh, you might say, oh, well, uh, like, you know, it might, it might be useful.

Might not. You know, uh, shaving 863 milliseconds off here. Not, not sure that that’s going to be, you know, the, the big winner.

Uh, you know, it might help a little bit more. But, uh, you know, this one up here where it’s, you know, three seconds of execution time, that might be a little bit more compelling. But, you know, um, also, if you write your queries correctly, uh, they tend to run faster anyway.

So, uh, this is about all I had to say about this one. Um, this is me urging you to write date math in your queries correctly and improve performance. And, uh, I don’t know, maybe you’ll get to see more of your friends or family or just get to do the things you enjoy better than sitting around scratching your head about why your SQL Server queries are all slow when you have poorly written date math inquiries like this.

So, I don’t know. You’re welcome. Um, you’re welcome in advance, I guess.

For all that. Uh, I’m going to, I don’t know. Maybe I’m going to go do something I enjoy now. Maybe I’m going to go sniff some scotch.

Sounds like a pretty decent idea. It’s 6.28 p.m. here at the moment. Sitting here recording videos instead of smelling delicious scotch. That seems like a bad choice.

Maybe you shouldn’t listen to me about anything. Maybe I’m wrong. Maybe, maybe I’ve got my life entirely wrong. Oh, anyway. I’m going to kind of go re-evaluate some things. Thank you for watching.

Please like, please subscribe. It’s the thumbs up and the bell. Uh, if you don’t, I’ll just have to sniff scotch even harder. I might sniff it so hard that it goes right up my nose.

And who knows what will happen then. Get scotch in my brain or something. Anyway, uh, thank you for watching. I hope you learned something.

Hope you enjoyed yourselves. Uh, and I hope that you start writing date math correctly in your where clauses so that you don’t end up with poorly performing queries. Thank you for watching.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% 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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.