Friday, 19 December 2008

Surprising SQL speed increase - Part 2

Part 1 is availble below

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'