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

About these ads

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