SQL Server – Parallelism with Temp Table but Not Table Variable

parallelismsql servertable variabletemporary-tables

The first query (inserts into table variable) takes twice as long as the second one. It does not use parallelism in the execution plan.

The second query (inserts into temp table) uses parallelism in its execution plan and is able to achieve the results in almost half the time.

I am trying to run this from a table function hence the need for the table variable as opposed to the temp table.

The execution plan is quite complex and I would prefer not to dive in that direction (yet). I am wondering if someone has an explanation or hypothesis of why the first SQL is not using parallelism, while the second is.

First:

DECLARE @TableVar as TABLE (
    [Date] [date] NULL,
    [B] [int] NULL,
    [C] [decimal](5, 3) NULL)

INSERT INTO 
    @TableVar
SELECT 
    [Date]        = CAST(LO.Dt as Date)
  , [B]           = DMC.[B]
  , [C]           = DMC.[C]
FROM                  
                 dbo.fnTblFunc1(@DateStart, @DateEnd) AS DMC 
    INNER JOIN   dbo.fnTblFunc2(@DateStart, @DateEnd) AS LO ON DMC.Date = LO.Dt
OPTION (FORCE ORDER  )

Second:

CREATE TABLE #TempTbl(
    [Date] [date] NULL,
    [B] [int] NULL,
    [C] [decimal](5, 3) NULL)

INSERT INTO 
    #TempTbl
SELECT 
    [Date]        = CAST(LO.Dt as Date)
  , [B]           = DMC.[B]
  , [C]           = DMC.[C]
FROM                  
                 dbo.fnTblFunc1(@DateStart, @DateEnd) AS DMC 
    INNER JOIN   dbo.fnTblFunc2(@DateStart, @DateEnd) AS LO ON DMC.Date = LO.Dt
OPTION (FORCE ORDER  )

DROP TABLE #TempTbl

Best Answer

Quote below from this question on the differences between table variables and temp tables. Look at the section on parallelism.

Queries that insert into (or otherwise modify) @table_variables cannot have a parallel plan, #temp_tables are not restricted in this manner.

Related Solutions

SQL Server 2005 – INSERT Performance: Temporary Tables vs Table Variables

The obvious difference between the two plans is that the fast one is parallel and the slower one serial.

This is one of the limitations of plans that insert into table variables. As mentioned in the comments (and it seems as though it had the desired effect) you could try doing

INSERT INTO @DATA ( ... ) 
EXEC('SELECT .. FROM ...')

to see if that gets around the limitation.

SQL Server – Massive Joins vs Updating Temp Table

SQL Server has an upper bound on creating efficient query plans given a moderately complex query involving a lot of joins - there isn't a single upper bound or magic formula to determine when a query is complex enough to cause a problem; it is very case-by-case and involves baselining from some known expectation (people sometimes think a certain query shouldn't take 30 seconds, but sometimes that's really the best and most optimal outcome for that context). In other cases, SQL Server is simply overwhelmed and can't (at least when not given infinite time) quickly come up with an optimal plan. So it will spend some time coming up with a "best plan I can find in this time" and then it will show "Reason for early termination: timeout" and you get what you get. You can force SQL Server to spend more time coming up with a plan using trace flag 2301 (which Paul White mentions and links to a more thorough piece in this answer), but more time may not prove to help that much (and more compile time may not be a good thing for end user performance on the whole). Another alternative is to break up the logic so SQL Server can focus on optimizing smaller, less complex queries.

Think of it like eating a pie - you can probably eat a pretty good-sized piece in one sitting, but if your goal is to eat the whole pie, you might want to spread it out across multiple settings.

For SQL Server, in some cases it can do better with chunks of that logic separated. This doesn't mean you should simplify all joins into #temp table waterfalls, but in some extreme cases it is a valid workaround.

Related Question