Learn more about SQL Server tools

 

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

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

SQL Server Performance of SELECT INTO vs INSERT INTO for temporary tables


By:   |   Read Comments (7)   |   Related Tips: More > T-SQL

Problem

SQL Server includes SELECT...INTO and INSERT...INTO code for inserting data into temporary tables. Can you provide some samples and outline which option performs better?

Solution

SQL Server includes the two options for temporary tables:

  • Local temporary table
  • Global temporary table

You need to add prefix '#' for local temporary tables and '##' for global temporary tables. These objects will be created in the TempDB system database. Temporary tables are stored in the TempDB database with a unique object name and created on a connection or session level. These tables are visible and accessible within the session only. We can drop the temporary table using the DROP TABLE command or the temporary table will be dropped automatically when the session disconnects. In addition, SQL Server maintains statistics for temporary tables.

Creation and deletion of temporary tables requires access and modification of the TempDB allocation map pages (IAM, SGAM and PES). Temporary tables are cached in buffer pool as compared to permanent disk based tables. With temporary table caching, SQL Server will not drop the table physically, but will truncate it and maintain the IAM and data pages. When the table is created later, SQL Server will reuse the earlier pages, which reduces the number of page modifications required.

Temporary tables can be created in two ways:

  • CREATE TABLE
  • SELECT INTO

Generally speaking, the performance of both options are similar for a small amount of data. Data is inserted quickly in the temporary table, but if the amount of data is large then we can experience poor query performance. This usually happens with temporary tables when we insert a large number of rows.

SQL Server INSERT INTO Example

SET STATISTICS IO ON
SET STATISTICS TIME ON

DECLARE @ids NVARCHAR(MAX) = N'115676,115678,115679,115680,115681'
DECLARE @input_xml XML

CREATE TABLE #list_to_table(Id BIGINT)

SELECT @input_xml = Cast('<root><x>'+ Replace(@ids, ',', '</x><x>')+ '</x></root>' AS XML)    
 
INSERT INTO #list_to_table
SELECT f.x.value('.', 'BIGINT') AS Id
FROM @input_xml.nodes('/root/x') f(x)

DROP TABLE #list_to_table

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 2 ms.
Table '#list_to_table____000000000015'. Scan count 0, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. (5 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

When there is a large amount of comma separated values as the input parameter, the code can be delayed for a few seconds while inserting the data into the temp table.

SQL Server SELECT INTO Example

As alternate solution we can use is the SELECT...INTO command which generally performs better than the INSERT...SELECT command.

SET STATISTICS IO ON
SET STATISTICS TIME ON

DECLARE @ids NVARCHAR(MAX) = N'115676,115678,115679,115680,115681'
DECLARE @input_xml XML

SELECT @input_xml = Cast('<root><x>'+ Replace(@ids, ',', '</x><x>')+ '</x></root>' AS XML)

SELECT f.x.value('.', 'BIGINT') AS Id
INTO #list_to_table
FROM @input_xml.nodes('/root/x') f(x)

DROP TABLE #list_to_table

SQL Server parse and compile time:

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

(5 row(s) affected)

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

SQL Server INSERT...SELECT vs. SELECT...INTO with a Larger Data Set

The above examples are very small, so let's use the example below to see how this performs with a large amount of data. This test will have over a million rows.

SQL Server INSERT... SELECT

CREATE TABLE #EMPLOYEEEEE
    (Emp_id BIGINT, 
     f_name NVARCHAR(100), 
     l_name NVARCHAR(100),
     Email NVARCHAR(100),
     is_active BIT)
  
INSERT INTO #EMPLOYEE
SELECT Emp_id, f_name, l_name, Email, is_active 
FROM employee

SQL Server parse and compile time:

SQL Server Execution Times:
   CPU time = 1407 ms, elapsed time = 3241 ms.

(1727264 row(s) affected)

SQL Server SELECT...INTO

SELECT Emp_id, f_name, l_name, Email, is_activee
INTO #EMPLOYEE
FROM employee

SQL Server parse and compile time:

SQL Server Execution Times:
   CPU time = 1499 ms,  elapsed time = 489 ms.

(1727264 row(s) affected)

Results and Analysis

As we can see the SELECT...INTO was considerably faster 489ms compared to 3241ms.

The INSERT...INTO command will reuse data pages which are created in cache for insert/update/delete operations. It will also truncate the table when it is dropped. The SELECT...INTO command will create new pages for table creation similar to regular tables and will physically remove them when the temporary table is dropped.

Next Steps
  • Keep these performance considerations in mind as you develop your code or have to tune code that is not performing optimally.
  • Test additional scenarios in your environment to determine which coding technique will perform the best.
  • Always have an open mind to various coding techniques, test each technique and let the performance dictate the answer.


Last Update:






About the author
MSSQLTips author Jignesh Raiyani Jignesh Raiyani is a SQL Server Developer/DBA with experience in design and development of T-SQL procedures and query performance tuning.

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    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, January 06, 2017 - 12:25:07 AM - Jignesh Raiyani Back To Top

Hello John,

 

CPU Time = Timing of query processing by CPU.

Elapsed Time = Timing of Query start to completion.

 

CPU time is increased when Query uses a single core. if its uses number of cores than timing will be reduced.

So,I have found that locks are placed for little bit time while CPU load increased.

 


Thursday, January 05, 2017 - 2:58:08 PM - John Back To Top

 Jignesh, I have a question on the timing results for the SELECT...INTO test with a large data set.  Your example shows CPU time at 1499 ms and elapsed time at 489 ms.  How can CPU time exceed elapsed time?  Parallelization?  I noticed also that the CPU time was longer than the INSERT INTO example test of the same large data set.

Thanks!

 


Thursday, January 05, 2017 - 1:17:51 AM - Patel Bhavesh Back To Top

 

Jignesh good job as per approach performance bases..


Wednesday, January 04, 2017 - 11:39:52 PM - Shreyash Back To Top

Really helpful Jignesh.

 

Needless to say that, the help regarding my performance issue that I was looking for is finally I have.

 

Thanks..nice job.!


Wednesday, January 04, 2017 - 8:29:29 AM - Ankit Patel Back To Top

 

Good Job...

Congo jignesh....


Wednesday, January 04, 2017 - 8:18:09 AM - Hardik Prajapati Back To Top

 It's really needfull..Thanks Jignesh Raiyani

 


Wednesday, January 04, 2017 - 2:06:04 AM - Ranga Back To Top

Congrats Jignesh!


Learn more about SQL Server tools