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.

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