Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Performance Improvement for SQL Server SELECT... INTO T-SQL Statement


By:   |   Last Updated: 2017-01-27   |   Comments (5)   |   Related Tips: More > Import and Export

Problem

The SELECT…INTO T-SQL statement is used to copy data from one table to another by creating a new table in the default database filegroup of the destination database and inserting the data from the source table into the created one. With a large amount of data, the SELECT…INTO statement runs slowly as it runs under a single thread using a serial plan to copy the requested data. Are there any enhancements with the SELECT…INTO T-SQL statement in SQL Server that makes the statement run faster?

Solution

SQL Server 2014 and later comes with a valuable enhancement for the SELECT…INTO T-SQL statement, where it now runs with multiple threads using a parallel plan. The parallel execution for SELECT…INTO makes it faster compared with the old serial execution in previous SQL Server versions.

In this tip, we will demonstrate the results. We will try to run a standard SELECT…INTO command to copy data between two tables in the same database twice; the first time using SQL Server 2012 compatibility level 110 and then using SQL Server 2014 compatibility level 120.

Let’s first change the compatibility level of the MSSQLTipsDemo test database to 110 to force it to run as the SQL Server 2012 version:

ALTER DATABASE MSSQLTipsDemo SET COMPATIBILITY_LEVEL = 110;
GO

Now we will run the SELECT…INTO statement. We will also set STATISTICS TIME ON, so we can see how long it took to complete.

USE MSSQLTipsDemo
GO
SET STATISTICS TIME ON
SELECT * INTO [Production].[TransactionHistory2] FROM [Production].[TransactionHistory]
SET STATISTICS TIME OFF

The below execution plan shows us that the SELECT…INTO T-SQL statement executed using a serial plan:

execution plan shows us that the SELECT…INTO T-SQL statement executed using a serial plan

In our example, the statement took 132 ms to execute the query:

In our example, the statement took 132 ms to execute the query

Let’s change the compatibility level of the MSSQLTipsDemo database to 120 to force the code to run as the SQL Server 2014 version:

ALTER DATABASE MSSQLTipsDemo SET COMPATIBILITY_LEVEL = 120;
GO

Drop the table we created above, so we can run the statement again:

USE MSSQLTipsDemo
GO
DROP TABLE TransactionHistory2

And again run the following standard SELECT…INTO command after enabling STATISTICS TIME:

USE MSSQLTipsDemo
GO
SET STATISTICS TIME ON
SELECT * INTO [Production].[TransactionHistory2] FROM [Production].[TransactionHistory]
SET STATISTICS TIME OFF

The generated execution plan shows us that the SELECT…INTO statement executed using SQL Server 2014 compatibility is using a parallel plan as shown below:

execution plan shows us that the SELECT…INTO statement executed now under SQL Server 2014 is using parallel plan

In our example, the statement took 87 ms to complete:

In our example, the statement took 87ms to complete

It is clear from our example that the SELECT…INTO T-SQL statement ran ~1.5X faster using a SQL Server 2014 compatibility level using a parallel plan rather than running with a serial plan.

Next Steps


Last Updated: 2017-01-27


get scripts

next tip button



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.

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.



    



Wednesday, February 01, 2017 - 12:34:19 PM - Ahmad Yaseen Back To Top

 Hi Ankit,

 

Thank you for your comment.

Please note that the CPU time increase is normal behaviour due to the parallel plan execution.

Best Regards,

Ahmad

 


Wednesday, February 01, 2017 - 12:25:50 AM - Ankit Patel Back To Top

 

Hi  Ahmad Yaseen.

 

but your CPU time Incresed ?

in first statment  CPU Time is  78 ms, and second statment is  171 ms.

 


Tuesday, January 31, 2017 - 8:28:19 AM - Ahmad Yaseen Back To Top

Thank you Jeff for your input here, Please find below my answers:

1.  How many rows are in your Production.TransactionHistory table? 113,443

2. Does the table look like the following? Yes

3. Which Recovery Model was your database in? Full

 

Best Regards,

Ahmad

 


Tuesday, January 31, 2017 - 1:16:42 AM - Jeff Moden Back To Top

Nice tip.  Thanks for taking the time to post it.  I have 3 questions, please.

1.  How many rows are in your Production.TransactionHistory table? 113,443?

2. Does the table look like the following?

CREATE TABLE [Production].[TransactionHistory](
 [TransactionID] [int] IDENTITY(100000,1) NOT NULL,
 [ProductID] [int] NOT NULL,
 [ReferenceOrderID] [int] NOT NULL,
 [ReferenceOrderLineID] [int] NOT NULL,
 [TransactionDate] [datetime] NOT NULL,
 [TransactionType] [nchar](1) NOT NULL,
 [Quantity] [int] NOT NULL,
 [ActualCost] [money] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_TransactionHistory_TransactionID] PRIMARY KEY CLUSTERED
(
 [TransactionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
;

3. Which Recovery Model was your database in?

 

 


Friday, January 27, 2017 - 7:55:40 AM - Hiren Patel Back To Top

 Thank you for the great Tip!

 


Learn more about SQL Server tools