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] [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 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 =
@Count–1;
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 =
@Count–1;
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.
