SQL Server Performance Problems When Joins Have No Equality Predicate
Thanks for watching!
Video Summary
In this video, I delve into the world of non-equality predicate joins in SQL Server, specifically focusing on nested loops joins without equality predicates. You’ll see how these can lead to significant performance issues, even when indexes are present. I explore why parallel nested loops queries with guaranteed one-row outer parts perform much better and discuss the downsides when this guarantee is broken. Additionally, I share practical solutions, such as introducing computed columns or using hash-based joins, to mitigate these problems and achieve more efficient query plans. If you’re dealing with similar performance challenges in your own SQL Server environment, be sure to check out my training resources for expert guidance on tuning and optimizing queries.
Full Transcript
Your friend, Erik Darling here with Darling Data. And today, actually I should say tonight’s video. We’re doing some night recording for reasons that I don’t have to explain to you. We’re going to be talking about da-da-da-da joins with no equality predicates because, gosh, have I seen these just cause a disgusting amount of performance problems in my time. Part of the problem with joins with no equality predicates is that it’s a disgusting amount of performance problems in my time. So, the problem with no equality predicates is that it’s a disgusting amount of performance that I’m using in my time, but also I want to be talking about. And what we’ll find is that even with great indexes in place, there is just absolutely no helping some queries. The other thing we’re going to find is that sometimes we need to introduce some sort of hacky, hashy, equality-predicate type thing, so that we don’t have to be able to do that.
we can get reasonable performance out of our queries. But before we dig into all that, you know what time it is. It’s time for me to tell you how to give me money.
You can sign up for very low-cost memberships to say thank you for all the videos, all the time and stuff that goes into these miraculous things that has bit my tongue. If you can’t, they’re like $4 a month, and there’s a link in the description that you can click on to join the channel.
If you’re all out of $4, you can always like these videos. You can engage and interact with me in other ways that I find appealing. You can comment on the videos.
The comment sections have been great lately, so thank you to everyone who continues to do that. And you can also subscribe to the channel for the absolutely low, low price of $0. and you can get notified also for the low price of $0 every time one of these things goes live.
If you are the type of person who says, gosh, we could use Erik Darling’s help with our SQL servers, these are the types of things that I excel at.
I’ve done over 700 of them now just with Darling Data as an independent consultant. Well, I mean combined, not like each. That would be nuts.
And that’s not even my entire consulting career. Imagine that. So you can hire me for all those things. If you would like some low-cost, incredibly high-quality SQL Server training, all the performance tuning in the world, really, I have all of it.
I mean, what else is there other than beginner, intermediate, and expert? I can’t think of anything. And you can get it all for 75% off, which means the cost is about $150 USD.
And what do you call it there? That’s for life. Yeah, you don’t have to resubscribe to any of the stuff you get in this bundle. It is everything for all eternity.
So that’s nice for you. As far as upcoming events go, where I will be live and in person, shaking you upside down by your ankles, emptying out your pockets like a schoolyard bully, I will be at Pass Data Summit, November 4th and 5th, co-hosting two days of performance tuning grandiosity with Kendra Little.
If there’s an event near you that could use a pre-con speaker, let me know because I got pre-cons and I will travel. So with that out of the way, let us engage in festivities.
Let us have fun with our SQL Server selves. For some reason, the click wouldn’t work, so that all went black. That was fun.
So let’s make sure we have no indexes. And the first thing I want to show you is that, and this is actually kind of like probably a little bit more than you may have bargained for when you started watching this.
But there’s something really interesting that can happen to parallel nested loops queries or parallel nested loop query plans when you are guaranteed to have one single row on the outer part of the nested loops join.
And this is not something that happens when you don’t. If you have more than one row, I mean, if you have zero rows, I guess you don’t have to worry too much about it.
But if you have more than one row, things can get a little weird. But first, let’s take a look at this query plan. So this finishes relatively quickly. And the reason why this finishes relatively quickly is because there’s exactly one row in the user’s table with an ID of 22656.
Now, granted, user ID 22656 is Mr. John Skeet. You have a million plus reputation points on Stack Overflow. Even in the 2013 copy, he has that many.
So I think, anyway, it’s a big number. But if we look at the query plan, something really cool happens. At least I think it’s cool.
You might not think it’s cool. But if we look through this, we have sort of a typical guaranteed one row nested loops, parallel nested loops plan, where we get our one row from the user’s table.
And then we distribute that row out to our streams. And this is, of course, broadcast partitioning, which means that the one value from that row ends up on multiple threads, well, I mean, on dot threads, really.
And what happens on the inner side of the parallel nested loops join, and the reason why this is unique is because our row is unique. And when we look at the number of, the way that the rows get distributed on parallel threads, they do not, normally you would see 17142169 on every thread, because that’s what the inner side of a parallel nested loops join does.
It runs dot copies of whatever you do on the inner side of the nested loops join, which can be awful sometimes. Other times, when you’re guaranteed one row from the outer portion, you can end up with a much more, usually a much more efficient query plan, where those rows do get spread out.
So if you added up all those numbers that I just stuck my hand into, like a weirdo, you would get 17124169. So that’s cool.
What happens when you don’t? When you don’t guarantee one row? So the first thing I want to show you is that if we look at all the users in the users table, whose IDs are between 22656 and 22666, there’s only one other of them, right?
There’s only one other ID in there. But if we look for this, where ID is between 22656 and 22657, SQL Server is still going to expect two rows to come out of there.
Because of that, our query plan is going to change drastically and dramatically. It’s going to be awful.
And I don’t know, something about night recording is making the shadows over here a little weird. I don’t know how to get that to be any less weird right now. I can just close my arms so you can’t really see behind me. And then I can just do weird little robot T-Rex type arm things.
And I don’t know, that won’t look awkward at all, will it? No, not one bit. But if we look at the query plan, it changed quite a bit.
It took quite a bit longer. So this took about 22 and a half seconds. Yeesh. Yeesh. That didn’t do good. And just about all of that time is spent in between these two things right here. Scanning the clustered index to get new rows out and spooling those rows into a lazy table spool.
I’ve got lots of videos about how lazy table spools work. The short of it is that SQL Server takes the values that come out of here and it sends them over here. And then the first time this executes, SQL Server runs the clustered index scan over there.
Let me zoom in so my pointing is a little bit more effective. SQL Server runs the clustered index scan and gets whatever rows it needs for the predicate that gets passed over here. When it’s done with that, it truncates the spool and the next row that comes in, it repopulates the spool by scanning the table or usually scanning.
Generally, if you have a seek over there, you won’t see a table. Sometimes you will, but in general. Hitting this, let’s just say, hitting this object to get the next set of rows out to populate the table spool with is what happens.
This just takes a very long time. And part of why it takes a long time is because we end up with very, very lopsided parallelism. If you look at this, all of the rows end up on a single thread.
This is quite abnormal a lot of the time, but it can be very normal when you have these sorts of plan shapes. The reason why this happens here is because we’re looking for a single thing at a time. And we just get some really unfortunate data distribution stuff happening in the post table.
So we end up just putting all of our work on one thread for the two iterations of the nested loops joint. Because even, well, really the one iteration, because only one row comes out, even though we expect two rows. But SQL Server sort of like getting ready to defend itself against two rows means that we lose that guaranteed one row on the outer side of the nested loops thing.
And that sort of messes us up a bit. You’ll see that the partitioning type in here changes from broadcast to round robin. Meaning that this thing just, you know, puts things on threads as they come out.
And that’s, you know, kind of not good for this situation. Of course, having an index on the post table for last activity date is pretty helpful in both scenarios. One thing that is kind of a downer, though, is that with that good index in place, the original query slows down a bit.
This goes from finishing just about instantly with a parallel plan to taking a bit longer with a serial plan. Now, if you remember, the parallel plan spread out, you know, those dot threads pretty nicely. And we did all this work just about instantly.
Now we lost a little bit of efficiency here, right? This is about 1.7 seconds now, which isn’t great. But I think, you know, generally the efficiency that you gain with this query, where this thing no longer takes like 22 seconds to run, this thing takes another 1.7 seconds to run.
It makes it worthwhile. What you have to watch when you are writing queries like this that do not have a direct equality predicate, right? The only thing that our only predicate in the join clause is where the last activity date on the post table is between the creation date and the last access date on the post table.
You have to be really careful that your join keys are well indexed for this sort of thing. And, of course, the, you know, bigger and, you know, more involved your queries are, the harder that gets to, you know, get the harder that gets to really index for. So let’s get rid of these indexes.
And let’s talk about a slightly different kind of range query. Now, I can’t actually run this one to completion. This thing basically never finishes.
I’ve never, never spent too, too long trying to get this to run. I think the longest I let it run for was about 20 minutes. And it really just, you know, really just made the room hot. The laptop was sizzling.
I could have used it like a griddle. But this will basically never finish. It’s a real unfortunate sort of situation. And it’s made even more unfortunate by the fact that, you know, SQL Server asks for some kind of silly indexes.
So if we ran this multiple times, SQL Server would eventually suggest an index on display name. And I’m going to, the original index it wanted was on post type ID, comma, last enter display name. I’m just going to put this in the where clause here to, you know, shortcut having to do all that stuff.
Because it effectively gives you the same thing. But what’s really rough is that unless we write our query with an equality predicate like this. Right?
Like let’s say that we, let’s actually give you a slightly better example. Let’s do this first. And let’s look at what SQL Server comes up with for a query plan. Seeks into the post table.
And then does all of this sort of weird work. And then nested loops join to hit the users table. This will never finish either.
If we change, if we stick a force order hint on here, and we look at what happens, we’re going to see a query plan that looks a lot like the one that we saw up above before we had an index. The problem is this one basically never finishes either. This one, you know, just does really poorly.
And yeah, it basically just never finishes. A lot of the problem is that you have, you know, two point, like I said early on in the video, when you have a join without an equality predicate, you are, you are basically at the mercy of nested loops. So a parallel nested loops here where you are not guaranteed one row on the outside means that you’re going to spool a whole lot of rows in here.
Right. And that’s, that’s pretty painful. So you have 2.4 million something rows here.
That means 2.4 million rows are going to end up on each thread. And if you look at this number, you can see why this is going to take a very long time. Right.
SQL Server, like the estimate here is actually kind of close to reality because it’s that 2.4 something million number times eight. Right. So this actually is how many rows end up on each thread every time you go into this side of the join. And that table spool just does not buy you anything there.
The only thing that I’ve ever found that helps at all with this is to give SQL Server some kind of a quality predicate. Now, if I were doing this, like with a client to really help them get a query running faster, I would probably make computed columns that do something like this so that I can have a sargable equality predicate for this join.
But in this case, this actually gets us good enough performance. The problem is that if we don’t add that force order hint in, if we just do this, we end up with this awful execution plan again. With the force order hint on there, we end up with a much more favorable execution plan.
Now, SQL Server, because we have that equality predicate, we can at least get a hash. Let’s join here. Now, the logic of this may not be 100% correct because I’m saying where the display name equals a display name and the display names like this.
There are all sorts of ways that you might need to logically look at this to figure out if what we’re doing here is exactly correct. One other thing that you could do is find like the min length of display names in the users table and just match on the minimum length of one being equal to the minimum length of another, which is like, I think it’s like three characters over in the users table.
There are all sorts of ways that you can do this or look at this or approach this. You might even try hashing some of it or you might even try hashing the display names or something like that. But the goal is to just get something that’s an equality predicate into the join clause so that when you run this, SQL Server has other options for join types.
And when you run this, it actually finishes pretty quickly with those indexes in place. Is this result correct? I don’t know.
You know why I don’t know? Because none of the other queries finish. So that’s fun there. But it was my attempt to kind of give you some advice on how to start tuning these queries and how to start getting at least some semblance of a reasonable query plan back. Like I said at the beginning of the video, or I don’t actually forget when I said it, joins without equality predicates have really caused a lot of performance problems that I’ve seen over the years, especially when there are really large tables.
And almost without fail, there’s no good indexes to support what you’re actually doing. And almost without fail, it takes a lot of effort to get these queries to do anything reasonably fast. So I’m going to wrap this one up because I’ve got a few other videos that I need to record this evening.
So I’m going to get this one on the books. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something.
I realize that the outcome of this video is a little disappointing because there really is a lot of thought that has to go into what makes sense for any sort of a quality predicate in here. And that can take a lot of tinkering and tweaking. And that’s just a lot of time and effort that you’re going to have to spend, you know, reasoning with the data that you have in your tables.
For me, this is probably a close enough approximation that actually gets some results back and gets the query to finish. But, you know, you might have a completely different set of requirements that make this absolutely useful. So, from me and Bats Maru, my only friend in the world, thank you for watching and I will see you in the next video.
Have a good night.
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.