Learn T-SQL With Erik: Grouping Sets vs Union All
Video Summary
In this video, I delve into the lesser-known world of `GROUPING SETS` in SQL Server and explore its alternatives, including `CUBE` and `ROLLUP`. While these features are powerful for complex aggregations, they often face performance challenges due to how the SQL Server optimizer handles them. I demonstrate a real-world example using Stack Overflow data, showing that even with a columnstore index, `GROUPING SETS` queries struggle to utilize batch mode efficiently, leading to slower execution times compared to equivalent queries without these features. By comparing the query plans and performance metrics, you’ll see firsthand why sometimes sticking to simpler methods can yield better results in practical scenarios.
Full Transcript
All right, we’re gonna talk about, boy, something I don’t see very often, be honest with you. Maybe I don’t see it very often because a lot of people don’t really understand how this stuff works and so they get afraid of it. But we’re gonna talk about grouping sets and alternatives here. And the alternatives are important because there’s also the distinct possibility that someone wrote a grouping sets query once upon a time and, gosh, it just didn’t perform terribly well, which is, uh, eh, that’s something that can happen. Boy, howdy. We’re gonna look at that. Um, all of this content is preview material for my course Learn T-SQL with Erik. Uh, it is on the pre-sale price right now of 250 US dollars. That’ll last you for the rest of your year. Uh, it will go up to 500 US dollars once the advanced material is published after the summer. Uh, the, the beginner material is on the very cusp of being completely published. So, uh, you’ll have many hours, uh, of, of content to get through, uh, anyway. Uh, this is all companion content, meaning not the exact content, but it is companion content to, uh, the material that Kendra Little and I will be presenting at, uh, past data community summit in Seattle. So if you attend our pre cons there, uh, you will, you will get access to this material, uh, with your price of admission. Um, I think that’s about it. So we’ll just get at a lot of this thing. And, uh, well, I mean, there’s a whole slide here. Now you can see all my secrets, right? You can see all this stuff that I do when you’re not watching. So, uh, we’re gonna get into the old stack overflow database here.
And, uh, I’m gonna show you what a grouping sets query looks like. There are of course other grouping set, uh, let’s just call them clauses that you can use, uh, like cube and rollup. Uh, cube will, uh, create distinct grouping sets out of all possible column combinations and rollup will, uh, create, um, well, rollup is a little bit more, uh, uh, complicated in what it does at least, at least it’s more complicated to explain where you define your rollup clause. And then SQL Server will take whatever column you put first in the rollup clause and match that to other columns. So it doesn’t do all unique sets. It just does like a subset of unique sets based on whatever you put in first. Uh, I cover it in way more detail in the actual material.
So if you’re really fascinated by what cube and rollup do, uh, you can, you can buy the material and watch it or just watch it if you’ve already bought it. But, uh, with grouping sets, you can define whatever sets of columns you want to group things by. Uh, I have chosen to do, uh, a few levels of grouping here, one by, uh, all, by all three columns that I’m selecting.
And then one grouping set for, uh, each of the columns individually. I could of course add stuff in here. Like I could add another line that says vote type ID and post ID and another one that says vote type ID and vote year and post ID and vote year. But just to keep, just to keep things relatively simple, I’m going to, um, I’m going to just do the three together and then the three individually.
There’s also this empty parentheses at the very end, which is going to be the global aggregate for this. So I’m going to start this thing running and you’ll, you’ll notice that I have a columnstore index up here. I have not created that yet. Uh, we’re going to save that for later.
What I want to show you here is just that, um, SQL Server, uh, SQL servers optimizer fights real good and hard against, uh, using any sort of batch mode stuff, uh, with these grouping sets queries. Uh, even like we’ll show you in a second with the columnstore index, but right, right about 30, 30 or so seconds for this thing. And you know, grouping sets queries, they tend to need to process a lot of data.
Like you’re talking about like, you know, doing like big aggregations of stuff. It would seem a fairly natural fit for the optimizer to want to use is like batch mode for these things, but we just don’t get any. You can kind of figure that out from the query plan by looking at the choice of operators, not always, but in some of these things.
So like, you know, first we have, uh, parallel exchanges, parallel exchanges, like there’s one here, repartition streams, repartition streams, repartition streams, distribute streams. Uh, that, well, that’s a filter. That’s not quite it.
Uh, gather streams over here. None of the parallel exchanges support batch mode. There are operators in the plan that support batch mode, but they all, I, I, I, at least the last time I ran this, they all ran in row mode. So like there’s a sort at the end.
Uh, there’s a filter that runs in row mode, filter support batch mode. Uh, stream aggregates here. One, two, three, uh, well, one, one up there, one down there. Those don’t support batch mode.
The hashes do support batch mode, of course, but they all run in row mode. So this whole thing is kind of cooked. This whole, this whole thing takes 30. Well, actually we should go look.
Cause like that looks pretty funny. 43 seconds. And then I got 20 seconds, 43 seconds, 32 seconds, SQL Server. What happened? Let’s, let’s go, let’s go look at the actual tail of the tape over here in, uh, the query time stats. Hopefully my big head won’t be in the way.
So, uh, that, that one parallel operator lied to us. So we used 166 seconds of CPU time to get 32 seconds of wall clock time. So what we appreciate, we appreciate SQL servers willingness to use a parallel execution plan here.
We do not appreciate the lack of batch mode here for them. Look at that number of rows that we are selecting and aggregating. Gosh darn it, SQL Server.
Why won’t you use batch mode? So what I’m going to do is I’m going to kick off, uh, creating this columnstore index. And just so I don’t get yelled at later, I’m going to make sure that that semi colons in there. We don’t want to improv.
We don’t have a lack of termination here. Do we? I’m going to get that started and I’m going to show you what an equivalent query without the grouping sets stuff looks like. Uh, so we’re going to have one query at the beginning where we select and group by all three columns.
That’s the vote type ID, post ID and vote year. Uh, I’m using a little trick that I showed off in one of my earlier videos where I’m doing this little cross apply values trick. To have, uh, one source for this date, part year creation date thing.
Uh, so that I can reference that one. Uh, well, I can reference that a few times throughout the query without having to keep rewriting date, part year creation date. Now, of course, like you have to do more typing, right?
So we have one query that does all three columns and groups by all three. We have another query that groups by just vote type ID. We have another query that groups by just post ID.
We have another query that just groups by vote year. And then we have one query that, that essentially just like doesn’t aggregate on all of them. Now, if I run all, what I want to do is run all these together. Now, keep, remember that I just created a columnstore index here.
We wouldn’t need the columnstore index for this thing to run in batch mode. Uh, SQL Server would shoot, could shoot, can choose batch mode very naturally for this. Um, I’ll show you that at the end, but the important thing is that when you look at this plan, there’s two things.
There’s a couple few things about this that are much, much different from the plan that we just saw using the grouping sets method. One, there are no parallel exchange and exchanges in this until the very end, right? There’s one necessary one at the end where we do gather streams.
Uh, but there’s also no stream aggregates. Like we don’t have all the repartition stream stuff in here. And of course, if we look at, we look at the execution modes for all of these operators, these are all going to say batch mode, batch mode, batch mode. So this whole thing runs a lot faster.
If you look at the query time stats for this one, just cause it’s not, maybe not terribly obvious from the, from the query plan as a whole. Uh, we spend, uh, 1.13 seconds of CPU time for 1.8 seconds of elapsed time, right? So that’s a huge improvement, uh, using batch mode over, uh, like row mode execution for these queries.
Let’s go back up to the, the, uh, grouping sets function query. And let’s run this with the columnstore index in place. If we let this go for a little bit, right?
It does, it does go for a little bit still. Uh, it’s already slower than the, the union all method of doing this. Um, this thing will take about 12 or 13 seconds in total. Um, and the, the query plan is different, right?
Like with, with the, um, with the columnstore index in place, you don’t see all, like, you don’t see a lot of the repartition stream stuff, but there is still some real junk in the query plan. Like this whole section here, where, uh, like very early in the plan, we gather streams. That means we end a parallel zone right here and then we restart a parallel zone right here.
But in, inside of that serial zone, right? Where we, it’s like no more parallel and then back to parallel in here, we, we do a stream aggregate. Because this is just something that grouping sets has to do for the global aggregate.
Like I’ve, I tried all sorts of tricks to get rid of this. I tried an option hash group hint. Uh, I tried a query rule off hint to prevent the stream aggregate, but still it’s stuck in there. I don’t want to start getting into all that stuff in this video, but man, this thing was just very, very persistent.
And if we look at the query time stats for the grouping sets one, like we go into properties and we look at query time stats here. Like we were at 1.7 something seconds for the one that, uh, the union all, uh, format and like 13 seconds of CPU time here, we’re up to 30 seconds of CPU time and 10 seconds of elapsed time. So this isn’t like, you know, like when I say like, I don’t see this stuff very often.
I kind of get why, like, it’s not just that the syntax syntax is kind of like weird and difficult to remember, or even like occur to you. If you need to do this sort of thing. Uh, it’s that like, even if you, you, you, you’re like a steadfast remember of syntax, which I am generally not.
I need to refer back to, you know, stuff that I wrote before to remember like half the things that I want to do. Uh, like, even if you are really good at remembering this stuff, like you might try this and just be like, holy cow, this is slow. So let’s, um, uh, let’s, uh, get rid of the, the, uh, the columnstore index that I created.
And I just want to show you that kind of naturally, uh, the union all version of this does a lot better on its own. Just sort of naturally using batch mode. SQL Server is like, I’m not afraid of batch mode here, right?
Like, like, it’s not going to be as good as when we had the columnstore index to read from. So like, if you’re doing this kind of like big analytical aggregation stuff, there’s a, you know, very, very good chance that you’re just going to want to use a columnstore is like the source of your data anyway. But even just like getting some batch mode stuff, like this, this runs pretty well or runs better even without the columnstore index, like then the grouping sets version of the grouping sets version was like 30 seconds or something.
This is, this is like twice as fast without even having columnstore is like the, the source to read from the main thing that slows down is we have to read from the clustered index over and over again. Right? Like we do a lot of stuff in there, but even that’s happening with batch mode on road store. We just don’t have those.
We just don’t have that really nicely compressed columnstore index to really speed things up as we’re reading data. So we still kind of have like this IO bound portion of the query that, um, like, like it doesn’t have the good column source source to read from, but like, it still happens in batch mode. So it’s still an improvement. And for all of these branches, notice that we don’t have like, we don’t have that break where we have like the, the, like, like the serial zone with the stream aggregate.
We don’t have all the repartition streams in here. This all pretty cleanly just runs in batch mode and does everything in batch mode and is a lot faster just naturally. Like I, of course, like I, like I said, I would absolutely prefer to have the columnstore index as a data source for this type of thing. But even without that, even just getting batch mode on rowstore, uh, this ends up a lot faster than the, uh, the grouping sets alternative.
So like when you’re, when you’re writing these types of queries, you know, of course, like what you’re reading from is very important. Um, you know, rowstore indexes, just not, not quite the same jam as columnstore indexes for these big analytical agro ag, aggregative, uh, types of queries. But, uh, like, you know, depending, like often just how you write the query and I’m going to, I’m going to say something unfortunate generally.
In general, the more you type, the better off you are, right? Like, like taking, you know, shortcuts like this, this might, you might like learn this in some like shifty Microsoft DP exam. And, you know, think that you’re, you know, like you’re, you’re the hottest cake on the block, uh, knowing, knowing how to use grouping sets, but, you know, get, get out there in the real world with, you have to do anything practical on like a meaningful set of data, not like worldwide importer works or whatever.
You’re, you’re, you’re going to want to abandon that ship pretty quickly. So anyway, uh, thank you for watching. I hope you learned something.
I hope you enjoyed yourselves. Uh, and I will see you in the next video, uh, which, um, I think this is, this is going to be a Friday video. And then, um, the next one will be an office hours episode. And boy, just, we just have so many exciting things.
So many exciting things coming up, don’t we? All right. Anyway, thank you for watching. Thank you.
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.