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
What is funny about temporary tables in a stored procedure is not so much the transient existence of the table (which gets dropped upon the DB connection's termination), but the scope of the stored procedure.
Someone asked this question on StackOverflow : Scope of temp tables created in MySQL stored procedure. It has been over a year and nobody answered the question? Let me set the record straight. The fact is: The temp table exists inside and outside of the Stored Procedure, but you can do things with the temporary table only inside the scope of a running Stored Procedure.
According to the Book
Chapter 5 has a subheading Returning Result Sets to Another Stored Procedure.
It says in paragraph 2 on Page 117:
Unfortunately, the only way to pass a result set from one stored procedure to another is to pass the results via a temporary table. This is an awkward solution b, and -- because the temporary table has scope throughout the entire session -- it creates many of the same maintainability issues raised by the use of global variables. but if one stored program needs to supply another stored program with results, then a temporary table can be the best solution.
Looking back at the StackOverflow question, I can see someone called the Stored Procedure from the mysql client. Since the mysql client is not a Stored Procedure, the results cannot be manipulated the mysql client level via DML other than doing a SELECT to see the results. Since you calling a recursive stored procedure, you can rest assured the temp table is fully accessible for the duration of the DB Connection.
I hope this answers your question.
UPDATE 2014-01-31 11:26 EST
In your last comment, you said
If we employ persistent connections, will the MEMORY table persist through multiple REQUESTS, and it seems it will, so for performance sake, I'm assuming that using this method will *REQUIRE us to explicitly DROP the temporary MEMORY table. Do I assume correctly?
Yes and No. I say Yes because it is one way to do it. I say no because another way to do it is:
CREATE TEMPORARY TABLE IF NOT EXISTS id_list (iid CHAR(32) NOT NULL) ENGINE=memory;
TRUNCATE TABLE id_list;
Whichever way you choose, the operation is still the same since TRUNCATE TABLE drops and recreates the table. This will not harm other DB Connections since each Connection has its own id_list table.
Best Answer
Any case the SP is executed using some client connection (from Event Scheduler, for example). If they are called one-by-one from some outer SP/UDF - they are executed in the same connection, and temp table created by 1st SP exists when 2nd starts. If they are executed independently (for example, as/from 2 separate event procedures) - they are executed in different connections, and each have its own independent (local) temp table.
Server only accept queries and returns its results. Anything that sends queries and receives its results is client. See
SHOW PROCESSLIST
- you will see all client connections, including Event Scheduler service connection and executed events connections, separate connection for each separate event.Temporary tables have a connection scope. If you execute 2 SPs in two different connections, each have its own temp table with its own structure and data despite the fact that their names are the same.
If you execute one SP then another, but close connection after executing 1st and create new for executing 2nd - each SP will have its own temp table again.
But if you execute one SP then another in the same connection then temptable created by 1st will exist while 2nd start, with the data inserted by 1st. - akina