SQL SERVER – Performance Improvement with of Executing Stored Procedure with Result Sets in SQL Server 2012

Earlier I posted article SQL SERVER – Denali – Executing Stored Procedure with Result Sets. After reading this SQL Expert Ramdas asked following and very interesting question:

This is a nice feature and i am sure would be used a lot. How is the performance of this as compared with using temp tables?

I really loved this question, I ran the following code and measured the performance difference using execution plans.

USE AdventureWorks2008R2
GO
CREATE PROCEDURE mySP (@ShiftID INT)
AS
SELECT [ShiftID] ,[Name] ,[StartTime] ,[EndTime] ,[ModifiedDate] FROM [HumanResources].[Shift] WHERE [ShiftID] = @ShiftID
GO
-- Executing Stored Procedure
EXEC mySP @ShiftID = 2
WITH RESULT SETS
(
(
[ShiftID] TINYINT
,[Name] NVARCHAR(50)
,[StartTime] DATETIME
,[EndTime] DATETIME
,[UpdateDate] DATETIME -- Notice Name Change
)
);
GO
-- Create TempTable and Inserting into it
CREATE TABLE #mySP
([ShiftID] TINYINT
,[Name] NVARCHAR(50)
,[StartTime] DATETIME
,[EndTime] DATETIME
,[UpdateDate] DATETIME)
INSERT INTO #mySP
EXEC mySP @ShiftID = 2;
SELECT *
FROM #mySP
GO
-- Clean up
DROP TABLE #mySP;
DROP PROCEDURE mySP;


Let us examine the results.

From the resultset it is very clear that new method takes only 14% where as compared to it the old method which has cost of 84%. I love the new method in ‘Denali’. Thanks Ramdas for interesting question.

Reference: Pinal Dave (https://blog.sqlauthority.com)