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.

Impact of sp_ prefix on stored procedure performance


Naming conventions are integral part of a quality code but sometimes we need to pay additional care while deciding on the naming patterns. There are two type of programmers, one who prefix everything and the other who don’t. In world of SQL server, the former group is further divided in two parts, one who use sp_ prefix for naming their stored procedures and others who use something else (usp_ or short app name). The recommendation has been to avoid sp_. In this article I will explain the reasons for this recommendation with supported data from my experiments.

sp_ does not stand for Store Procedure

The myth is sp_ stands for stored procedure, infact it means System Stored Procedure. SQL server has some system stored procedures defined in master database. These procedures starts with sp_ prefix and are accessible from any other database. One such example is sp_helptext. If you want to have the text of a stored procedure execute sp_helptext‘<SP Name>’.

Performance impact

To understand the impact on performance lets create a database say DummyDb. Create a table DummyTable and insert 100 rows

–Create database

CREATE DATABASE [DummyDb]

 

–Create Table

CREATE TABLE [dbo].[DummyTable](

       [Id] [intNULL,

       [Name]

[varchar](100) NULL

) ON [PRIMARY]

 

–Insert 100 rows

DECLARE @Count int;

SET @Count = 100;

WHILE(@Count>0)

BEGIN

       INSERT INTO DummyTable VALUES (@Count, ‘Name_’+ CONVERT(varchar(100),@Count));

       SET @Count = @Count1;

END

 

Now lets create two stored procedures GetEmployee
and sp_GetEmployee

–Create GetEmployee

CREATE PROCEDURE GetEmployee

AS

BEGIN

       SELECT ID, Name FROM DummyTable

END

 

–Create sp_GetEmployee

CREATE PROCEDURE sp_GetEmployee

AS

BEGIN

       SELECT ID, Name FROM DummyTable

END

GO

 

Lets execute both the stored procedures

1. Execute GetEmployee

DECLARE @Count int;

SET @Count = 500;

WHILE(@Count>0)

BEGIN

       exec GetEmployee

       SET @Count =

@Count1;

END

 I executed the above statement three times and it took 38, 41 and 38 seconds which means it comes to be 117/1500 = 0.078 seconds per execution (Although it will not be same every time, but 1500 is a good sample for comparison).

2.Execute
sp_GetEmployee

DECLARE @Count int;

SET @Count = 500;

WHILE(@Count>0)

BEGIN

       exec sp_GetEmployee

       SET @Count =

@Count1;

END

 I executed the above statement three times and it took 40, 41 and 41 seconds which means it comes to be 122/1500  = 0.081 seconds per execution (Although it will not be same every time, but 1500 is a good sample for comparison).

The above data clearly prooves the sp_ prefix degrades the performance of stored procedure.

Why is it slow?

Whenever SQL server gets an command to execute a stored procedure with sp_ as a prefix, it first scans all the system stored procedures. If it doesn’t find a matching procedure there, then it scans the user defined stored procedures. This additional time causes the performance hit.