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.

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

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 (http://blog.SQLAuthority.com)

5 thoughts on “SQL SERVER – Performance Improvement with of Executing Stored Procedure with Result Sets in SQL Server 2012

  1. Pingback: SQL SERVER – Weekly Series – Memory Lane – #027 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s