Mind Your Darn Aliases

YouTube player

Chapters

Full transcript

Erik Darling here with Darling Data, and today’s video is a bit of a public service announcement. Mind your darn aliases. The reason we’re doing this is because I got an email the other day. I know, weird, someone would want to talk to me, and they said, we took your advice. We replaced a left join with not exists, and the query just ran forever. We don’t know why. And I was like, oh, well, that’s not good, because there’s nothing more expensive than free advice like that, right? Well, you know, I want to make sure that everyone is equally satisfied, and I don’t have to start handing out refunds. So, we looked at it, and there was something silly about it, and that’s what we’re going to look at in today’s video. Down in the video description, way down below, if you scroll past my nether regions, I don’t think you can see those, but who knows? YouTube’s weird. Maybe there’s some, like, thing they do. They scroll well past where the camera can see and just AI generate what they think, what they presume it would look like. Anyway, maybe that’s the next thing I’ll make. There we go. It’s a winner. Down in the video description, there’s all sorts of helpful links where you can hire me for consulting, buy my training. There’s new stuff, which we’ll talk about in a second. You can become a supporting member of the channel.

If you think that the value you get from this channel is greater than or equal to, like, $4 a month, you can hit a subscribe button, or you can hit a member button. Give me money. You can ask office hours questions. That is free, at least for now, anyway. And, as always, please do like, subscribe, and tell a friend. So, there’s new stuff that we have going on here at Darling Data. I’m going to move my big head out of the way. Where people seem to need help with AI and doing things and not dumb way with them. And so, that’s what I want to help you with. I’ve got a course that I’m recording and working on some of the material for. And that’s for individuals, mostly, I would assume. It’s on pre-sale now for $250. There are links down in the video description for this as well.

But if you work at a company, and you want me to come help you in your environment, get things up and running in a way where you can have DBAs and developers having the robots interface with SQL Server in ways where nothing will explode, break, go horribly awry. That’s what this is for. It’s a one-day class. You bring as many people as you want, and I teach you how to use your stuff better. It’s a good idea, because more and more people are doing this, and need help with this. All right. If you need free SQL Server monitoring and Postgres monitoring, that’s up there, too. It’s on the slide, so I have to say it. I’ve got my free open-source tool at code.erikdarling.com.

There’s no email sign-up required, any of that stuff. You just go, you download it, you do a couple buttons, and then it’s looking at your SQL Servers, right? It’s very easy, very quick, very painless, and completely free. There’s nothing that you have to pay for, I guess, aside from maybe like a VM or something. But everything else, you know, VMs are pretty cheap.

I will be in just a couple short weeks at Pass Summit West in Seattle, Washington from November 9th to 11th. I have a pre-con there doing advanced T-SQL stuff, and I’ve got a regular session on isolation levels where I will once again shove read committed snapshot isolation into your brain. But now let’s go over to SQL Server Management Studio. Let’s look at this thing, I guess, a little bit.

So let me make sure I have this thing. You know what? Let’s see if I have to drop it. Hopefully I don’t. Oh, man, it’s going to make me connect. That’s not a good time. All right. So good. It’s already there. Anyway, this is what happened. All right. So I’m using I have to rewrite it to the Stack Overflow schema because I don’t have whatever schema that was.

I don’t know. It was still DBO. It’s different tables, different data, not Stack Overflow. And so the people sent me this query and it looked a bit like this. And if you’ll permit me some zoom in time here, I’m just doing a select count, but you can get the gist.

From one table, aliased, where it not exists, select some stuff from another table where p.owner user ID equals ID. And you would maybe think that SQL Server would say, well, clearly, they care about where not exists the user or posts from the user’s table. But that’s not quite the way it works. SQL Server has other things in mind.

SQL Server is like, it kind of finds the closest column and binds to that. Right. So if we if we look at the query plan for this, it looks like this. I can’t run the actual plan because just like theirs, it would take forever.

And who wants to sit here through forever? I don’t think I have enough things to say that would make that make sense. But we had we had this top above this scan. And that’s that’s that’s that’s that’s weird.

Because if I move my head out of the way a little bit, we have we have an index on owner user ID. That should have been nice and seeky, but that didn’t work out. And then we’ve got this row count spool.

And then it’s just like, well, why why on earth would you be counting roll rows? That doesn’t make sense either. SQL Server. And I think you have gone daffy. One of my least favorite plan shapes in the entire world is is a top above a scan. Right. Because that that top is going to scan that table every single time.

And and the post table being 11 million rows or so, it’s it’s trying to find matches between owner user ID and the ID column in the user’s table over and over again. Right. Let’s move this over a little bit. So my my giant head doesn’t interfere.

But this is this is what the predicate ends up being. Right. Where owner user ID equals post that ID. Ah. Oh, man.

That one stings. Right. Of course, the query plan that we would want to see is what would what we would see if we properly aliased our ID column over here. Right. If we get the estimated plan for this one, we can get the actually I don’t have them turned on.

What an idiot I am. I’m a buffoon and a half. Right. We run this. Right. What do we have?

A much a much different looking execution plan. Right. But there is there is no row count spool. I mean, there’s still a scan over here, but there’s not a top over it. I would consider this plan completely adequate for for what we’re doing here.

You know, you could maybe mess around with like a FORCESEEK hint or a loop join hint if you felt like it. But, you know, I don’t know if I want to do 2.5 ish million nested loops joins. Maybe maybe maybe in some cases, maybe if like there were a where clause on the user’s table.

I wouldn’t that would that would be sensible there. But for this, I consider this to be an adequate enough plan for what we’re asking this thing to do. And so and so but we got this because we alias things correctly.

Right. And our and our hash join now correctly identifies that we need to match the owner user ID column in the post table to the ID column in the user’s table and not the one in the post table. Right.

So remember, binding, it betrayed us here because we didn’t alias things correctly. So mind your darn aliases, my friends, mind your darn aliases. All right.

Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I’ll see you in tomorrow’s video where I’m going to demoify an answer that I gave in an Office Hours episode that I can’t remember if it was this week’s or last week’s. But it stuck in my head and I and I and I wanted to write a demo for it.

So I did. Anyway, there we go. All right. I’ll see you tomorrow. Goodbye.

Going Further

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



Leave a Reply

Your email address will not be published. Required fields are marked *