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