3

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
6

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.