THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter Larsson

Thinking outside the box

Performance consideration when using a Table Variable

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)
 
Published Thursday, October 15, 2009 4:26 PM by Peso
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Nick Beagley said:

I knew a plan that included a join on a table variable would never be parallel, i didn't realise it would also affect populating the table variable.

There's a good comparison of table variables and temp tables on sql server central (and the comments): http://www.sqlservercentral.com/articles/Temporary+Tables/66720/

October 15, 2009 7:05 PM
 

Everest said:

Amazing to me that a table variable, which is represented as a # table in tempdb, has all of the functionality chopped from it when comparint to its counterpart.

Good article

Thanks

Lee

October 19, 2009 10:03 PM
 

SQL and the like said:

It is widely know that data modifications on table variables do not support parallelism, Peter Larsson

March 4, 2010 4:32 AM
 

Peso said:

Yes, I wrote that I came across this last time in 2007.

March 4, 2010 5:06 AM
 

Martin Smith said:

@Peso As I'm sure you realised since writing this operations on table variables are logged.

@Nick - Plans that join on table variables can certainly be parallel but you may need an `OPTION (RECOMPILE)` hint for this to happen so that SQL Server takes account actual row counts. Here is an example http://stackoverflow.com/questions/1645846/can-queries-that-read-table-variables-generate-parallel-exection-plans-in-sql-se/8242831#8242831

Also I'll plug my write up on the differences here http://dba.stackexchange.com/a/16386/3690

December 24, 2012 7:36 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement