x

table variable vs temp table performance issue

I have a query that is something like the below snippet

DECLARE @QueuedItems TABLE 
        (   key BIGINT
            , priority tinyint
            , sortOrder bit            )

INSERT INTO @QueuedItems(Key
            ,priority
            ,sortOrder            )

SELECT DISTINCT TOP(10) q.Key
        , ISNULL(r.priority,0) AS priority
        , ISNULL(sortOrder,0) AS sortOrder
    FROM dbo.Queue q (NOLOCK)
        INNER JOIN dbo.statQueueReason r (NOLOCK)
            ON q.queueReasonKey = r.queueReasonKey
    WHERE txnStatusKey = 4 /* Match */
    ORDER BY
            ISNULL(r.priority,0) 
            , ISNULL(sortOrder,0) DESC
            , q.Key ASC

SELECT * FROM @QueuedItems

This SQL Snippet takes 33 seconds to execute. However when modified to using temporary table, it takes just 4 seconds to complete. Here is the modified snippet

CREATE TABLE #QueuedItems
(   Key BIGINT
    , priority tinyint
    , sortOrder bit    )

INSERT INTO #QueuedItems(key,priority, sortorder)

SELECT DISTINCT TOP(10) q.Key
        , ISNULL(r.priority,0) AS priority
        , ISNULL(sortOrder,0) AS sortOrder
    FROM dbo.Queue q (NOLOCK)
        INNER JOIN dbo.statQueueReason r (NOLOCK)
            ON q.queueReasonKey = r.queueReasonKey
    WHERE txnStatusKey = 4 /* Match */
    ORDER BY ISNULL(r.priority,0)
            , ISNULL(SortOrder,0) DESC

    SELECT * FROM #QueuedItems

Please note that the Queue table has around 11 million rows. My understanding was if we are selecting only small amount of data (10 in this case), table variables are the way to go. However this reversal of performance was a surprise to me. Wondering if anyone can shed some light on it.

Thanks for your all time in advance.
more ▼

asked Sep 15, 2010 at 12:31 PM in Default

kesavnag gravatar image

kesavnag
11 1 1 2

did you flush the cache between executions?
Sep 15, 2010 at 12:45 PM Fatherjack ♦♦
@kesavnag What is really alarming is that your statement takes 4 seconds to execute. If the dbo.Queue table has only 11 million rows then there should be no way that it takes 4 seconds to select 10 records from the join of just 2 tables where the second table named dbo.statQueueReason is probably a small catalog. Is there any way to restate your select statement? Can provide the information about 2 columns which are not properly referenced (sortOrder and txnStatusKey)?
Sep 15, 2010 at 01:01 PM Oleg

+1 (well, thumbs up) to you both.

I would think that Fatherjack is right here about caching.

@kesavng - Could you also supply us with the table creation scripts (including the indexes if there are any).

I have a sneaking suspicion that you will be doing a table scan/clustered index scan on your big table (Queue) to find the correct information and that will be what is causing the slowdown (you are doing sorts and a distinct here) - I don't think it has anything to do with using a temp table or temp variable.
Sep 15, 2010 at 01:16 PM WilliamD
Another request, to add to @WilliamD's is that you take a look at, and post if possible, the query plan. This could well give you insight into the differences. I do tend to agree though that there shouldnt be much difference between inserting 10 rows into a temp table and a table variable, if it was 10k rows then may be ...
Sep 15, 2010 at 01:47 PM Fatherjack ♦♦
Yeah, I wanted to suggest the query plan too, but it is difficult to do that on here.... we can hope :)
Sep 15, 2010 at 01:50 PM WilliamD
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

This is likely to be due to the fact that inserting into a table variable prevents parallelism from occurring in most circumstances. Have a look at the differences in the execution plans generated for the two statements, and you'll probably find that's why you're suffering.

From [table (Transact-SQL) on MSDN][1]:

Important
Queries that modify table variables do not generate parallel query execution plans. Performance can be affected when very large table variables, or table variables in complex queries, are modified. In these situations, consider using temporary tables instead. For more information, see CREATE TABLE (Transact-SQL). Queries that read table variables without modifying them can still be parallelized.

However, as Oleg commented, it's pretty likely that you're missing an index somewhere because selecting 10 rows from such a small table should be pretty much instant, in which case the difference between having parallelism or not would be negated by the fact that you would be using index seeks rather than scans.

[1]: http://msdn.microsoft.com/en-us/library/ms175010.aspx
more ▼

answered Sep 21, 2010 at 12:55 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

Hmm, I just noticed I'm about a week late with this answer. How did that happen?!? :)
Sep 21, 2010 at 08:40 AM Matt Whitfield ♦♦
+1 on parallelism, main reason I will opt for temp tables in addition to indexes... He is selecting 10 rows from 11 million, the ORDER BY is constraining him. Perhaps a wider clustered index that always kept the queue table in the correct order. Then you can simply select the top 10 without the sort operation
Sep 21, 2010 at 09:46 AM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

My understanding is #temp tables are created in tempdb whereas a table variable is stored in memory with some tempdb I/O. With 11 million rows you are probably taxing your memory and causing it to page out whereas SQL is able to handle dumping 11M rows to disk much more efficently.

Another thing that pops up in my head that I read somewhere is that SQL will not automaticlly statistics on table variables. That is another handicap.

In some cases temp tables are the solution, in other cases table variables are. IT DEPENDS.
more ▼

answered Sep 15, 2010 at 12:58 PM

Tim gravatar image

Tim
36.4k 38 41 139

@TRAD - the idea about a temp. variable being only memory resident is a myth. The difference between the two is as you later stated; table variables do not have statistics generated on them and are therefore a bit easier going on the overhead.
Sep 15, 2010 at 01:07 PM WilliamD
Thanks @WilliamD. I learn more everyday.
Sep 15, 2010 at 01:34 PM Tim

There is another important difference - table variables are not subjected to transaction commits and rollbacks, i.e. (best with results to text)

set nocount on;

begin tran;

    select 1 boloney into #phony;
    declare @phony table(boloney int identity(1, 1));
    insert into @phony default values;

rollback tran;

select * from @phony;
select * from #phony;

set nocount off;
go
Both temp table and table variable are created and populated with data after transaction began. Once it rolled back, temp table does not even exist because its creation and population was rolled back. The table variable exists and still gets to keep its record which was inserted into it inside of the transaction which then got rolled back :)
Sep 15, 2010 at 01:53 PM Oleg

@Fatherjack -- No I have not flushed between executions.

@WilliamD --- Here is the create script for the Queue table. I am breaking it into 2 consecutive comments.

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Queue]( [QueueKey] [bigint] IDENTITY(1,1) NOT NULL, [Key] [bigint] NOT NULL, [processKey] [tinyint] NOT NULL, [queueReasonKey] [tinyint] NOT NULL, [txnStatusKey] [tinyint] NOT NULL, [createDate] [datetime] NOT NULL CONSTRAINT [DF_Queue_createDate] DEFAULT (getdate()), [updateDate] [datetime] NULL, [sortOrder] [bit] NULL, CONSTRAINT [PK_Queue] PRIMARY KEY CLUSTERED ( [QueueKey] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] GO
Sep 20, 2010 at 05:27 PM kesavnag
CREATE NONCLUSTERED INDEX [IX_Key] ON [dbo].[Queue] ( [Key] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IX_txnStatusKey] ON [dbo].[Queue] ( [txnStatusKey] ASC ) INCLUDE ( [Key]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] GO ALTER TABLE [dbo].[Queue] WITH CHECK ADD CONSTRAINT [FK_Queue_MainKeyTable] FOREIGN KEY([Key]) REFERENCES [dbo]. MainKeyTable GO ALTER TABLE [dbo].[Queue] CHECK CONSTRAINT [FK_Queue_MainKeyTable] GO
Sep 20, 2010 at 05:29 PM kesavnag
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x252
x22

asked: Sep 15, 2010 at 12:31 PM

Seen: 5244 times

Last Updated: Sep 15, 2010 at 12:42 PM