Performance Tuning GREATEST And LEAST Functionalist In SQL Server
Thanks for watching!
Video Summary
In this video, I dive into the exciting world of SQL Server’s new greatest and least functions introduced in version 2022. Erik Darling from Darling Data shares his insights on how these functions can be used effectively, especially when working across two columns in a single table or joining tables. While demonstrating their usage, he also highlights some performance considerations and suggests ways to optimize queries for better speed, such as using early aggregations. The video is packed with practical examples that showcase the functionality of greatest and least, along with tips on how to emulate these functions if you’re not running SQL Server 2022 yet. With a touch of humor and self-deprecating remarks, Erik keeps things entertaining while delivering valuable information for database professionals looking to improve their query performance.
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
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.