The SQL Server Performance Tasting Menu: How Common Table Expressions Don’t Help Performance

“Common” Is Not A Compliment

Video Summary

In this video, I delve into the intricacies of Common Table Expressions (CTEs) in SQL Server, addressing why they’re often misunderstood and misused. I explain that CTEs are not just about making migrations easier or achieving code parity; their true value lies in their ability to help with complex queries by allowing you to break down a query into more manageable parts. However, I caution viewers against relying on CTEs for performance benefits, as they don’t materialize and can lead to suboptimal execution plans due to repeated re-execution of the underlying syntax. To illustrate this point, I walk through an example where using a CTE led to multiple index seeks instead of just one, highlighting the importance of understanding how CTEs work and when other methods like temp tables might be more appropriate for ensuring performance stability.

Full Transcript

Oh, you’re still here! You poor soul. So, this is the at least third video. And this one is about CTE. And you will find out just why they’re called common. I think they’re they’re crappy, to be honest, because they’re misleading. And the reason I know they’re misleading is because a question that I frequently get during consulting engagements is, should I use a CTEor a temp table?

And the answer is, of course, no. That is not an or question. They are not comparable objects. It’s not a comparable way of doing things. Shut up! Beeping at me. Yes, that’s right. Even in a pandemic. Anyway, let’s get on with the show. So, why do we have CTE? Well, it’s not just to make migrations from Oracle. I don’t know, does anyone migrate from Postgres? I don’t know. But it’s not just to make migrations easy. It’s not just for code parity with other database platforms. No, no, no, no. There are very definite limitations to derived tables. And if you can’t already tell by the little squiggles that exist in this beautiful white snowscape of a document, SQL Server is not going to be able to run this query.

We get an invalid object name x. Why? Because we cannot reuse x after this point. We can run this query. We can say select there and we can select everything from x. But once we try to reuse x, SQL Server is like, I don’t know who that new phone, who this? And SQL Server just stops being able to know what x did.

It’s not keeping track of it. It’s not stalking its x’s. So this is why we have a CTE. Because with a CTE, SQL Server has no problem stalking its x’s. We can run this query just fine. We can reuse x as many times as we want.

But the very reason that this exists is also, I think, one of the greatest shortcomings and one of the biggest misgivings around CTE. Now if I run this query, where I’m going to have a CTE, I’m going to select data from, I should turn on query plans for this. If I run this, we have one index seek into the users table.

But if I union this CTEtogether a few times, I run this query, all of a sudden, we have not one, not two, but three seeks into the users table. CTE don’t materialize. Whenever you re-reference them like this, you need to re-execute the syntax inside of them. The same thing will happen, I didn’t mean to bring that up, with joins. Now I wrote this join in kind of a funny way, but the way that you write the join doesn’t matter. If we run this query and we look at the execution plan, we will see one, two, three seeks into the users table to execute the one, two, three joins that I do to my CTE.

So the first thing that you need to realize is that they don’t materialize. They are not a materialized data set. You cannot index a CTE. You can index the underlying table or tables, but the CTE itself can’t be material. There’s no like, like, with index, like, like, like, create this index when you, when you use a CTE. That doesn’t exist right now.

I don’t know if it ever will. Now, this will get worse and worse as you make syntax inside of the common table expression more complicated. And as you join to the common tables or re-reference the common table expression more and more, if I run this query and I’m not like, actually run, I’m just getting the plan for it. I’m getting the execution plan minus for this query. And I zoom way out. We can see that for each iteration of that CTE, we did quite a bit of work. We have like, we have the base query down here. And if we zoom in a little bit down here, we can see that for the three joins, we have one, two, three times we touch badges. And then we’ll have a one, two, three times we touch votes. And then one, two, three times we touch posts. So we, and that goes all the way down to here, where the one, two, three times that we touch the comments table. So if you ever write a query with a common table expression in it, and you happen to take a look at the execution plan and you think that, gosh, that’s a big complicated execution plan for this query. Well, it’s your fault. You didn’t listen to me. I tried to warn you. Of course, CTE can sometimes be helpful. Sometimes. The problem is that you might just get lucky with them being helpful, and that they may not remain helpful because there are no guarantees with CTE. None at all. So right now I have these two indexes available. I have this index on the users table called users, and it’s on creation date, reputation, and ID. And I have this index on the post table that’s on owner user ID and ID. And because I’m filtering on post type ID, I have post type ID as an included column. So I only want to find where post type ID equals one.

And the thing is, when I go and run a query that really well matches, well, I have index that really well match the join and the where clause for this query. But that index on the users table doesn’t quite cover all the columns I’m selecting from the users table. Well, something kind of unfortunate happens.

And that SQL Server doesn’t use our my, it doesn’t use my index, which makes me incredibly sad. And this query runs for a little bit longer than I’d like. So we can see SQL Server not using my nice narrow nonclustered index here. And we can see this query running for 1.2 seconds over here.

Of course, if I tell SQL Server, well, hey, I want you to use my index, we can get it to. But I want to show you something really quick first. This query SQL Server estimates will cost 92.1 query bucks. 92.1. Keep remember that number 92.1. If we come on down here, and we we use an index hint, we’d say SQL Server use this index. All right, this is case center that might that might not work. We’re going to make sure that we can use index. If we tell SQL Server use this index, please, you’ll notice that this query ran a lot faster. This query ran in 0.94 milliseconds. That’s under 100 milliseconds.

So I’m told, I’m going to check with my check with my legal team on that. So this ran a lot faster. But SQL Server thought that it would cost 126.7 query, just 126. Let’s just call it 0.8 query. 0.79 is 0.8. So I’m going to round up. I’m going to throw a ceiling on that.

126.8 query bucks to run this one that ran much faster. So like I said in a previous video, cost means nothing. Stop paying attention to it. It has nothing to do with reality. It’s an estimate. It’s not an estimate for your hardware. It’s a general estimate for everyone’s hardware.

Isn’t that funny? So we have this query. And we have this query that when we tell SQL Server to use a narrow nonclustered index, it is much faster. But it’s more expensive. So SQL Server doesn’t choose it naturally. The reason it’s more expensive is all over here. This key lookup. SQL Server uses our narrow nonclustered index right here. And we immediately do a key lookup here to go get all those other columns that I selected. There’s no predicate in here. There’s a seek predicate, which represents the relationship between the nonclustered index and the clustered index.

That’s how we find each row. We seek into the non-clustered, seek into the clustered index from the nonclustered index using the ID column. And then we go fetch all of the columns that we don’t have in our nonclustered index. And this is where SQL Server estimates a lot of the costs.

68.2 of the 126.8 query bucks is in this operator. Gosh, you’re expensive. You’re so expensive, but you’re so fast. You’re so much faster than that other query. Well, one way to get SQL Server to use our narrow nonclustered index is to think about our query a little bit more differently. Sometimes I wonder if at some point we’re going to just have bridge words that encompass a couple different words.

Like we could have effect spelled with an A-E. Or we could say like fewer less. Or we could have like a where that’s like W-H-E-R-E. And like there that’s like T-H-E-I apostrophe Y-R-E. That’s something that covers everything so that you can never make another typo. I hope it never happens. I wonder if that’s the direction we’re heading as a society because there’s some not good things out there happening to the English language.

Some true butchery. And I’m responsible for some of it even. But if we think about our query a little bit, if we separate our query mentally before we start writing it, we need to think about the columns that we’re working with. We need to put them into two groups. We have informational columns. Those are columns that we’re just showing to people.

Columns that we’re only going to select, only going to display. And then we have relational columns. Those are columns that we’re going to join on, filter on when our where clause, maybe group by, maybe order by, maybe do some sum summing, averaging, row numbering, whatever. We do something with them other than just showing that.

They’re not just window dressing. So if we think about our query, we separate our query out into like mentally into those two logical groups of columns. All right, we have here we’re only going to have a narrow select list. And then we’re going to do all our relational work inside of this CTE.

And then when we get outside of this CTE, then we’ll select everything from the users table here. All right, we’re joining back to the users. We’re doing sort of a self-join here, but only to the rows that we get out of here. All right, so we’re going to narrow our result set down as much as we can.

And we’re going to use the primary key of the users table to our advantage. And we’re going to join it to itself over here. And when we do that, we no longer have to hint SQL Server to use the index.

All right, SQL Server uses the index on its own. But something else changes about this query, too. Now, with a key lookup like we saw in the last plan, the key lookup can’t happen at two different stages in the execution plan.

Whenever we do our seek into the nonclustered index, we have to also immediately afterwards nested loops join and go fetch the stuff for the key lookup out in the rest of the execution plan. That’s not true here when we sort of rewrite things manually with a self-join. Notice that the clustered index seek into the users table to go get all of our columns happens way over here.

So for the index seek into the users table and the index scan of the post table, we are only bringing a very narrow set of rows across. That means that for all of these operations that happen leading up to here, where we go get all those columns out, we can ask for much less memory to perform all that stuff, especially that sort operation.

Pretty cool, huh? So when we manually write a key lookup, SQL Server can detach those things. The problem is that because this is a CTE, we’re not guaranteed to always get that same execution plan.

For example, if we go back a little bit further in time, right? We’re going to go back from up here. We are only going back through 2013-12-10.

Down here, we’re going to go back to 2012-12-10. And if we go do this and we say run this query, it’s going to slow down. And we’re going to get a different query plan.

So now SQL Server kind of did what it did before, right? Where we have the users table where we seek into there. We sort data here.

We do our fake key lookup self-join here. And then we do a merge join here. And we do some stuff here. And I don’t know.

It’s just all exhausting. It’s just all exhausting. And that takes longer than I would want it to.

Now, granted, we return a lot more rows. 366,000 rows get returned here. But that’s not really the point. Now, the CTEworked well initially.

But at a certain point, the plan changed. It got less effective. And I don’t know. I… SQL Server was free to rewrite our query internally.

The optimizer was free to rewrite our query in any way it wanted. However, we can guarantee things a little bit better if we actually materialize them. So what I’m going to do is stick that initial query into a temp table.

And then select data out. And you’ll see that that happens a bit faster with a bit more stability. Now, this part is guaranteed to happen.

All right? And then when we go and get more data out, we will see the same execution plan happen. All right? So we’ll run this. And notice this part’s not going to get faster because we’re still going to have to return a lot of rows.

But the two individual queries execute a lot faster because we use the best plan for both… Or rather, I think, probably the best possible plan. If we spent, like, a long time tuning stuff, we might be able to get better plans out of that.

But I’m pretty happy. And overall, the only reason that that thing actually runs for three seconds, like it says down here, is because we returned a lot of rows.

Neither of these query plans runs anywhere near three seconds. Even combined, they’re probably just about half a second. A little over half a second. But if we were only to return the top 3,006 rows, like we did for that first query, this would happen very quickly again.

Not a lot of influence here, but the overall results returned more quickly because SSMS is a dirty pig. And it takes forever to fill up buffers and display data and, like, format stuff for this stupid Excel sheet that everyone thinks is what a database looks like. So there’s that.

Anyway, what I hope you learned here is that when you’re thinking about CTE and temp tables, realize that they are two fundamentally different things. CTE, while they can sometimes be useful to help you separate parts of the query out, nothing is materialized.

They do not necessarily reduce complexity for the optimizer. So the optimizer will look at those CTE and is free to rewrite things across CTE, within CTE, CTE and their final finishing query. There is no performance benefit to a CTE.

There are some interesting things you can do with CTE if you throw a top in there, but that’s a little bit beyond what we’re going to go into in this particular demo. We’ll talk about that in some other ones down the line.

Anyway, that’s about all I have to say about CTE. If you want truly materialized data, you must use a temporary object, something where you can store results. SQL Server can come up with statistics about those results and only those results.

Maybe you can index those results in a different way than you would your underlying tables. That makes it a little bit more palatable. And there’s all sorts of stuff that you can do in there as well that can help performance issues.

So anyway, I’m going to stop here. Finally. Finally.

And I’m going to record the next video and do some YouTubing, uploading and processing. All sorts of exhausting, boring, dull things that I have the leisure time to do these days. So I will see you over in the next video.

Thank you for watching. Hope you learned something. Hope you enjoy yourself. Et cetera and so forth. Ah, goodbye enough. you

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 performance problems quickly.