Surprising SQL speed increase

Question

I’ve just found out that the execution plan performance between the following two select statements are massively different:

select * from your_large_table
where LEFT(some_string_field, 4) = '2505'

select * from your_large_table
where some_string_field like '2505%'

The execution plans are 98% and 2% respectively. Bit of a difference in speed then. I was actually shocked when I saw it.

I've always done LEFT(xxx) = 'yyy' as it reads well. I actually found this out by checking the LINQ generated SQL against my hand crafted SQL. I assumed the LIKE command would be slower, but is in fact much much faster.

My question is why is the LEFT() slower than the LIKE '%..'. They are afterall identical?

Also, is there a CPU hit by using LEFT()?

Answer 1

More generally speaking, you should never use a function on the LEFT side of a WHERE clause in a query. If you do, SQL won't use an index--it has to evaluate the function for every row of the table. The goal is to make sure that your where clause is "Sargable"

Some other examples:

Bad: Select ... WHERE isNull(FullName,'') = 'Ed Jones'
Fixed: Select ... WHERE ((FullName = 'Ed Jones') OR (FullName IS NULL))

Bad: Select ... WHERE SUBSTRING(DealerName,4) = 'Ford'
Fixed: Select ... WHERE DealerName Like 'Ford%'

Bad: Select ... WHERE DateDiff(mm,OrderDate,GetDate()) >= 30
Fixed: Select ... WHERE OrderDate < DateAdd(mm,-30,GetDate()) 

Bad: Select ... WHERE Year(OrderDate) = 2003
Fixed: Select ... WHERE OrderDate >= '2003-1-1' AND OrderDate < '2004-1-1'

Answer 2

It looks like the expression LEFT(some_string_field, 4) is evaluated for every row of a full table scan, while the "like" expression will use the index.

Optimizing "like" to use an index if it is a front-anchored pattern is a much easier optimization than analyzing arbitrary expressions involving string functions.