DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
 
 
SITE SEARCH SQLUSA.com VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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

 

 

Exam Prep 70-461
SQL 2016 DESIGN & PROGRAMMING
 
 
 
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE