Post
Ask Question

Impact of Sp_prefix on Stored Procedure Performance

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

  1. CREATE DATABASE [DummyDb]  
Create Table
  1. CREATE TABLE [dbo].[DummyTable](  
  2. [Id] [int] NULL,  
  3. [Name]  
  4. [varchar](100) NULL  
  5. ) ON [PRIMARY]  
Insert 100 rows
  1. DECLARE @Count int;  
  2. SET @Count = 100;  
  3. WHILE(@Count>0)  
  4. BEGIN  
  5. INSERT INTO DummyTable VALUES (@Count, 'Name_'+ CONVERT(varchar(100),@Count));  
  6. SET @Count = @Count-1;  
  7. END  
Now let's create two Stored Procedures, GetEmployee and sp_GetEmployee.

Create GetEmployee
  1. CREATE PROCEDURE GetEmployee  
  2. AS  
  3. BEGIN  
  4. SELECT ID, Name FROM DummyTable  
  5. END  
Create sp_GetEmployee
  1. CREATE PROCEDURE sp_GetEmployee  
  2. AS  
  3. BEGIN  
  4. SELECT ID, Name FROM DummyTable  
  5. END  
  6. GO  
Let's execute both of the Stored Procedures.
  • Execute GetEmployee
  1. DECLARE @Count int;  
  2. SET @Count = 500;  
  3. WHILE(@Count>0)  
  4. BEGIN  
  5. exec GetEmployee  
  6. SET @Count =  
  7. @Count-1;  
  8. 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
  1. DECLARE @Count int;  
  2. SET @Count = 500;  
  3. WHILE(@Count>0)  
  4. BEGIN  
  5. exec sp_GetEmployee  
  6. SET @Count =  
  7. @Count-1;  
  8. 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.


Similar Articles