Let us run the following script and first create a Temporary Table. Right after that, we will use the same data to populate our Table Variable. Following that, we will run a simple select statement and compare its performance.
-- Create Table
CREATE TABLE #FirstIndex (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100));
-- INSERT 1
INSERT INTO #FirstIndex (ID,FirstName,LastName,City)
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%12345 = 1 THEN 'Las Vegas'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;
-- -----------------------------
-- Create Table Variable
DECLARE @FirstIndex TABLE (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100));
-- INSERT 1
INSERT INTO @FirstIndex (ID,FirstName,LastName,City)
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%12345 = 1 THEN 'Las Vegas'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;
-- --------------------------------------------
-- Performance Co mparision
SET STATISTICS IO,TIME ON;
SELECT *
FROM #FirstIndex
WHERE City = 'Las Vegas';
SELECT *
FROM @FirstIndex
WHERE City = 'Las Vegas';
SET STATISTICS TIME OFF;
-- Clean up
DROP TABLE #FirstIndex
GO
Please make sure that you run the above script in a SINGLE execution or it will give an error. Table Variables have to be created and consumed in the same sessions.
Now when we observe the Statistics IO or statistics Time there is not much difference between them.
For Temp Table:
Table ‘#FirstIndex00000000000C’. Scan count 1, logical reads 4546
SQL Server Execution Times:
CPU time = 62 ms, elapsed time = 60 ms.
For Table Variable:
Table ‘#B8CBF7CD’. Scan count 1, logical reads 4546, physical reads 0
SQL Server Execution Times:
CPU time = 63 ms, elapsed time = 74 ms.
However, when we see the execution plan we see that table variables are not estimating the rows to be returned correctly and they show value as 1, whereas in the case of the Temp Table we are getting a correct estimation.
.gif)
0 Comments