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.
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 🙂
Yeah, I’ve heard that for years and years. I think it’s just that people are in the habit of putting the column on the left and variables, parameters, what have you, on the right.
[…] Left or Right? - Grant Fritchey (Blog|Twitter) […]
Oh, it was true in SQL 2000!!!
Right join was less effective than left.
You sure about that one? I don’t have a 2000 machine to test it out on (and no, I’m not going to track one down).
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.
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!
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.
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.
As a native Hebrew speaker I just wanted to comment that math and programming and graphs we write and draw as everybody else from left to right..