Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Query Performance for INSERT SELECT vs INSERT EXEC


By:   |   Last Updated: 2018-12-21   |   Comments   |   Related Tips: More > T-SQL

Problem

I was asked by a developer if doing INSERT SELECT versus INSERT EXEC was any different in the way of performance and overhead in SQL Server.  They were wondering if inserting data into a table using the following commands if they would behave any differently in terms of query performance?

INSERT ... SELECT * FROM [dbo].[table]

INSERT ... EXEC ('SELECT * FROM [dbo].[table]')
Solution

While the two INSERT queries perform a straight-forward insert directly from a physical table, the INSERT ... EXEC structure will always perform slower than the equivalent INSERT ... SELECT structure. This is because the INSERT ... EXEC structure first requires populating the rows into a Parameter Table before spooling the same rows into the final destination table. Where the INSERT ... SELECT structure directly spools the rows into the final destination table avoiding the intermediate Parameter Table and hence is more efficient and executes faster.

Let's take a look at example and prove this is the case.

Demonstration

We will use the AdventureWorksDW2012 database to compare the two queries performance. The SQL Server edition and version used to run this demonstration is SQL Server 2016 Developer Edition, but that shouldn't matter. To remove the queries running in parallel, the MAXDOP 1 query hint will be applied to the queries.

The two queries being compared in this tip are executed in their own session against the [dbo].[FactProductInventory] table which contains 776,286 rows.

To capture the differences, we will use an Extended Events Session, so we can look at how they perform differently.

Query 1: SQL Server INSERT ... SELECT Query Performance

The simple INSERT ... SELECT below took approximately 1.5 seconds to complete which is indicated by the statistics time.

SELECT * INTO temp1 
FROM [AdventureWorksDW2012].[dbo].[FactProductInventory]
WHERE 1=0;
SET STATISTICS TIME ON
INSERT INTO temp1
SELECT * FROM [AdventureWorksDW2012].[dbo].[FactProductInventory] OPTION (MAXDOP 1)
SET STATISTICS TIME OFF
GO

--------------------------------------------------------------------------------
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 1484 ms,  elapsed time = 1541 ms.

When the query is running, an Extended Event session was configured and run in the background. The output of the Extended Events is viewed from SSMS using Watch Live Data. The output of Watch Live Data is then grouped by name, wait_type and opcode.

The script to configure the Extended Events can be downloaded at the bottom of this tip.

Extended Events Output for INSERT … SELECT Query

The total row count involved in INSERT ... SELECT query is 776,286 which is the count of rows in the [dbo].[FactProductInventory] table and can be seen in the screenshot below.

The MEMORY_ALLOCATION_EXT is allocation of new data pages to accommodate the new rows. As indicated in the sql_batch_completed details, the cpu_time took the largest chunk of the elapsed time (duration column). This is because [dbo].[FactProductInventory] is quite small and its data pages are already in the SQL Server buffer pool (indicated by 0 physical reads).

sql extended events output

Query 2: SQL Server INSERT ... EXEC Query Performance

The query is converted into an INSERT ... EXEC structure and executed.

The query now took 7.5 seconds to complete as indicated by the statistics time. Note that the statistics time output contains 2 parts, the first execution time took approximately 6 seconds to complete, and the second execution time took 1.5 second to complete.

As mentioned in the solution description, the first part of the query is to do with populating a Table Parameter, and the second part of the query is inserting rows into the destination table which took approximately 1.5 second.

SELECT * INTO temp2 
FROM [AdventureWorksDW2012].[dbo].[FactProductInventory]
WHERE 1=0;
SET STATISTICS TIME ON
INSERT INTO temp2
EXEC('SELECT * FROM [AdventureWorksDW2012].[dbo].[FactProductInventory] OPTION (MAXDOP 1)')
SET STATISTICS TIME OFF
GO
-----------------------------------------------------------------------------------
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 2 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 5984 ms,  elapsed time = 6000 ms.

SQL Server Execution Times:
   CPU time = 7562 ms,  elapsed time = 7574 ms.

(776286 row(s) affected)
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

Extended Events Output for INSERT … EXEC

With the INSERT ... EXEC structure, the row count that we are dealing with has doubled to 1552572 rows. This confirms the scan on [dbo].[FactProductInventory] table, as well as the clone of the table into a Parameter Table. The CPU time, duration and logical reads has also gone up. Higher counts of SOS_SCHEDULER_YIELD wait type indicates that SQL Server performs a lot more work when inserting the rows into the Parameter Table.

But reading the Extended Events output proves that INSERT ... EXEC execution is more intensive than the INSERT ... SELECT structure with higher pages allocation in memory, CPU and I/O utilization.

sql extended events output

Summary

Most of the time, the INSERT … EXEC structure is used because it allows a developer to have flexibility in building dynamic T-SQL to put data into a permanent or temporary table. But note that this flexibility comes with a penalty in performance due to higher memory, CPU and I/O utilization.

This was a simple example that you would probably never do, but this example helps illustrate the overhead of INSERT ... EXEC.

Next Steps


Last Updated: 2018-12-21


next webcast button


next tip button



About the author
MSSQLTips author Simon Liew Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Masterís Degree in Distributed Computing.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools