Pages

Saturday, June 1, 2013

How to compare temporary table vs table variable speed?

The following performance measurement tests show that for this particular case temporary table performance is the same as table variable performance.

USE AdventureWorks2012;
GO

CREATE  TABLE #Product (
       [ProductID] [int]  NOT NULL PRIMARY KEY,
       [Name] nvarchar(64) NOT NULL,
       [ProductNumber] [nvarchar](25) NOT NULL,
       [MakeFlag] bit NOT NULL,
       [FinishedGoodsFlag] bit NOT NULL,
       [Color] [nvarchar](15) NULL,
       [SafetyStockLevel] [smallint] NOT NULL,
       [ReorderPoint] [smallint] NOT NULL,
       [StandardCost] [money] NOT NULL,
       [ListPrice] [money] NOT NULL,
       [Size] [nvarchar](5) NULL,
       [SizeUnitMeasureCode] [nchar](3) NULL,
       [WeightUnitMeasureCode] [nchar](3) NULL,
       [Weight] [decimal](8, 2) NULL,
       [DaysToManufacture] [int] NOT NULL,
       [ProductLine] [nchar](2) NULL,
       [Class] [nchar](2) NULL,
       [Style] [nchar](2) NULL,
       [ProductSubcategoryID] [int] NULL,
       [ProductModelID] [int] NULL,
       [SellStartDate] [datetime] NOT NULL,
       [SellEndDate] [datetime] NULL,
       [DiscontinuedDate] [datetime] NULL,
       [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
       [ModifiedDate] [datetime] NOT NULL);

GO



DECLARE @Product TABLE (
       [ProductID] [int] NOT NULL PRIMARY KEY,
       [Name] nvarchar(64) NOT NULL,
       [ProductNumber] [nvarchar](25) NOT NULL,
       [MakeFlag] bit NOT NULL,
       [FinishedGoodsFlag] bit NOT NULL,
       [Color] [nvarchar](15) NULL,
       [SafetyStockLevel] [smallint] NOT NULL,
       [ReorderPoint] [smallint] NOT NULL,
       [StandardCost] [money] NOT NULL,
       [ListPrice] [money] NOT NULL,
       [Size] [nvarchar](5) NULL,
       [SizeUnitMeasureCode] [nchar](3) NULL,
       [WeightUnitMeasureCode] [nchar](3) NULL,
       [Weight] [decimal](8, 2) NULL,
       [DaysToManufacture] [int] NOT NULL,
       [ProductLine] [nchar](2) NULL,
       [Class] [nchar](2) NULL,
       [Style] [nchar](2) NULL,
       [ProductSubcategoryID] [int] NULL,
       [ProductModelID] [int] NULL,
       [SellStartDate] [datetime] NOT NULL,
       [SellEndDate] [datetime] NULL,
       [DiscontinuedDate] [datetime] NULL,
       [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
       [ModifiedDate] [datetime] NOT NULL);

TRUNCATE TABLE #Product;
INSERT #Product SELECT * FROM Production.Product;
-- (504 row(s) affected)

INSERT @Product SELECT * FROM Production.Product;
-- (504 row(s) affected)


DECLARE  @ExecutionTime  TABLE(      Duration INT    )

DECLARE  @StartTime DATETIME,  @i INT = 1;
WHILE (@i <= 100)
  BEGIN
    DBCC DROPCLEANBUFFERS
    SET @StartTime = CURRENT_TIMESTAMP -- alternate getdate()
    /****************** measured process ***********************/

    SELECT * FROM @Product WHERE Name = 'Road-550-W Yellow, 48';

    /**************** end measured process *********************/
    INSERT @ExecutionTime
    SELECT DurationInMilliseconds = datediff(ms,@StartTime,CURRENT_TIMESTAMP)
    SET @i +=  1
  END -- WHILE

SELECT DurationInMilliseconds = AVG(Duration) FROM   @ExecutionTime
-- 22 msec
GO

-- Temporary table
DECLARE  @ExecutionTime  TABLE(      Duration INT    )
DECLARE  @StartTime DATETIME,  @i INT = 1;
WHILE (@i <= 100)
  BEGIN
    DBCC DROPCLEANBUFFERS
    SET @StartTime = CURRENT_TIMESTAMP -- alternate getdate()
    /****************** measured process ***********************/

    SELECT * FROM #Product WHERE Name = 'Road-550-W Yellow, 48';

    /**************** end measured process *********************/
    INSERT @ExecutionTime
    SELECT DurationInMilliseconds = datediff(ms,@StartTime,CURRENT_TIMESTAMP)
    SET @i +=  1
  END -- WHILE

SELECT DurationInMilliseconds = AVG(Duration) FROM   @ExecutionTime
GO
-- 23 msec

CREATE INDEX idxProdName on #Product(Name);
GO

-- Temporary table
DECLARE  @ExecutionTime  TABLE(      Duration INT    )
DECLARE  @StartTime DATETIME,  @i INT = 1;
WHILE (@i <= 100)
  BEGIN
    DBCC DROPCLEANBUFFERS
    SET @StartTime = CURRENT_TIMESTAMP -- alternate getdate()
    /****************** measured process ***********************/

    SELECT * FROM #Product WHERE Name = 'Road-550-W Yellow, 48';

    /**************** end measured process *********************/
    INSERT @ExecutionTime
    SELECT DurationInMilliseconds = datediff(ms,@StartTime,CURRENT_TIMESTAMP)
    SET @i +=  1
  END -- WHILE

SELECT DurationInMilliseconds = AVG(Duration) FROM   @ExecutionTime
-- 23 msec
GO
DROP TABLE  #Product

No comments:

Post a Comment