Working on a client's data migration recently has given me the chance to benchmark table variables against traditional temp tables in SQL 2008.
The server I used was not especially fast and tempdb is even stored on the same drive as the rest of the databases. Still, the results have shown that table variables were at least 10x faster in every test.
The most telling scenario was as follows:
There was a table with 2.8Million rows. One of the fields of interest was a 500 character comment field.
The application that populated this table added a record for each comment with no regard to keys (there was no unique key). However, I noticed that there was a combination of fields that could be used for a unique key, but there were several thousand instances where I would need to concatenate the comment field from more than one record to make the key unique.
Example:
Key Comment
1 abc
1 def
2 der
3 mmd
3 xyz
Need to be imported to the new table as:
1 abcdef
2 der
3 mmdxyz
There was a bit more complexity involved, but hopefully, you get the idea.
So I needed to use xml_path to concat the values like this:
SELECT A.KEY,
(SELECT COMMENTFIELD AS 'text()'
FROM MYTABLE
WHERE KEY=A.KEY
ORDER BY KEY ASC
FOR XML_PATH(''))
FROM MYTABLE A
The problem is that this returns a recordset like this:
1 abcdef
1 abcdef
2 der
3 mmdxyz
3 mmdxyz
No problem, I just need to group them, right? Well there are 2.8M rows here! Time to benchmark!
I first wrote this using #temp tables.
SELECT A.KEY,
(SELECT COMMENTFIELD AS 'text()'
FROM MYTABLE
WHERE KEY=A.KEY
ORDER BY KEY ASC
FOR XML_PATH(''))
INTO #MYTEMP
FROM MYTABLE A
The insert and grouping alone took 9.5 hours.
I then rewrote it to use table variables
DECLARE @MYTEMP AS TABLE(
KEY AS INT,
COMMENTFIELD AS VARCHAR(1000)
)
INSERT INTO @MYTEMP
SELECT A.KEY,
(SELECT COMMENTFIELD AS 'text()'
FROM MYTABLE
WHERE KEY=A.KEY
ORDER BY KEY ASC
FOR XML_PATH(''))
FROM MYTABLE A
This same logic took 21 minutes! That means it only took 4% as long to run!
If that doesn't make the case for using table variables over temp tables, I don't know what will. From now on, I will default to the table variable when writing code and only consider the temp table for special cases where I need to pass the dataset around.
Did this help you? If so, please leave a comment!
