Performance

Or ‘How to slow down a query without apparent reason’

I’ve discussed functions in where clauses before and shown how they can prevent index usage and slow down queries. I’ve also discussed data type conversions and shown that SQL Server sometimes does the conversions in places not expected. There’s a nice little gotcha that results from the combination of these two. Let’s take a look at a simple example.

Which of the following queries will run slower? (sample code at the end)

Select ID from TestingConversion where Code = 'AAA'
Select ID from TestingConversion where Code = N'AAA'

(more…)

Or ‘How to really slow a query down without trying’

Here’s a query that looks innocent enough. Table structure and sample data are at the end of the post.

-- returns 5 out of 5000 rows
SELECT InsertDate FROM TestingFunction WHERE LEFT(Code,1)='AA'

Knowing that there’s an index on the column code, the optimiser might be expected to use an index seek to satisfy the query. However, the execution plan shows an index scan. Why?

(more…)