Left or Right?

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:

ExecSimple

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:

ExecScan

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:

ExecScan

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.

12 thoughts on “Left or Right?

  • Kev Riley

    I often wondered why people kept banging on about the ‘left’ side of a WHERE clause. Glad it’s not just me who thought this 🙂

  • Steven Howes

    I think you’re right grant. At least it makes sense to me. When I’m talking out the code to myself I always think “if column X equals Variable y…”. to go one step further, I’ve found if I do do it the otherway around I find it hard to understand… Equating a variable to a column value seems backwards.

  • Joe Celko

    This is language dependent. We read left-to-right, and have subject-verb-object grammars in most European languages. If you ever had Arabic, Chinese, or students in a class, you would see some differences.

    • Good point. That explains why people lay out the stuff the way they do. And, if the issue is just that people thought/think/believe/speculate, that the column has to be on the left side of the comparison, that could be where the thought that the placement of the function was the problem, not the function itself.

      Oh, and thanks for posting a comment Joe.

      SQUEEEE! Joe Celko read my blog!

  • Rob

    It’s not about the function – it’s about the determinacy of the data. Look for ‘Left(…,4) = ‘Down’ and SQL will be able to use the index [starting with] it may still be a scan – but it’s only over a range of values not the entire table. In this case it’s the indeterminate nature of the RIGHT function – you need to read the entire address line to find and locate the last 3 characters before you can compare them.

    Placing your constant LEFT or RIGHT of your operation sign makes little difference (when it’s an equals)

    • I didn’t want to go too far into separating the sargeable from the non-sargeable functions in this simple post, but yes, that does matter too. Same with LIKE (‘%somevalue’) vs. LIKE (‘SomeValue%’) or LIKE (‘%SomeValue%’). If the function allows for the use of an index it’s part of the fact that it would be deterministic, seek generating, as opposed to non-deterministic, or scanning.

  • Ville-Pekka Vahteala

    I tested these on our SQL SERVER 2000 and Northwind datbase.

    SELECT a.CompanyName
    FROM dbo.Customers AS a
    WHERE a.CompanyName = ‘Berglunds snabbköp’;

    SELECT a.CompanyName
    FROM dbo.Customers AS a
    WHERE ‘Berglunds snabbköp’ = a.CompanyName;

    SELECT a.CompanyName
    FROM dbo.Customers AS a
    WHERE ‘snabbköp’ = RIGHT(a.CompanyName, 8);

    SELECT a.CompanyName
    FROM dbo.Customers AS a
    WHERE RIGHT(a.CompanyName, 8) = ‘snabbköp’;

    SELECT o.*, c.*
    FROM Orders o
    LEFT JOIN Customers c
    ON o.customerId = c.customerId
    WHERE c.CompanyName = ‘Berglunds snabbköp’;

    SELECT o.*, c.*
    FROM Customers c
    RIGHT JOIN Orders o
    ON o.customerId = c.customerId
    WHERE c.CompanyName = ‘Berglunds snabbköp’;

    All the pairs have equal execution plans either function or constant being on left or right side.

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.