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

Question

When I try and insert some results from an sp_executesql into a variable table, I get really bad perf.

First, the query just as a simple 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 runs in a few seconds as there's a few million results to pass over the wire from a DB in the cloud to my localhost machine. So that's totally kewl.

Query plan:

Now, lets change the query to INSERT INTO the results...

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'

This takes about 45 seconds to return the results to my localhost machine. Same Query (well, same SELECT query).

Lets look at the query plan...

Now lets 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 the plan..

• So when I run the query normally, it's ok and fast.
• When I INSERT INTO it takes 45 odd seconds.
• Same times when I have params or not.
• Same times if I use OPTION (RECOMPILE) or not.
• Why use sp_executesql instead of just the raw sql statement? Because we have a heap of dynamic WHERE / AND statements which starts to make things hard/not-that-perf-nice.

Tech..
- Sql Server 2012

Answer 1

The difference between both statements is that in the raw version:

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

... the results of the select are directly streamed into the insert statement.

But in the dynamic version:

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'

... the results of the EXEC sp_executesql is cumulated into a sort of a temporary table called the Parameter Table (you can see the extra step in your execution plan). Only after this temporary table has been populated with the millions of rows does the insert statement actually start reading the data. This is much slower.

You may be able to get around this performance degradation if you can somehow refactor the code to push the INSERT statement inside the EXEC sp_executesql call. By doing that, the results of the SELECT statement could once again be streamed directly to the INSERT statement.

References: Here is an interesting article that discusses the problem you are facing: The Hidden Costs of INSERT EXEC.

A little aside: you don't need the ORDER BY clause if all you are doing is inserting the data right after.