Pure.Krome Pure.Krome - 4 months ago 28
SQL Question

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

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:

enter image description here

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...

enter image description here

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..

enter image description here




  • 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

enter image description here

Answer

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.