EXEC sp_executesql is really very slow when used with INSERT INTO :(

Question

When I try to insert some results from sp_executesqlinto a variable table, I get a very bad perfection.

First, the query is just as simple as Select.

EXEC sp_executesql N'SELECT      [a].[ListingId]
FROM        [dbo].[Listings] [a] LEFT OUTER JOIN 
            [dbo].[AgencyCompany] [b] ON [a].[AgencyCompanyId] = [b].[AgencyCompanyId]
ORDER BY    UpdatedOn DESC'

This is done in a few seconds, as several million results are transferred by wire from the database in the cloud to my local machine. So totally kewl.

Request Plan:

Now let's change the query for the INSERT INTOresults ...

DECLARE @ListingIds TABLE (ListingId INTEGER PRIMARY KEY)
INSERT INTO @ListingIds
EXEC sp_executesql N'SELECT      [a].[ListingId]
FROM        [dbo].[Listings] [a] LEFT OUTER JOIN 
            [dbo].[AgencyCompany] [b] ON [a].[AgencyCompanyId] = [b].[AgencyCompanyId]
ORDER BY    UpdatedOn DESC'

It takes about 45 seconds to return the results to my localhost machine. The same request (well, the same request SELECT).

Let's look at the query plan ...

Now try this with raw sql ...

DECLARE @ListingIds TABLE (ListingId INTEGER PRIMARY KEY)
INSERT INTO @ListingIds
SELECT      [a].[ListingId]
FROM        [dbo].[Listings] [a] LEFT OUTER JOIN 
            [dbo].[AgencyCompany] [b] ON [a].[AgencyCompanyId] = [b].[AgencyCompanyId]
ORDER BY    UpdatedOn DESC

4 seconds to run and plan ..

• , , .
• INSERT INTO 45 .
• , .
• , OPTION (RECOMPILE) .
• sp_executesql sql? WHERE / AND, /--perf-nice.

..
- Sql 2012

Answer 1

, :

DECLARE @ListingIds TABLE (ListingId INTEGER PRIMARY KEY)
INSERT INTO @ListingIds
SELECT      [a].[ListingId]
FROM        [dbo].[Listings] [a] LEFT OUTER JOIN 
            [dbo].[AgencyCompany] [b] ON [a].[AgencyCompanyId] = [b].[AgencyCompanyId]
ORDER BY    UpdatedOn DESC

... select insert.

:

DECLARE @ListingIds TABLE (ListingId INTEGER PRIMARY KEY)
INSERT INTO @ListingIds
EXEC sp_executesql N'SELECT      [a].[ListingId]
FROM        [dbo].[Listings] [a] LEFT OUTER JOIN 
            [dbo].[AgencyCompany] [b] ON [a].[AgencyCompanyId] = [b].[AgencyCompanyId]
ORDER BY    UpdatedOn DESC'

... EXEC sp_executesql , ( ). , , insert . .

, , - , insert EXEC sp_executesql. , select insert.

. , , : INSERT EXEC.

: ORDER BY, , , .

Answer 2

.

, " " , . . , , . .

? , . , .

, insert into , , . , .

EDIT:

. , . . , . 10 .

Answer 3

, , , . , .

.