Why You Should Avoid People Who Tell You To Avoid Subqueries In SQL Server
Thanks for watching!
Video Summary
In this video, I delve into the world of SQL Server performance tuning and take a critical look at the uninformed masses posing as experts on platforms like LinkedIn. These individuals often share advice such as avoiding subqueries and using `SELECT *`, which is not always sound or necessary. I explain why these blanket statements are misguided, providing examples from my own query analysis to illustrate that context matters greatly. By examining a specific query with multiple subqueries, I demonstrate how the real issue lies in inadequate indexing rather than the use of subqueries themselves. The video concludes with practical advice on creating an index that supports all parts of the query efficiently, leading to significant performance improvements.
Full Transcript
Erik Darling here with Erik Darling Data. Make sure that my enunciation is working. My new AI enunciation chip is in place. And I’m recording this video early today because this is the situation that we are dealing with currently in my neck of the woods. And this is, I think that there’s probably a good line out of Ferris Bueller’s Day Off about, how can I be expected to care about SQL Server on a day like this? The only thing we don’t like about today is this little guy down here. We don’t like this one. Because of this, there is a very high chance that I am going to have to turn off my microphone at some point in order to not sneeze in your face and ears during the recording of this video. I’ve already had to use allergy eye drops four times today because my vision would let my eyes get dry. My vision would start to get a little blurry and my vision would start to get a little blurry and I’d be like, oh, the Lord’s taking me. Blurry vision is the first sign. But it turns out just the, we thoroughbred nerds here at Darling Data have all of the allergies and all of the symptoms that come along with those allergies and one of them being dry, itchy eyes. So today we’re going to talk about my least favorite kind of people in the world.
And that is the uninformed masses posing as performance tuning experts on the internet, primarily on places like LinkedIn where people are supposed to be like visibly professional and know what they’re talking about. And there are people who say things like hot SQL tuning tips, hot performance tips. There’s like little fire emojis next to all these great ideas like don’t use select star, like avoid distinct. And like, like don’t use sub queries. And all of that advice is dumb. Like unmitigated dumb. There are times when select star is not harmful. There are times when distinct is not only necessary, but entirely helpful. And there are times when sub queries are just fine. Most of the people who say these things and make these blanket statements have no idea what they’re talking about. They’re like fitness people who are just like, yeah, bro, just eat less and exercise more. You can look like Ronnie Coleman. You can’t, you don’t, let’s not just diet and exercise.
You know, people who are just like, oh, well, you need to, you need to front squat. You know, those, the entire crossfit model with, with the wads and the people just doing idiotic, saying idiotic things and doing idiotic things in order to confuse their muscles. It’s a very, very sad state of affairs. I only know this because I, I, most of my feed is either databases or people doing squats. So it’s, it’s about what my internet looks like. So yeah, um, we’ve got this query here and this query is, you know, got a bunch of sub queries in it. All, they all go to the badges table.
All right. Well, every single one of them goes to the badges table. Even this, even this little exists down here. What you may not know is that exists is also a sub query. All right. This is a sub query. You can, you can, you know that exists is a sub query because if you try to create an indexed view and there’s an exists in it, you will get an error when SQL Server will say sub queries aren’t allowed in indexed views.
And you leave it. What? But it’s, it just exists. It’s just a little joined. What are you worried about? What are you worried about SQL Server? What idiot designed indexed views to not be able to use exists? Who would do that? Well, someone at Microsoft, let’s call them Sam, someone at Microsoft.
Microsoft. So, uh, this query, uh, is it admittedly on the slow side? Is it executed for, if I write, lift up the correct arm, you can probably see under my armpit there about 31 seconds. Uh, that’s not great, but you know, let’s look, let’s look at why. Let’s see. We’re, we’re using sub queries. Why, why shouldn’t we use sub queries?
A lot of people who tell you not to use sub queries will say, do a join instead. I’ve got bad news for anyone who thinks that a sub query does not end up doing a join because I can guarantee you, there are many joins in this query plan to implement all of those sub queries. There are a bunch of them.
A lot of people will also say that sub queries can only use nested loops joins, but there are folks out there. I got to be honest with you. SQL Server lies and obfuscates and, uh, is, is, you know, uh, less than forthcoming about many things. But these are all hash joins. Every single one of them, all hash joins.
Okay. So what, what, what are the slow parts of our execution plan? We’ve got, we’ve got, I think like five or six sub queries in the select list. So are they all slow? Not really. Only a couple of them are slow. And a couple of them are slow because we don’t have a good index to support the query that we’re running. And that’s really the, the root cause of, of many performance problems is just, you don’t have a good index in place to, to answer the question that your query is asking.
Indexing data is really important. And a lot of people are probably the same types of people who tell you to avoid sub queries are probably the same types of people who have no idea what the hell an index does in a database. So if we go and look, there are really only two slow parts of this query plan. I didn’t need that tool tip. SQL Server management studio. There are really only two slow parts of this query. We have two eager index pools that get built in here.
And these are both admittedly, they, they, they, they, they are using nested loops joins. And we, we, and just based on this query pattern and based on my, my just, you know, wizard like knowledge of SQL servers, query optimizer, I know exactly which two sub queries are causing these eager index pools. It’s, it’s going to be these first two, right? If I, if I quote this out, rather if I quote these two sub queries out with the top ones in them, and I rerun this query with all the rest of the sub queries still intact, boy, oh boy, we get a pretty fast execution plan.
And boy, oh boy, look at, look at every single one of these hash joins. We have no nested loops joins. What happened? Did, did someone, did someone on the internet lie to us maybe about sub queries, not being able to use hash joins or only being able to use nested loops joins?
Is someone on the internet stupid or dishonest somewhere in between? Is it, is it malice or ignorance? We’ll never know until we capture them and torture them.
Get the truth by hook or by crook. So wise man once said, So this is really just a case of us having inadequate indexes to, to answer a couple of the questions that our queries have attempted to ask of our data. And if we go and create this index here on the badges table, what we’re going to do is we’re going to fully support not only the two sub queries that we had up, up in the top that were causing the eager index pools, we’re going to help all of the sub queries really.
And this, this index only took three seconds to create. So we’re doing pretty good there. And, and since every single sub query is correlated between user ID and ID, right? We have user ID as a leading column.
And since some of these queries are also ordering, or at least one of them is ordering by date, I’m going to have that as a second key column. And since some of them are selecting name and some of them are selecting date, we’re also going to have name as an included column.
Right now, if we rerun this with all of our sub queries in place, all of these terrible, awful, no good, very bad sub queries. Well, the thing finishes instantly, doesn’t it?
Right? And oh, oh, oh, oh, no, but we have a bunch of nested loops joins now. That’s that, that’s obviously a big performance problem. Right?
All our sub queries using nested loops joins. How will we recover? How will we ever recover from this query that runs in 67 milliseconds? How will we do it? What are we going to tell our, what are we going to tell our boss?
What are we going to tell our wife, our spouses, our children? Like, I mean, you know, if it were me at work, it would be like, what am I going to come home and tell my wife and kids that daddy’s a failure? Because he used sub queries.
And with that, I’m going to end my Friday. Because that’s enough. And like I said before, it’s far too nice a day for me to care about SQL Server.
So, I hope you enjoyed yourselves. I hope you learned something. If you like this sort of SQL Server content, you can join nearly 3,461 other people who subscribe to this channel and watch every single video that I post the whole way through.
If you like this video, thumbs up is appreciated. So are nice comments. I’m going to go have a nice long weekend now.
I’ll miss you. I love you. It’s been lovely. It’s been a great video. But it’s time for me to go have margaritas. And whatever else comes along.
I’ve recently discovered a strong affection for vodka martinis. My wife steals all the olives out of them. That’s okay. Olives aren’t really my thing anyway. But those have been hitting the spot lately, too.
So maybe some margaritas, maybe some martinis, but not in the same glass. It’s a recipe for handcuffs.
Not the bedroom kind of handcuffs with the fuzzy stuff on them. The metal ones that really hurt your wrists. I mean, I guess that could go either way, depending on what you’re into.
But anyway, let’s end that here. Thank you for watching. I will see you, depending on how the weekend goes, if the margaritas and martinis maybe have their way with me.
I might not see you for a while. But assuming that all goes well, I will be back to my regular blogging and recording schedule just around Tuesday of next week.
So I do hope that everyone out there has a great weekend. Except the people who tell you to avoid subqueries. I hope that you suffer tremendously.
You deserve it. Thanks 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.