No, this is not about politics. It’s about your WHERE clause… and your JOIN criteria… and your HAVING clause. It’s about a canard that still makes the rounds occasionally. Please, help me put this statement to sleep for once and all:

A function on the left side of the equals sign can lead to performance problems

Well, you know, it’s sort of true. But then, a function on the right side of the equals sign can also lead to performance problems. In short, it’s not the placement of the function that causes issues, it’s the function that causes issues. Let’s take a look at a really simple example:

SELECT  a.AddressID,
        a.AddressLine1,
        AddressLine2
FROM    Person.Address AS a
WHERE   a.AddressLine1 = 'Downshire Way';

This simple query results in an equally simple execution plan:

Now, if we decide that we want to do something like look for all results that have ‘Way’ in them. It’s a different result set, but our index could be used for the new result set. The query will get modified to this:

SELECT  a.AddressID,
        a.AddressLine1,
        AddressLine2
FROM    Person.Address AS a
WHERE   RIGHT(a.AddressLine1, 3) = 'Way';

That’s a function on the left side of the equals sign. OMG!!1! The execution plan isn’t as nice any more:

So, if we change the query to this:

SELECT  a.AddressID,
        a.AddressLine1,
        AddressLine2
FROM    Person.Address AS a
WHERE   'Way' = RIGHT(a.AddressLine1, 3);

Whew, dodged a bullet since we have the function on the right side of the equals sign. And so we get a better execution plan now:

Uhm, wait. That’s still a bad plan isn’t it? Why yes, yes it is. That’s because the problem isn’t which side of the equals sign we have a function, but the fact that we have a function on the column at all. There are a number of permutations we can get into around this. For example, what if, instead of putting the function on the column, we put it on the string, to only match to ‘Way’ instead of ‘Downshire Way.’ Well, that would fix the function issue, but then, we’d have to use a LIKE command and add a wild card to the beginning of the string, resulting in scans again. But the fundamental concern remains, we’re not talking about the left or right of the comparison operator, we’re talking about the existence of the function on the column.

Please, don’t repeat this one any more. OK? Thanks.

For lots more on query tuning, let’s get together and talk. I have an all day seminar in two weeks at Connections in Las Vegas. Please go here to sign up.

Or, I’ll be doing a full day pre-conference seminar at the PASS Summit this year in Seattle. Last time I gave a similar talk it sold out, so please, if you’re interested, sign up now.

The post Left or Right? appeared first on Home Of The Scary DBA.