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.

Popular posts from this blog

Service Broker sys.transmission_queue clean up

AWS DynamoDB vs Azure CosmosDB vs Azure Table Storage pricing comparison

Execution of user code in the .NET Framework is disabled