Peter Larsson Blog

Patron Saint of Lost Yaks

Performance consideration when using a Table Variable

by Peter Larsson in  , ,

This is nothing new to me (I come across this autumn 2007) and probably not to you either but I forgot about it and yesterday it came alive again.
I often choose a table variable for performance reasons (if data is less than 1 page anyway) due to the benefits of no logging etc etc.

But yesterday I wrote a query for Phil Factor's "Subscription List" competition where I had choosen a table variable for the same reasons as before. It took a while to realize why (in this case) the solution with a temporary table was 30% faster than the solution with a table variable.

So I started to investigate the issue. SQL Profiler proofed me right over and over again for the 30% performance gain when using a temporary table over a table variable. It just didn't make any sense to me that the performance should differ that much. The INSERT was derived from a million record table, and the final resultset after grouping was just 120 records of 14 bytes each, well below the "one page rule". How come the solution with table variable was so much slower?
Remember, in this fierce competition we calculated milliseconds for who was in the lead, so 30% was a giant leap.

I won't bore you with details, but finally it come clear to me that it seems table variables doesn't support parallelism. So I went to Google for searching this issue and the results supported my finding. Insert to a table variables makes parallelism impossible. Why? A table variable is just like any other table (except logging).
I found this KB article http://support.microsoft.com/default.aspx/kb/305977/EN-US/ (the INSERT ... EXEC is suppoted with SQL Server 2008).
And this http://msdn.microsoft.com/en-us/library/ms175010(SQL.90).aspx. However it doesn't say why a table doesn't support parallelism, just that doesn't support parallelism.
Here is a page from the SQL Server Engine Storage Team http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx

Here is a testcase in a million record table.

-- First with a temporary table
CREATE TABLE    #Sample
                (
                    DateJoined SMALLINT NOT NULL,
                    DateLeft SMALLINT,
                    Registrations INT NOT NULL
                )
 
INSERT      #Sample
            (
                DateJoined,
                DateLeft,
                Registrations
            )
SELECT      DATEDIFF(MONTH, 0, DateJoined),
            DATEDIFF(MONTH, 0, DateLeft),
            COUNT(*)
FROM        dbo.Registrations
GROUP BY    DATEDIFF(MONTH, 0, DateJoined),
            DATEDIFF(MONTH, 0, DateLeft)
 
DROP TABLE #Sample
 
-- Second with a table variable
DECLARE @Sample TABLE
        (
            DateJoined SMALLINT NOT NULL,
            DateLeft SMALLINT,
            Registrations INT NOT NULL
        )
 
INSERT     @Sample
            (
                DateJoined,
                DateLeft,
                Registrations
            )
SELECT      DATEDIFF(MONTH, 0, DateJoined),
            DATEDIFF(MONTH, 0, DateLeft),
            COUNT(*)
FROM        dbo.Registrations
GROUP BY    DATEDIFF(MONTH, 0, DateJoined),
            DATEDIFF(MONTH, 0, DateLeft)
 
-- And then a conventional SELECT
SELECT      DATEDIFF(MONTH, 0, DateJoined),
            DATEDIFF(MONTH, 0, DateLeft),
            COUNT(*)
FROM        dbo.Registrations
GROUP BY    DATEDIFF(MONTH, 0, DateJoined),
            DATEDIFF(MONTH, 0, DateLeft)