By: Simon Liew | Updated: 2018-12-21 | Comments (3) | Related: > TSQL
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).
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.
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
- Download the Extended Events configuration script
- Here are some related tips:
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-12-21
