You are here:Home » tsql » Execute with ResultSets and Performance Review - SQL Server 2011

Execute with ResultSets and Performance Review - SQL Server 2011

I have wrote for the best feature of SQL Server 2011 in my earlier post here, http://paresh-sqldba.blogspot.com/2011/05/with-result-sets-tsql-enhancement-in.html.
There i have given the demonstration of the usage of Resultset with execution of the stored procedures.

Using this enhancement we don not require temp table and then insert the records from executing stored procedure in case when we mostly require change data and also change column name from resultset of stored procedure.

Here i would like demonstrate the same but something different from earlier. In this demo i will explain the usage and performance review with new method of enhanced Resulset and old method using temp table to get the data by executing stored procedure.

We need some demo script for table creation and get populated some data in table.

USE DBWithResultSet
GO

-- Creating table 
IF( Object_id('WithResultSetTab') > 0 )
  DROP TABLE WithResultSetTab

GO

CREATE TABLE WithResultSetTab
  (
     LogId   INT IDENTITY (1, 1),
     LogDesc VARCHAR(100),
     LogDate DATETIME
  )

GO

-- Inserting sample records
INSERT WithResultSetTab
SELECT 'Descrption of the Logs occured during testing',
       Getdate()
FROM SYS.OBJECTS a
CROSS JOIN SYS.OBJECTS b

GO

Now here we will create one stored procedure which will get the data from above created table.
Let us create it.

CREATE PROCEDURE Getresultsetfromtab
AS
  BEGIN
      SET nocount ON

      SELECT LogId,
             LogDesc,
             LogDate
      FROM   WithResultSetTab
  END

GO

We will run first and old method to get the data from stored procdure execution as well as call second enhance method for the same.
Let us run the script below using both methods.

CREATE TABLE #tempResultSet
(
 TempLogId int,
 TempLogDetail varchar(10) ,  
 TempLogdate varchar(20)
)

INSERT INTO #tempResultSet (TempLogId, TempLogDetail, TempLogdate)
EXEC Getresultsetfromtab

SELECT 
TempLogId, 
TempLogDetail, 
TempLogdate
FROM #tempResultSet

GO

EXEC Getresultsetfromtab
WITH RESULT SETS
(
 (
 LogId int,
 LogDetail varchar(10) ,  
 Logdate varchar(20)
 )
)

GO 



As you can see from above script when we require column change from execution of the stored procedure then first and second methods work, but for table data or colunm lengh or column data type change first method raised error but second method work fine.
In second method we have changed column name of LogDetail and also its length, LogDate converted to varchar datatype as well.

Now we will go on performance and review execution plans using both of the methods.
Let us run the script and check how performance are appearing.

DROP TABLE #tempResultSet

CREATE TABLE #tempResultSet
(
 TempLogId int,
 TempLogDetail varchar(100) ,  
 TempLogdate datetime
)

INSERT INTO #tempResultSet (TempLogId, TempLogDetail, TempLogdate)
EXEC Getresultsetfromtab

SELECT 
TempLogId, 
TempLogDetail, 
TempLogdate
FROM #tempResultSet

EXEC Getresultsetfromtab
WITH RESULT SETS
(
 (
 TempLogId int,
 TempLogDetail varchar(100) ,  
 TempLogdate datetime
 )
) 

GO 


You can see the execution plan here and difference of the execution time using temp table and tsql Resultset enhancement with execution of the stored procedures.

I woluld like you to share your comments if you are using any of these methos and experience using them.