Why is it quicker to use a temporary table when inserting data from a user function into a table variable?

Viewed 64

I have an inline table-valued function. I want to insert the output of that function into a table variable so I can pass it to another function.

DECLARE @PeopleToEmail mb.PeopleToEmailOrPhone;

INSERT INTO
    @PeopleToEmail
SELECT
    *
FROM
    mb.GetOptedInEmails('All')
;

On my dataset, this query takes 28 seconds to run.

However, if I use an intermediary temporary table, runtime drops to around 9 seconds.

SELECT
    *
INTO
    #OptIns
FROM
    mb.GetOptedInEmails('All')
;

DECLARE @PeopleToEmail mb.PeopleToEmailOrPhone;

INSERT INTO
    @PeopleToEmail
SELECT
    *
FROM
    #OptIns

This makes no sense to me and means my code is twice as long as it needs to be. Can anyone help me

  1. Understand why this is happening.
  2. Think of another way of optimising the query without the intermediary table.

I'm stuck on SQL Server 2012. The function is quite long but you can view it here.

No Temp Table

No Temp Table

Temp Table

Temp Table

0 Answers
Related