Why Performance Tuners Need To Use The Right Type Of Join In SQL Server
Thanks for watching!
Video Summary
In this video, I delve into the importance of writing queries in a logically correct way, highlighting common pitfalls and offering practical advice to improve query performance. I share a client-inspired example where a left join was unnecessarily used, leading to a poor row-to-second ratio and inefficient parallel merge joins in the query plan. By changing the join type to an inner join without adding any indexes or making other changes, we significantly improved the query’s execution time from 9 seconds to just 3 seconds. This example underscores the importance of understanding the intent behind your queries and ensuring that the types of joins you use are necessary and appropriate for the task at hand.
Full Transcript
Erik Darling here with Darling Data. Darling Data. I don’t know why everything that attempts to do a transcription of my videos insists that I work for Darlene Data. Clearly saying Darling, not Darlene. Starting to hurt feelings a little bit. But today we’re going to talk about the importance of writing your queries in a logically correct way, which sounds like a very obvious thing that everyone should do and know how to do, but not everyone does. There is a profound sense of safetyism with people who write queries, either on their own or via an ORM, aka the lazy way. And this is a client-inspired query tuning exercise that I want to go through with you. Now, not just every query requires a left join. A lot of people freak out, think about, I don’t know what, but they just randomly sprinkle left joins everywhere just in case. And that’s not always necessary. Now, this tactic isn’t always going to work out. Sometimes you might need a left join. Sometimes changing the type of join might not make any sort of profound difference to your query, but it’s an important thing to keep in your brain.
as you are assessing queries that you are assessing queries that you are working on, either to first first write, first birth into the world, or one that you’re working on trying to make faster. All right? It’s a good thing to keep in mind. Think about the intent of your query, and if you actually need the type of join that you’re writing. All right? It’s a smart thing to do. So, this query has a couple supporting indexes.
They’re mostly supporting indexes. There’s one index that’s not supporting in here, and that’s part of why the query isn’t as fast as it could be, but you can’t always just add an index immediately. Some people have change controls. Some people are unfortunate enough to be on standard edition and not be able to just create indexes when handsome young consultants are working with them, and they need to do it later because they don’t want to block things because standard edition is proof that Microsoft hates you. So, let’s look at this query. And what we’re doing is looking for the top 10 users ordered by a few things down here that kind of take the need for a left join out of the equation.
We’re looking at people with the highest question score, the highest answer score, and the most posts. Now, if this were like the first day that Stack Overflow existed, and maybe like the first hour, and let’s say we had like 11 users, and we were really afraid that like two of them hadn’t posted anything yet, then you would probably need a left join to round out the like 10 rows that we need to make this report, right? Or make this page or whatever. Make this, look at our top 10 users. They’re amazing. Whatever.
But, like, this is the Stack Overflow 2013 copy of the database. By this writing, there were like 2.5 million rows in the users table, and the odds are pretty good. I’m not much of a bookie, but the odds are pretty darn good that 10 of them have made posts. Questions and answers. Questions and answers jammed into one table. Absolute madhouse. Who would do that? Who would establish a parent-child relationship all in one table? I don’t know. It’s probably on my list of things to do to split out questions and, well, I guess, I was going to say questions and answers, but questions and every other kind of post into two separate tables and demo how queries are better when you normalize like a decent human being.
But that’s for another day. Day far, far, far, far, far away from now. So, what we’re going to do is just run this query. Just as it is, we’ve got query plans turned on, so we don’t need to worry too much about missing anything important here. And we have a left join in our query, and we’re going to run this, and we’re going to wait 8 or 9 seconds. I always forget. Oh, that’s my hand. 4, 5, 6, 7, 8. Okay, 8 seconds.
It’s a pretty long time to get 10 rows, right? Not a good row-to-seconds ratio. I keep stressing the importance of the row-to-seconds ratio. 8 seconds for 10 rows? Very bad. It’s like a little bit more than one row a second. It’s a terrible, terrible ratio.
Again, not much of a bookie, but I would not want to put money on that horse. Might want you to put money on that horse, so I make some money, but, you know, whatever. So let’s look at this query plan. We can see from the query plan itself, it actually took about 8 and a half seconds.
So my initial estimate of 9 seconds was only off by 500 milliseconds. That’s actually a pretty good call there. It’s not like I run this thing 15 times or anything, right? Definitely not.
And this gets a pretty shabby query plan. Part of it is, of course, because I don’t have any kind of good index on the comments table, and so we spend about 2 seconds in this branch of the plan aggregating some data, and then aggregating data some more, and then sorting data.
Now, the reason why this is one of my least favorite types of query plans is because there are not one, but two parallel merge joins. Uno dos, right?
And personally, I think that the optimizer should cost parallel merge joins out of existence. They’re nothing but trouble. They are hassles, and they have caused so many performance problems that I’ve seen in my query tuning time that I just don’t know why they continue to exist except to keep my bar tabs paid, I guess.
So the problem with merge joins is that they expect sorted input, and when you expect sorted input, you create intra-thread dependencies, right? So parallel plan, you have multiple threads.
Those threads all rely on each other, putting stuff through things in order, especially around parallel exchanges. If things go really bad in a parallel plan, you’re going to end up with intra-query parallel deadlocks, you said.
You’re so smart. You could also end up with just exchange spills. Either way, you’re not in for a good time performance-wise. I hate these things.
I wish they’d go away. I wish it was just parallel nested loops or hash joins. Just, like, if it can’t be an adaptive join, it shouldn’t be in the picture. Get rid of it.
So anyway, I’m, like, half-joking. I suppose there’s a use for them. Aside from keeping my bar tabs paid, I just haven’t found it yet. So this query takes nine seconds. A lot of bad stuff goes on.
And what we’ll notice is if we write this query in a way that more accurately expresses what we’re looking for, because if we’re trying to find the users who may have the top ten most impact on the site, they’re people who have had to have made posts, right?
They’re not going to be non-posting people with high question or answer scores or a high number of posts. We’re just not going to find them. See, they’re people who have had to have done something in the database.
They must have. They must have. So we’re going to change that join to an inner join without adding any indexes, without changing anything else. We’re not going to update statistics.
We’re not going to defragment anything. We’re not going to restart SQL Server. We’re not going to recompile, optimize for unknown. We’re not going to do anything, any of these bizarre things that I see people do when trying to figure out why a query is slow. So we’re just going to change that left join to an inner join, because there’s no way we need to worry about preserving people who haven’t posted anything.
And this query immediately goes from eight and a half, I’m going to say nine seconds. Nine seconds. I’m going to round up in my favor.
Nine seconds down to three seconds just by doing the right type of join. Now, you might notice that the shape of the plan changed quite a bit, and you might notice that certain join types will remain nameless, mainly parallel merge joins, have been stricken from the record. We got a couple hash joins.
We got a nested loops join. And this is more typical of the type of parallel plan that I want to see when I’m tuning queries. I almost never want to see a parallel merge join unless I want to write a good demo about parallel merge joins being terrible.
So just to repeat myself a little bit here, just to make sure that this is utterly clippable, if anyone decides to stitch this or whatever they call it. So when you’re writing a query, when you’re tuning a query, make sure you understand the intent of the query. You don’t need to understand absolutely all of the business logic.
But you should understand the intent of the query and use some of your God-given gray meats to figure out if the types of joins you’re requesting SQL Server to do are necessary or even correct. Right? Right?
Because a lot of the times when I see people using left joins is because they had no idea what they were doing. And they were just like, I see a lot of other left joins here. I’m going to do a left join everywhere. And you can end up with, you know, not great performance if you are not writing queries the correct way. Now, again, inner joins won’t always be faster.
Left joins won’t always be slower. There may be times when that crosses over. But when you’re analyzing query stuff, you really do need to keep in mind that these choices of joins do matter. Right?
They do matter when you’re writing queries. And sometimes how much they matter, like to query correctness, can also have an impact on how fast your query is. So it’s a really good thing to keep in mind, to pay attention to, to make sure you understand what your query actually needs to do so that you don’t add superfluous join types to your query and end up with a bunch of parallel merge joins.
It’d be sad for you. I don’t know. Maybe you’d have to hire me, which wouldn’t be so sad.
Keep those bar tabs paid. So, thank you for watching. Hope you enjoyed yourselves. Hope you learned something. If you like this video, thumbs up is the absolute best way to show that you like this video.
If you like SQL Server content about performance tuning, other things, bar tabs, sports betting, you know, stuff like that. Subscribe to my channel so that you can get a notification every time I talk about performance tuning or bar tabs or sports betting. You know, got to keep things lively over here.
Anyway, I guess that’s probably about close enough. 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.