How To Write SQL Server Queries Correctly: Subqueries
Thanks for watching!
Video Summary
In this video, I delve into subqueries in SQL Server, addressing common misconceptions and providing practical insights. I start by challenging the notion that subqueries always run once per row, explaining how their performance can vary based on the join type chosen by the optimizer. Throughout the video, I share examples of when to use subqueries effectively, emphasizing the importance of good supporting indexes for optimal performance. By walking through a detailed query example, I demonstrate how eager index spools in the estimated plan can indicate potential performance issues and show how forcing SQL Server to use specific indexes can significantly improve execution times. The video also explores scenarios where collapsing repetitive subqueries into a single apply operation can enhance efficiency, although this isn’t always necessary or beneficial. Overall, it’s a deep dive into understanding and leveraging subqueries for better query optimization in your SQL Server environment.
Full Transcript
Erik Darling here with Darling Data. And it’s just, you know, me and Bats here, kicking it. Feeling real twinsy here. Mwah! Love you Bats. In today’s video, we’re going to talk about Intel updates getting the hell off my screen. Intel. What can Intel get right these days? That’s the big question. Anyway, in today’s video, we’re going to talk about subqueries. Oh dear. Now, in the last video, I did foreshadow this a little bit because we were talking about exists and not exists. And how a lot of people who I end up working with think that exists and not exists are bad because they’re subqueries. And they just have this thing against subqueries. So like subqueries, oh, they always execute once per row. Oh, they’re slow. They’re not as good as joins. They have these preconceived notions. They have these preconceived notions that certain things are just always true across every query ever. And that they know best and better. And they have some kind of authority in the matter. They’ve tested every conceivable query that you could ever write with every index and every database and every cardinality estimation model. And they just know everything is always true the way that they’re going to be.
They’re often wrong. And that’s what we’re going to talk about today. So before we do that, let’s talk about money. Everyone’s favorite subject, unless you ask them how much of it they have, because then things are good. That’s when people get uncomfortable. If you would like to become a channel member, you can do so for as little as four bucks a month. It’s just a nice way to say thank you for all of the content that I produce. You can do so for as little as five years. You can do that by clicking in the link in the video description that says, like, become a member. Cool. If you don’t feel like giving me four bucks a month, you can like, you can comment, you can subscribe, you can make different numbers go up, which also always fills my heart with joy and, you know, makes me feel less alone in the universe. If the things I’m talking about during these videos make you think, wow, that Erik Darling sure is good at SQL Server, you’d be right.
And I’m also available for consulting in SQL Server matters like these. And as always, my rates are reasonable. If you would like some very high quality, very low cost SQL Server content that you can buy once and will continue to be yours and you’ll have access to it for the rest of your life for about 150 US dollars, you can use the link in the video description. Or you can go to that URL right there and put in that discount code and you can get all 24 hours of my performance tuning training for the one time low cost of 150 bucks. No upcoming events. No upcoming events. It is the end of the year. I have no interest. 2025. I will have lots of interest. Anyway, let’s talk about sub queries because they’re a lot of fun.
So we’re going to talk about sub queries in the select list. I like them because you can use them to skip a lot of additional join logic. When you write a join in a query, the optimizer will do all sorts of funny things, wondering about where that join would best be placed in your query plan. It doesn’t really do the same thing with sub queries. Now, when people start saying that sub queries run once per row, they are not entirely wrong. But the thing is, if you write a join, that join might also run once per row too. If you write a sub query and SQL Server uses a nested loops join, and let’s just say that your query returns 1000 rows.
Yes, that sub query will run 1000 times to produce a result in a nested loops join. If you write a join that does whatever your sub query wants to do, and SQL Server chooses a nested loops join, your query might actually run, your sub query might actually run, or sorry, your join might actually run a lot more times. Because that join might happen way earlier in the query plan. And let’s say, rather than just running 1000 times at the end, what if it gets joined to a big table, and it has to run lots of times? Hmm. Gosh. This sure do get confusing.
So when people say things like, sub queries run once per row, well, lots of things can run once per row. It all depends on the type of physical join that the optimizer chooses to implement that semantically correct query logic physically in the query plan. Hash joins, merge joins, they typically do one big seek or scan of an inner table, bring a bunch of data back.
Nested loops joins, you know the algorithm, take a row from the outer input, send it to the nested loop, go do something in the inner input, grab a row here, bup, bup, bup, grab another row, bup, bup, bup, that is also once per row. No. Ah. Ha ha ha. My rates are reasonable.
Anyway, sub queries do have some limitations. There are times when you can’t use them, which is okay. Everything’s got to have limitations. I have limitations.
I don’t know anything about Oracle. I don’t know anything about cheap wine. Except not to buy it. I don’t know anything about…
I don’t know. What’s it? That’s something else. Let’s go on. Anyway, but if you use sub queries in the right way, they can be an excellent method to retrieve some calculation result without worrying about what kind of join you’re doing, and how the optimizer might try to throw that join into the mix of, let’s face it, I know your queries.
There are 42 left joins and 11 inner joins and 13 cross joins already. Throw another join into the mix. Why not?
Well, it could get worse, you buffoon. Since sub queries are in the select list, it is sort of like doing an outer join because the sub query is not actually allowed to eliminate any results. It takes the whatever, however many rows are going to get projected from everything from like the from down, your from join where, group by, stuff like that.
And it takes that result and it says, sure, for every row that I’m going to get out of this whole jumble of things that user x just did, I’m going to go run this query to get a result for the row, which could be a nested loops join or it could be not a nested loops join. But either way, it’s going to be an outer join because it’s not going to filter anything from the results.
So the optimizer doesn’t have to care as much about where that join gets placed. It’s usually going to be way to the left in the query plan because that’s where that’s the left outer join that’s not going to eliminate anything goes because we need to get all the stuff that we’re going to actually do stuff for first. So that’s good.
It’s all good, wonderful things about sub queries. And the optimizer is generally smart enough to retrieve data for the select list sub queries after all the other joining and filtering is done. So they can be evaluated for as few rows as possible.
There are, of course, bad ways to write queries that might end up with a query plan that contradicts that statement. But I’m not in the business of writing bad queries, in the business of writing good queries that run fast. So the most important thing that you can do as a developer, if you’re going to write sub queries, or really almost any kind of query in general, is to make sure you have good supporting indexes for them.
So this is where we’re going to talk a little bit about performance before we talk about other stuff. Now, I’ve already created good indexes to support my sub queries. I already have them.
But what I want to show you is what a query plan will look like. I’m forcing SQL Server to use the clustered index for each of these three sub queries. What I want to show you is what a query plan will look like when sub queries in the select list are going to be slow.
So let’s just get an estimated plan for this. And the important thing that I want to show you is over here. If you have a query, we’re talking primarily about sub queries in the select list, where you can see this happen.
But if you have any query, ever, and you look at the estimated query plan, or you’re looking at something from the plan cache or something in query store, and you see eager index spools like these, this and this, being built off a large table, like, say, the POST table in the Stack Overflow database, that means that this query is probably going to be a lot slower than you would hope. This query is not going to be in for a good time.
SQL Server is using a single thread to scan the POST table once there and once where my head is. So it’s scanning that twice. It can only use a single thread because it’s building an eager index pool here and here. So this isn’t actually even going parallel.
Sorry, this and this aren’t actually even going parallel. Even though they have parallelism operators on them, you can only build an eager index pool with a single thread. This is Microsoft, once again, kicking standard edition users when they’re down because they never want you to be able to build an index in parallel.
No, nothing for you. So that happens, right? Like, this is what a query plan will look like when sub queries are going to be bad.
Always keep an eye out for this. No matter what kind of query you’re writing, if you see eager index pools being built off large tables like the POST table, you’re either missing an index that would really help SQL Server, or the optimizer is making a buffoonish choice and you need to add in a force seek hint.
Because the optimizer will sometimes build an index pool off a perfectly good index that it could have seeked into. I brought this up to Microsoft and Microsoft does what it usually does and shrugs and says, Oh, sorry, we spent $70 billion on AI.
We can’t fix basic stuff in SQL Server. So that’s cool. Anyway, let’s move on. And let me show you, I’ve quoted out the index hint here. So now SQL Server is going to be free to use the indexes that I have created that are good for our sub queries.
And what you’ll see is that SQL Server does indeed choose nested loops. So these sub queries do run once per row. That does happen.
It do be like that. But you could write any kind of join. You could write apply. You could write cross apply. You could write outer apply. You could write a regular join. You could write a derived join. And SQL Server might choose nested loops for it.
In which case, it would run once per row anyway. It gads. I’ve been gapped. Anyway, if we run this query, and I’m actually going to give, oh, you know, I should turn on query plans.
That would help, right? If we run this query, I want you to note that it runs very quickly. We don’t spend a long time doing anything in this query. In fact, if we go look at the execution plan, it finishes in 23 milliseconds.
This doesn’t feel like sub queries being slow to me. This also doesn’t feel like there being a very big penalty for three sub queries running once per row.
We don’t really do much of anything in here. It’s just this final sub query that does a little bit of extra work. These seeks are very fast.
We get zeros in here. It’s just this final count one that does, you know, any sort of work. We get 22 milliseconds of work across those two operators. So that’s really not all that awful.
All right, this is a fairly quick query. Now, there are times when you have sub queries that have very, very common sub expressions, right?
Like all of these queries are doing something pretty similar. All three of them are correlating on the post type ID column, right?
But this is looking for post type ID one. This is looking for post type ID two. They’re all correlating on owner user ID equals user ID. They’re all three of them are doing that.
And all three of them are going to the post table. So there are times when very repetitive sub queries like this can be collapsed into a single apply and can be faster.
There absolutely can happen. Not going to BS you on that. But typically, that is when the sub query is rather complex.
What I see a lot in some client query plans when there’s a lot of complexity like this is let’s say that the query runs for one second.
And let’s say there are 100 operators in it, right? In the query plan because it’s a big complex query plan. And I’m running to get the actual plan and I start looking at operator times.
And there’s not really a single part of the plan that really contributes to that one second. There are lots and lots of little parts of the plan that all contribute to it taking one second.
When I see that and I need to make it faster than one second, my goal is to reduce the complexity of the plan which can, which in part of that is reducing repetitive sub queries in the plan or repetitive, just let’s just, let’s just even go a little further than that.
Let’s just say collapsing repetitive expressions in the plan. That can be a really useful trick. Okay. So I’m like, no, like you can, there are times when that makes sense to do.
This just doesn’t happen to be one of them. So if, if let’s say that, you know, like we, like we look at this query and we think, oh, I think that those, all three of those sub queries are cut, like, you know, we don’t, we 23 milliseconds.
We need it to be faster. Now let’s say that we wanted to be, this to be quick. And let’s say we, we had a mental problem with making three round trips to the post table to do the stuff that we were talking, that we were doing up there.
We have two ways that we could write that. We could rewrite this query, right? We could use a left, a derived left join, right?
Because a number sub queries in the select list will always be outer joins because they’re, they’re not filtering out any rows, any rows that qualify to be projected from the query. We want to find what the, the, whatever calculation in this, in the sub query.
So we could rewrite this as a derived left join. We could get the max for post type ID one, the max for post type ID two, and the total count. And we are looking for, of course, where post type ID is in one or two.
So we could do this and this would still be reasonably fast, but it is not 23 milliseconds fast. This is 59 milliseconds.
We spend 45 milliseconds seeking into the post table, and then another 14 milliseconds aggregating that data, right?
Because 59 minus 45 is 14. And this query ends up being just, just a little more than twice as slow as the three separate sub queries. We could even rewrite this as an outer apply, right?
With the exact same logic. Because remember, no cross apply because we’re not restricting rows. We’re just applying, we’re applying a calculation to the result, right?
We can run this, and this gets an identical plan to the derived left join, and ends up at 59 milliseconds, right?
We get the exact same query and timing from both of those. So in this case, making the three separate round trips is about twice as efficient as making a single, making a single trip to the table. Now, query rewrites to use specific syntax arrangements are not available in ORMs generally.
Many times we’re working with clients, we’ll stumble across really, really awful application generated queries.
I’ll, you know, we’ll look at them, I’ll be like, here’s a useful rewrite. Look how much faster this goes. We are twice as fast. We are three times as fast. We are 10, 100 times, 1,000 times as fast.
Look at how much better things could be if you weren’t using that ORM. Maybe, maybe we could take this, that, this query that the ORM is causing, is causing problems the way it generates, and maybe we could put that in a stored procedure with the way I’ve rewritten it, and make your life easier and better.
And they’re like, no, it’s ORMs only. And I say, okay, well, how, how would you use your ORM to build the query in this way? And they’re like, I don’t know, we can’t do that.
Okay, so, your only option is, is to stick with an ORM that builds an inefficient query, and you’re just, you’re just going to live with that, because you’re afraid of the stored procedures.
This is, this, this happens quite a bit, and like, I’m like, hmm. So, what, what, what should, what should we do here? What would you like me to tell you? What, what, how can I, how can I get this across to you in a different way?
Your ORM is good up to a certain point. These queries have reached the point where it is no longer good. You need a different API into the database, the stored procedure is just an API, right?
All it is is an access layer into the database. You can make this better. You can fix things. We can, we can improve things. All we have to do is get away from these ORMs. Not everything that, that, that, that, that, code produces is good.
Right? Built a query with code. The query sucks. Maybe, maybe the code sucks too. I don’t know. But, for the most part, people just don’t know how to improve that.
There’s really no, not a lot of fine grain control there. It’s up to you to rewrite queries with a better arrangement so that they can be faster. Now, in this case, both of the attempts at rewrites resulted in an identical query plan.
The optimizer did a fine job here, but both of the single trip queries were, but little bit over twice as slow than the original. In this case, the difference is absolutely microscopic, right?
It’s the difference between like 60 milliseconds and like 20 milliseconds, right? Not anything that we’re going to get crazy about, but I just do want you to see that like making those three round trips was more efficient than making the single round trip.
For me, the real advantage of writing out the three separate sub queries is to better understand which of those sub queries does work. You know, it’s almost the same thing.
And we’re going to talk about this more with CTE, but it’s very, very similar to that pattern where when I see lots of CTE being used in the single query, you know, one of the first things that I do is I start just individualizing those common table expressions as select into temp tables because it helps me figure out exactly which point in the CTE I can like has a, is like really slow or causing problems or causing bad estimations that trickle down to the rest of the CTE and where I can start fixing things.
And maybe, you know, sometimes the, sometimes the end result is that I need to use the temp tables. And sometimes the end result is that I can make meaningful changes within the CTE to make those faster. And we can stick with the original.
Now, if, like I was saying earlier, if these sub queries were a lot more complex, excuse me, and they had like a lot more going on in them, like, like we had to like find the top one, but then join to something else and have like an exist clause.
And, you know, we were just doing a whole lot more work in each of these. Then I would probably be a little bit more keen on like collapsing this stuff into one either derived join or apply, outer apply sub query or just like maybe dumping like, uh, where is it?
Sorry, up here a little bit. Maybe I would just dump this into a temp table with like an exists clause. Remember the exists in the last video with an exists on users so that I could filter out the size of this and, uh, you know, only bring rows in there and then just join the temp table to the, to the users table and bring that out.
Sometimes that can be a good approach too, but it all depends on, well, a lot of local factors. Again, a lot of this stuff does depend on local factors, but anyway, so sub queries in the select list, not always the worst choice.
Um, whenever someone says the row by row, well, it depends on what kind of join SQL Server implements to, uh, or rather what kind of physical join SQL Server implements.
to logically implement your sub query. If it’s nested loops, yes, it’s row by row, but you could get row by row from nested loops. If you write a join or an apply or anything else, all of these things can use nested loops and also be row by row.
So don’t let people drag you down with that because they’re idiots and they don’t know what they’re talking about. Be smart. Say, and say to them, I know what I’m doing.
Write the query. Show them it’s faster or show them it’s not slow. Show them it’s no different, whatever, but whatever, whatever you do, don’t let people get away with nonsense.
Don’t let people get away with their chat GPT responses to SQL, about SQL Server stuff. Cause it’s not always smart. It’s not always right.
I spend a lot of time with the AIs trying to get something good out of them. And it’s really hard. They’re abysmal places. Abysmal.
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I hope that you will continue to write correlated subqueries. Because, gosh darn it, they can be pretty useful.
Alright. Cool. Well, I’m out of here. Goodbye.
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.