Learn T-SQL With Erik: Cross Table Date Math
Chapters
- 00:00:00 – Introduction
- 00:01:48 – Slow Query Analysis
- 00:04:37 – Optimizing Cross-Table Queries
- 00:06:53 – Indexed Views Explained
- 00:09:02 – Conclusion
Full Transcript
Erik Darling here with Darling Data, enjoying a lovely LaCroix Pamplemousse seltzer. No booze in that whatsoever. Can you believe how lucky we are to enjoy a non-alcoholic beverage? Anyway, we’re going to do some more Learn T-SQL with Erik. In this video, we’re going to talk about cross-table date math, and I am going to catch myself and a funny little bit of irony in this one. Down in the video description, if you would like to purchase the full training content, there is a link with a coupon code attached to it down in the video description. Just below me here, just below this lovely fold here. There are also links for you to do other things, like hire me for consulting, become a supporting member of the channel, ask me Office Hours questions, and you can also, while you’re down there, like, subscribe, and tell all of your friends. Your beautiful, wonderful friends who would just benefit so greatly from watching these videos. If you are in the market for free SQL Server performance and availability group recently monitoring, you can get mine. There’s also links for this stuff. Totally free, totally open source, no phoning home, no weird stuff.
A brand new sort of version of the monitoring tool is available. Let’s go to Postgres and Timescale backend, and it has a headless Windows service, and it can monitor up to 500 servers, again, totally for free. It doesn’t cost you. It doesn’t cost you more if you want to monitor more than 500 servers. I would just maybe start a second install to monitor the other 500, because concurrency for that many servers, even in the wonderful world of Postgres, is maybe not so much fun. But, but let’s talk T-SQL here. That is, that is the turkey that we care about.
So, so we’re going to use this query, which, if I remember its provenance correctly, came from the Stack Data Explorer site. I can just never find it when I go look there again. But it’s, it’s, it’s, it’s the intent of the query is to find posts that had a lot of very early upvotes, and this query was always very slow, and to me, the interesting part of the query was that the where clause was looking for a date diff in columns on two tables. Now, under normal circumstances, if you had both of these columns in the same table, right, you could, you could, if you were denormalized a bit, but this would be a terrible denormalization.
You could create a computed column, but them being cross table, you don’t get that. SQL Server 2005 or so had this feature called the date correlation optimization, which could help in cases like this, but it required a lot of stuff, like, like a unique index on one of them. Which you can’t always get, and a foreign key between the two of them, which you can’t always get, because your data is filthy and cackadoodoo dirty because of your, all your nolak hints and whatever.
I don’t know, whatever. Let’s just go, go along with it. It’s funny.
So, it would be very hard to get that set up, but you, nothing is stopping you from creating an indexed view that can do the same or a similar thing. Now, this query runs for about, oh, five, six seconds or so. Oh, we got, we got a big five and a half second thing there.
And, you know, like one, like, this is not the thing I wanted to look at. It was being very silly. But, sort of, like, the important thing here is that, like, you know, you can, like, there’s only so much you can filter until the tables kind of get joined together, and you can finally apply, like, some of those filters, like the date diff one here, right?
The date diff in hours is less than or equal to 168, which is seven days, right? So, you have to, like, get all the rows from both tables and then do the join, and then you can start doing other things. And then you can start filtering your results to where they’re greater than or equal to 50.
And that’s fine, but, you know, if you want this type of query to be much faster, there are some things you have to do, right? So, you know, like, you might not find it very useful to seek to 40 million rows. I know I sure wouldn’t, but this is the way the votes table kind of breaks down.
So, you know, like, anything in here is going to not be fun. Like, nothing about this is going to be enjoyable. So, what I would recommend here is, especially if you are not in a situation where you can get the date correlation stuff working naturally out of the box, one thing I just want to point out about the original query, I’m going to make you sit through about five and a half more seconds of runtime here, is that, like, the majority, if not the total of these operators, aside from gather streams, are all happening in batch mode already, right?
Like, this is a nearly entirely batch mode query, and so, like, you’re already getting a lot of, like, the oomph that you would get out of this. You know, like, I guess if you want to add a non-clustered columnstore index there, you could, but we’re going to look at a slightly different way of handling it.
So, let’s come on down here, and what we’re going to do is we’re going to take a portion of the query that makes for a good indexed view. It’s not the full query, because I’ve talked about in other videos where we talk about indexed views. Making overly specified, overly specific indexed views will largely lead to those indexed views not being used by queries that could potentially benefit from them.
So, looking at all the, like, this is just, like, the base query that I would start with in here, and the idea behind it is just to get this part sort of squared away, right? So, we’ll create our unique clustered index as required in order to create other indexes.
And then there’s a neat trick with indexed views. Now, the indexed view documentation has some stuff about columnstore indexes. You can obviously not create a clustered columnstore index on an indexed view, because you can’t create a unique one, and the indexed view needs that.
But you can create a non-clustered columnstore index over an indexed view, and that can sometimes get you a very, very good mix of both the pre-aggregate and the batch mode that you would want. Did I actually run that?
It just happened very quickly. So, this all happened, it all happened in the blink of an eye. And now we can sort of, we can run this query, right, where we can get our sort of pre-aggregated list of stuff from the indexed view that has a count big of greater than or equal to 50, just like our original query.
And then we can join to our, just those rows to our query, and we can get ourselves a wonderful result that goes a lot faster. This all finishes in, well, because this is all happening in batch mode, we have to, and there’s no parallelism, we have to go look over here, and that the query time stats, and we’ll see this finished in 500 milliseconds of wall clock time with 460 milliseconds, 462 milliseconds of CPU time.
So, if you’re in an environment where these types of queries really have to be front and center fast, indexed views can go a long way to making them much, much faster. There are, of course, many caveats and drawbacks to indexed views.
Maintaining them on modification can be somewhat painful. And for this one specifically, which is something I’ve talked about many times in many other videos, when you have a cross-table index, when you have an index view with a join in it between one or more tables, not lock escalation, but isolation-level escalation can get pretty out of control.
You’ll also end up seeing a lot of serializable stuff that you didn’t ask for. SQL Server just does it automatically. So, you do have to be pretty careful and, you know, pretty conservative with how many indexed views you’re using and all that stuff.
But under the right circumstances, and especially if you start getting real crazy and creating non-clustered columnstore indexes on your indexed views, you can do a lot of really, really neat tuning stuff if the workload demands it.
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you’ll purchase the full course that this was just a tiny, itty-bitty little taste of and, yeah, all that stuff.
All right, cool. 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.