Friday, 3 October 2008

Surprising SQL speed increase

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.

There are lots of places in our SQL where we use LEFT(something, x) = 'string'
This should be replaced by the LIKE command for greater speed.

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.