Impact of Sp_prefix on Stored Procedure Performance

Vineet Kumar
Mar 26, 2015
5.1k
0
2
Article

Naming conventions are an integral part of quality code but sometimes we need to pay additional care when deciding on the naming patterns. There are two types of programmers, those that prefix everything and those that don't. In the world of SQL Server, the former group is further divided in two parts, the ones that 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_. This article explains the reasons for this recommendation with supported data from my experiments.

sp_ does not stand for Stored Procedure

The myth is that sp_ stands for Stored Procedure. In fact it means System Stored Procedure. SQL Server has some system Stored Procedures defined in the master database. These procedures start with the sp_ prefix and are accessible from any other database. One such example is sp_helptext. If you want to get the text of a Stored Procedure then execute sp_helptext '<SP Name>'.

Performance impact

To understand the impact on performance, let's create a database, say DummyDb. Create a table DummyTable and insert 100 rows into it.

Create database

CREATE DATABASE [DummyDb]

Create Table

CREATE TABLE [dbo].[DummyTable](
[Id] [int] NULL,
[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 = @Count-1;
END

Now let's 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

Let's execute both of the Stored Procedures.

Execute GetEmployee

DECLARE @Count int;
SET @Count = 500;
WHILE(@Count>0)
BEGIN
exec GetEmployee
SET @Count =
@Count-1;
END

I executed the preceding statement three times and it took 38, 41 and 38 seconds. That results in 117/1500 = 0.078 seconds per execution (although it will not be the same every time, but 1500 is a good sample for comparison).

Execute sp_GetEmployee

DECLARE @Count int;
SET @Count = 500;
WHILE(@Count>0)
BEGIN
exec sp_GetEmployee
SET @Count =
@Count-1;
END

I executed the preceding statement three times and it took 40, 41 and 41 seconds. That results in 122/1500 = 0.081 seconds per execution (although it will not be same every time, but 1500 is a good sample for comparison).

The preceding data clearly prooves that the sp_ prefix degrades the performance of Stored Procedures.

Why it is slow

Whenever SQL Server gets a command to execute a Stored Procedure with sp_ as the prefix, it first scans all of 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 degradation.