Sql-server – Duplicate all stored procedures with a new name

This is fairly easy to test yourself. Let's create two very simple procedures:

CREATE PROCEDURE dbo.sp_mystuff
AS
  SELECT 'x';
GO
CREATE PROCEDURE dbo.mystuff
AS
  SELECT 'x';
GO

Now let's build a wrapper that executes them a number of times, with and without the schema prefix:

CREATE PROCEDURE dbo.wrapper_sp1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC sp_mystuff;
      SET @i += 1;
    END
END
GO
CREATE PROCEDURE dbo.wrapper_1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC mystuff;
      SET @i += 1;
    END
END
GO
CREATE PROCEDURE dbo.wrapper_sp2
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC dbo.sp_mystuff;
      SET @i += 1;
    END
END
GO
CREATE PROCEDURE dbo.wrapper_2
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC dbo.mystuff;
      SET @i += 1;
    END
END
GO

Results:

Conclusions:

• using sp_ prefix is slower
• leaving out schema prefix is slower

The more important question: why would you want to use the sp_ prefix? What do your co-workers expect to gain from doing so? This shouldn't be about you having to prove that this is worse, it should be about them justifying adding the same three-letter prefix to every single stored procedure in the system. I fail to see the benefit.

Also I performed some pretty extensive testing of this pattern in the following blog post:

http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix

Thanks SQL Kiwi, you answered my question. The temp tables are all #temp tables. After reading your comments I thought why not try rename all the temp tables and make each a unique name.

I went thought each procedure and gave each #temp table a unique name per stored procedure, then I ran the command

DBCC FREEPROCCACHE;

Then then tried running my main procedure again and all seems to be working again. SQL must be getting confused with the temp table names being the same in the all the stored procedures. What I cannot explain is why from the SSRS server the report executes and from SSMS I get errors (On our Live Environment Only)

I unfortunately have taken this system over from an outsourced development house and I would not have done things they way they did it. I have been slowly replacing some of the temp tables with CTE's & re factoring out some of the un necessary joins, in most cases this gives us better performance.