Query Performance – Function doesn’t use index seek


Indexes play an important role in SQL query performance, but improper knowledge of how indexes work, can lead to degraded performance. In this article I will throw some light on one such practice. We all know indexes help in select statements and make other statements (insert, update, delete) slower. At the same time if we use a function on indexed column in where statement, it does not use the index and hence query performs as it does not have a index. Lets look into real time example

Step 1. Create a table Person :

CREATE TABLE [dbo].[Person](
[ID] [char](800) NULL,
[FirstName] [char](2000) NULL,
[LastName] [char](3000) NULL,
[City] [char](500) NULL
)

Step 2. Insert some random data :

INSERT INTO Person (ID,FirstName,LastName,City)
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
‘Vineet’,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN ‘Sanjay’
ELSE ‘Mahesh’ END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN ‘New Delhi’
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN ‘Chennai’
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN ‘Hyderabad’
ELSE ‘Bangalore’ END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO

Step 3.  Execute a select statement with where clause on City :

SELECT * FROM Person
WHERE  City = ‘Chennai’

Note : Till now we do not have any index on City column.

Following is the execution plan

Execution Plan Without Index

Look at the query execution plan, SQL Server did a table scan which is expected because there is no index on the City column.

Step 4. Create a non clustered index on City column :

CREATE NONCLUSTERED INDEX Index_Person_City
ON Person (City);

Step 5. Execute following select statement :

SELECT * FROM Person
WHERE  City = ‘Chennai’

Notice the execution plan (below) now, SQL Server is using index seek.

Execution Plan With Index

Step 6. Now lets execute a select query which uses function on column name in where clause :

SELECT * FROM Person
WHERE CONVERT(Varchar(500), City) = ‘CHENNAI’

Below is the Query Execution Plan. SQL Server is using Scan which contributes to 29% of the total cost of query whereas in step5, it used Seek which contributed to only 5% of the total cost.

Execution Plan With Index With Function

Conclusion :

So in summary , the above steps show that SQL server was able to perform a seek operation on City column but when a function is used on the column it was not able to perform seek and hence it performed scan operation.

Advertisements

What do you think about this article?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s