Monday, June 05, 2006

Interesting Performance Problem Part II

In my last post I discussed a problem with a slow running query. In this post I will explain why.

The answer to the problem can be seen if you look at the execution plan for the query. To do this from query analyzer choose the query --> Show Execution Plan from the top menu. Alternatively execute 'set showplan_Text ON'. This will cause the output to display the execution plan not the results.

Once this is done you will see the following:
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Spitfire].[dbo].[Users]))
|--Index Scan(OBJECT:([Spitfire].[dbo].[Users].[IX_Users_2]), WHERE:(Convert([Users].[IsValid])=1))

See the problem. The compiler is casting the [Users].[IsValid] to an integer to perform the comparison to the value of 1. What this means is that, for each and every row in the table the value has to be read, converted an int and then evaluated against the integer 1. The sort of the index is useless and performance will be slow. A simple change to the query will resolve this.

SELECT * from Users WHERE IsValid = CONVERT(bit,1)

This will make the compiler convert the integer value to a bit first, and then compare it to the column in the table. This means that only one conversion is executed and the index may be used. The resulting execution plan is:
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Spitfire].[dbo].[Users]))
|--Index Seek(OBJECT:([Spitfire].[dbo].[Users].[IX_Users_2]), SEEK:([Users].[IsValid]=1) ORDERED FORWARD)

This will use an index seek, not an index scan. A much faster and much more efficient plan for our purposes. Now, the same query will be virtually instantaneous about 1ms. MUCH BETTER.

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.