Performance Tuning GREATEST And LEAST Functions In SQL Server

Performance Tuning GREATEST And LEAST Functionalist In SQL Server


YouTube player

Thanks for watching!

Video Summary

GREATEST and LEAST arrived in SQL Server 2022, and on a join between two tables they perform poorly unless you aggregate early. Before 2022 you can emulate them with CROSS APPLY, supplying the columns as VALUES and taking the MAX, but GREATEST was only about 500 milliseconds faster than that, and batch mode did not help enough. The optimizer scanned both indexes, 17 million and almost 25 million rows, joined them, and aggregated only at the end. When I aggregated the max score from posts and from comments first, then used CROSS APPLY, it ran in 1.059 seconds, though the Gather Streams operator showed two.

Full transcript

Guess who? Erik Darling, Darling Data, recording a video about something in SQL Server. Shocking, I know. Shocked me every day that there’s stuff to talk about still. Uh, you know. Maybe someday there’ll be something new, or maybe someday there will be nothing left to say, but until then… Gotta keep going, I guess. Until I rub the eyebrows off my face. Uh, alright. So, uh, in this monster of a video, this absolute savage unit of a video, we’re gonna talk about, uh, well…

For the… for the sake of demo length, we’re gonna just talk about the greatest function, which is new to SQL Server 2022. But the greatest function is… was accompanied in… in release by the least function. So you can do greatest and least, uh, right? So, uh, every other database platform for, like, 20 years has had the greatest and least functions. And in… sometime in 2020, 2021, uh, Microsoft said, Oh, yeah, we better play catch up on this… this basic database functionality stuff that, uh, other databases are wiping the floor with… floor with us with.

Um… Yeah. So, great. Um… SQL Server 2022 has the greatest and least functions. If I had a kazoo, or a party… one of those party things, I would… I would… I would do that right now. Uh, but I don’t have either one, so just use your imagination. Pretend I am, uh, interacting with, uh, with party noise-making, uh, devices. Assuming that you’ve been to a party and you know what I’m talking about, um…

Maybe, maybe, maybe, maybe, maybe one time you saw a presentation about parties and, uh, noise-makers were part of… part of the presentation. Uh, you could… you could fill in that blank if you… are… are… are not a… hyper-social being. Who works with databases would be shocking, wouldn’t it?

Uh, it’s like… it’s like that joke about, uh… Well, uh, I don’t know. I guess IT people in general, it’s, uh… Uh, how do you tell the difference between an introverted IT person and an extroverted IT person?

And it’s that, uh, an introverted person stares at their shoes while they talk to you, and an extroverted IT person stares at your shoes while they talk to you. So…

Uh, yeah. There’s that. So, uh, we have the greatest and least functions. And I’m gonna show you two examples of them. Uh, one is just across two columns in one table. And the other is across two columns, uh, in join tables.

And, uh, honestly, for the single table thing, it turns out fine. Uh, but for the double… the join table thing, uh, I am a little bit less in love with the query plan. Um, it’s… it’s a bit slow for me.

Right? I don’t… don’t enjoy slow queries. Uh, and this is… this is gonna be true of… Uh, well, other examples. Well, I’m gonna show you. Well, I’m gonna show you.

So, if you’re not on SQL Server 2022, right? And you’re… or whatever other problems you have in your life, uh, there is a way to emulate the greatest and least function… greatest and least functionality.

Whew! Um, it’s… it’s too early for me to be drunk, so don’t… that was just a mouth malfunction. A mouth function. Uh, you can emulate the greatest and least functionality, uh, by doing the old cross-apply trick.

Um, supplying the… the columns that you need to greatest and least as values, right? And then aliasing, uh, that as a single column to reference. And then, um, from there, uh, finding the max of whatever is in that aliased column.

So, whatever comes out of combined date. You can’t emulate greatest and least functionality doing that. Uh, the problem is that, uh, you know, performance for this sort of thing is not all that hot.

Um, you know, again, for the single table query, it’s not that big a deal. Uh, but for the multi… for the join and if you’re, you know, doing other stuff, that’s a three and a half seconds. The greatest version of this was, like, was only about 500 or so milliseconds faster.

So, performance isn’t necessarily great for that. Uh, and, uh, batch mode does change the picture a little bit, but not enough to, um, not enough to make me happy. So, if you do need to emulate the greatest and least functionality in SQL Server, you may want to write in, you know, you’re unhappy with the speed of the query.

If you write it the norm… the way that I just showed you and speed is fine, like, you’re not worried, like, like, performance is good enough, don’t worry about this. But if you’re unhappy with performance, uh, you can always rewrite the query a little bit to do some, uh, earlier aggregations. Um, I have another video, uh, about SQL Server performance where, uh, it just… the optimizer just doesn’t spot an opportunity for, uh, earlier aggregation and, um, performance sucks.

And we have to kind of tell SQL Server what to do. Uh, so we have… we’re gonna… what we’re gonna do is we’re gonna do… we’re gonna aggregate early on our own the max score from posts and the max score from comments. And then we’re gonna use, uh, the… the cross-apply just to continue with the emulation of the… the greatest and least functionality.

We’re gonna… just wanna do this. And, uh, this will be a lot faster. Right, the early aggregation allows us to kinda hit these two tables, uh, bring the rows that we need to figure out the… the max of, uh, a lot faster, right?

So we… oh, geez louise! Zoom it! I hit control! Why are you messing with me? Why? Uh, we do this. We do the early aggregation here. Uh, and then we do a secondary aggregation to bring things, uh, down to one row even further here. And, uh, this all takes about… well…

It’s another real curious case of… uh, uh, operator times and execution plans. Looking real stupid. Real bad. Uh, kind of embarrassing, actually. Uh, this… this query doesn’t take two seconds, as the gather streams operator might have you believe.

It really does take 1.059 seconds. Uh… Yeah. No. Summer interns. Uh…

Well, yeah, that’s about that. Alright, well, now I’m just depressed. Um, thanks. Thanks, SQL Server, for that. Uh, let’s just wrap this up with the demo to show you…

Uh, that that can also work well for a single table query. Uh, not that this was slow before, but, uh, you can… you can express, uh, the… logic in many different ways that can… they can help with performance in many different situations.

Uh, usually, I find, when I’m query tuning, that, uh, I… I need to guide SQL Server towards doing aggregations a lot earlier, uh, than it sometimes does. And, you know, usually, uh, that… those… those, uh, those… as long as there is a meaningful reduction in the number of rows via those aggregations, uh, you should see pretty decent performance improvements. You know, like, comparing, um, this plan up here…

with the greatest and least. Alright, and… Run this again. Uh, this is real goofy, because SQL Server scans both indexes. Uh, there is no early aggregation before the join.

And the aggregation only occurs at the very end. Uh, for some reason, SQL Server doesn’t think to do anything between… getting…

17 million rows and 20… almost 25 million rows, and… joining those fully together and then aggregating stuff. The optimizer’s like, no, this will be fine! Alright, so we have our initial aggregation here, and then our final aggregation here.

Uh, but, um… Like I said, my general experience is that it is a lot better and faster if you guide SQL Server towards the correct… uh, the correct early aggregation strategy like this.

Um, even though this filthy idiot liar of a gather streams operator is still trying to convince us that our query… took two seconds to finish. It really took one second.

So… Alright, well, I wasn’t drunk before, but I think I’m gonna go start on that now, cause… I’m gonna go start on that now, but…

I’m gonna go start on that now, and I’m gonna go start on that now, and I’m gonna go start on that now. Well… I mean, look at that. Look at that thing in front of you. I don’t know…

The thing… The thing is real ugly. Uh, we’re gonna… We’re gonna… We’re gonna have to… Have to drink to cope with that. Sorry to say. There’s no other alternative. Uh, I’ve…

I’ve jumped out of every single window that I have available. The glass is shattered. Uh, it is no longer as dramatic and exciting… An exit, as it was when the glass was there. Uh, so now…

Now we’re just down to drinking. To deal with… These inconsistencies. So… I’m being dramatic. Calm down. Uh, anyway. Thank you for watching.

I hope you enjoyed yourselves. Hope you learned something. I hope that… Uh… You will not… Be as offended as I am at these operator times and query plans.

They are… Truly… Truly awful things… Sometimes. And, um… If you like this video…

Thumbs ups… Are good. Um… Inspirational comments. Live, laugh, love. Um…

Add years to your life and life to your years. Peanut butter and onion. Uh… All nice things to hear about. So… Um… If you like SQL Server content… Uh…

Despite… This monstrosity that you see before you… Uh… You can subscribe to my channel. And we can at least laugh at this stuff together. Uh…

I probably won’t be drinking on camera. Cause that would be very unprofessional. But… Um… What… What happens below this line… Stays below this line. Lucky for you.

So… Um… Yeah. Anyway… Uh… Subscribe. Like. Comment. What else… What else can you do on YouTube? I don’t know. Anyway… Uh…

Thank you for watching.

Going Further

Someone still has to know enough SQL Server to keep the robots in check, and that’s what my training is for.