Monday, June 05, 2006

Interesting Performance Problem:
I recently had an interesting problem that stemed from an implicit conversion doing something really stupid.

Here is a reproduction of the scenerio.

I have a table Users with the following columns
ID int identity
FirstName varchar(50)
MiddleName varchar(50)
LastName varchar(50)
IsValid bit

The table has around 2,000,000 records in it, three of which have the IsValid set to true. Each column has an index on it.

When I do the following query performance is poor

SELECT * from Users WHERE IsValid = 1

It takes about 200ms to execute. This is way too slow.

I will discuss why and how to fix in my next post (which I promise will be much sooner.)

Note: Tests were run using SQL Server 2000, SQL Server 2005 does much better.


Post a Comment

<< Home