Logical Errors And Query Performance

Is that a nice way of saying typo?

People will often tell you to clearly alias your tables, and they’re right. It will make them more readable and understandable to whomever has to read your code next, puzzling over the 52 self joins and WHERE clause that starts off with 1 = 2. It can also help solve odd performance problems.

Take this query, for instance.

We have two references to the Posts table, as p and p2. Correctly aliased and referenced, the query finishes instantly with a pretty tidy execution plan and a total cost of 0.0166169.

Golden Earring

But what if we mess up? What if we write our query like this, and reference the outer query in the subquery? Our off-kilter where clause wreaks plan havoc.

Butt

That whole thing goes wonky, with a total cost of 161094000000. This is not helpful.

We get a similarly painful plan if we mess up the ORDER BY, referencing the outer query.

This plan is a bit less yucky, and costs a bit less, but is still all wrong. It ‘only’ costs 10705.1 query bucks.

Filth

Curiously!

If we don’t alias anything at all, the optimizer gets it right. I’m not trying to give you an excuse to not alias your tables, here. Just that the optimizer is sometimes smart enough to take the easy way out, and we’re back to our original plan and cost.

Now, I know this is a silly query, but that’s kind of the whole point. It doesn’t take much to throw performance off a whole lot. Whether it’s a typo or a logical error when writing the query, sometimes you can find easy performance wins just by double checking your work.

Thanks for reading!