Alex_Kuznetsov

Just a very brief example:

1. Create 10K stored procedures. 

DECLARE @i INT, @SQL NVARCHAR(300)
SELECT @i = 0
WHILE @i < 10000 BEGIN
  SET @i = @i + 1
  SET @SQL = 'CREATE PROCEDURE dbo.DummyProc' + CAST(@i AS NVARCHAR(10)) + ' AS RETURN 0'
  EXEC sp_executesql @SQL
END
GO

 2. Run them.

DECLARE @i INT, @j INT, @d DATETIME, @SQL NVARCHAR(300), @SQL_to_run NVARCHAR(300)
SELECT @i = 0, @j = 0, @d = getdate()
WHILE @j < 3 BEGIN
  SET @j = @j + 1
  IF @j = 1 BEGIN
    SET @SQL = 'EXEC DummyProc'
  END ELSE BEGIN
    SET @SQL = 'EXEC dbo.DummyProc'
  END
  WHILE @i < 10000 BEGIN
    SET @i = @i + 1
    SET @SQL_to_run = @SQL + CAST(@i AS NVARCHAR(10))
    --PRINT @SQL_to_run
    IF @j <3 BEGIN
      EXEC sp_executesql @SQL_to_run
    END
  END
  SELECT DATEDIFF(ms, @d, GEtdate()) as ms
  SELECT @i = 0, @d = getdate()
END
GO

 3. Clean up.

DECLARE @i INT, @SQL NVARCHAR(300)
SELECT @i = 0
WHILE @i < 10000 BEGIN
  SET @i = @i + 1
  SET @SQL = 'DROP PROCEDURE dbo.DummyProc' + CAST(@i AS NVARCHAR(10))
  EXEC sp_executesql @SQL
END
GO

The results:

ms - no schema names
-----------
3860

(1 row(s) affected)

ms - with schema names
-----------
640

(1 row(s) affected)

ms - the overhead of running the loop without sp_executesql calls
-----------
46

 However, if you repeatedly call one and the same procedure, you will not notice much difference one way or another:

CREATE PROCEDURE dbo.DummyProc
AS
RETURN 0
GO
CREATE PROCEDURE dbo.Run_SQL_100K_times
  @SQL NVARCHAR(30)
AS
DECLARE @i INT, @d DATETIME
SELECT @i = 0, @d = getdate()
WHILE @i < 100000 BEGIN
  SET @i = @i + 1
  EXEC sp_executesql @SQL
END
SELECT DATEDIFF(ms, @d, GEtdate()) as ms, @SQL AS SQL
GO
EXEC dbo.Run_SQL_100K_times 'EXEC DummyProc'
EXEC dbo.Run_SQL_100K_times 'EXEC dbo.DummyProc'
GO
DROP PROCEDURE dbo.Run_SQL_100K_times
DROP PROCEDURE dbo.DummyProc
GO

ms          SQL
----------- ------------------------------
2330        EXEC DummyProc

(1 row(s) affected)

ms          SQL
----------- ------------------------------
2310        EXEC dbo.DummyProc

(1 row(s) affected)