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