Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Welcome to my blog. I work as Database lead at Synaptic Digital. Hope you find some interesting stuff here.
Browse by Tags · View All
BRH 17
SQL Server 15
#SQL Server 11
#BI 10
#TSQL 8
TSQL 8
BI 7
SSRS 6
#SQLServer 6
SSRS 2008R2 5

Archive · View All
January 2011 6
December 2010 5
September 2012 4
May 2012 4
March 2011 4
November 2012 2
October 2012 2
January 2012 2
February 2011 2
November 2010 2

SELECT INTO is faster than CREATE & INSERT

Apr 17 2010 11:46PM by Chintak Chhapia   

Before we directly jump to the topic, I would like to give a basic introduction of SELECT..INTO statement.

As, we know that, select into statement can be used to create new table with same table structure and to insert a base table rows in new table. We need to consider following things before using this.

1.  Column constraints are not gets created in new table
2.  If a selected column is computed then corresponding column in new table will not be computed column. The values in new columns are the values that were computed at the time SELECT…INTO was executed.
3.  SELECT…INTO statement does not cause blocking for duration of select statement.
4.  Generally, SELECT…INTO is measurably faster than separate create and insert statements. I will try to demonstrate the same in this post.

Following steps are performed. I have run this test on SQL 2005 sp3.

Step-1 Create Base table and populate default data.

IF object_id('IntoTestBaseTable')  IS NOT NULL
DROP TABLE IntoTestBaseTable;
GO
CREATE TABLE IntoTestBaseTable ( c1 INT , c2 NVARCHAR (MAX) );
GO
DECLARE @i AS INT;
SELECT @i = 2500000;
WHILE (@i > 0)
BEGIN
      INSERT INTO IntoTestBaseTable
      SELECT @i, CAST (@i AS NVARCHAR (MAX)); 
      SELECT @i = @i - 1;
END
GO

Step-2 Clear cache and buffers

NOTE:- Do not run this on live/production environments.

DBCC FreeProcCache
DBCC DropCleanBuffers
GO

 Step-3 Measure the time required for SELECT..INTO

SET STATISTICS TIME ON;
GO
IF object_id('tempdb..#Tmp1IntoTestBaseTable') IS NOT NULL
    DROP TABLE #Tmp1IntoTestBaseTable;
GO
SELECT *
INTO   #Tmp1IntoTestBaseTable
FROM   IntoTestBaseTable;
 
--In my case, I get following details
--SQL Server Execution Times:
--CPU time = 4204 ms,  elapsed time = 4526 ms.

 Step-4 Create a second blank temp table. I have created blank table using SELECT..INTO statement. We can also use CREATE TABLE statement for this.

IF object_id('tempdb..#temp2IntoTestBaseTable') IS NOT NULL
   DROP TABLE #temp2IntoTestBaseTable;
GO
SELECT *
INTO   #temp2IntoTestBaseTable
FROM   IntoTestBaseTable
WHERE  0 = 1

Step-5 Clear cache and buffers
NOTE:- Do not run this on live/production environments.

DBCC FreeProcCache
DBCC DropCleanBuffers
GO

Step-6 Measure the time required for INSERT INTO

INSERT INTO #temp2IntoTestBaseTable
SELECT *
FROM   IntoTestBaseTable;
--In my case, I get following details
--SQL Server Execution Times:
--   CPU time = 15125 ms,  elapsed time = 52301 ms.

Step-7 DROP temp tables

 IF object_id('tempdb..#temp2IntoTestBaseTable') IS NOT NULL
    DROP TABLE #temp2IntoTestBaseTable;
GO
 
IF object_id('tempdb..#Tmp1IntoTestBaseTable') IS NOT NULL
    DROP TABLE #Tmp1IntoTestBaseTable;
GO

Now, we can clearly see the execution time difference between SELECT INTO and CREATE & INSERT. The reason for this is the log records get created in step is different. SELECT..INTO performs as a bulk insert and its minimally logged operator.  As shown above we are inserting records into TEMPDB and recovery mode of TEMPDB is always SIMPLE, this becomes minimally logged operation.

Even if we do not use temporary table and use the physical table in the same database, SELECT..INTO is still faster than separate CREATE & INSERT. This is due to the log records are generated for each statement. Following steps demonstrates the same.

NOTE:-I have changed the recovery model of database to Balk-logged for this demo.

Step-1 Clear cache and buffers
NOTE:- Do not run this on live/production environments.

DBCC FreeProcCache
DBCC DropCleanBuffers
GO

Step-2 Measure the time required for SELECT..INTO

 IF object_id('Tmp1IntoTestBaseTable') IS NOT NULL
    DROP TABLE Tmp1IntoTestBaseTable;
GO
SELECT *
INTO   Tmp1IntoTestBaseTable
FROM   IntoTestBaseTable;
--SQL Server Execution Times:
--CPU time = 4328 ms, elapsed time = 12770 ms.

Step-3 Look at the number of log records generated for test

I have taken this script form following post:

http://sqlblog.com/blogs/tibor_karaszi/archive/2010/03/22/are-log-records-removed-from-ldf-file-for-rollbacks.aspx

SELECT count(*) FROM fn_dblog(NULL, NULL) 
WHERE [Transaction ID] = (SELECT TOP(1) [Transaction ID] 
                          FROM fn_dblog(NULL, NULL)
                          WHERE AllocUnitName LIKE '%Tmp1IntoTestBaseTable%'
                          ORDER BY [Current LSN] DESC)

--13496 log records generated

Step-4 Clear cache and buffers
NOTE:- Do not run this on live/production environments.

DBCC FreeProcCache
DBCC DropCleanBuffers
GO

Step-5 Create a second blank physical table. I have created blank table using SELECT..INTO statement. We can also use CREATE TABLE statement for this.

IF object_id('temp2IntoTestBaseTable') IS NOT NULL
    DROP TABLE temp2IntoTestBaseTable;
GO
SELECT *
INTO   temp2IntoTestBaseTable
FROM   IntoTestBaseTable
WHERE  1 = 0

Step-6 Measure the time required for INSERT INTO

INSERT INTO temp2IntoTestBaseTable
SELECT *
FROM   IntoTestBaseTable
--SQL Server Execution Times:
--CPU time = 15187 ms,  elapsed time = 27503 ms.

Step-7 Look at the number of log records generated for test

SELECT count(*) FROM fn_dblog(NULL, NULL) 

WHERE [Transaction ID] = (SELECT TOP(1) [Transaction ID] 
                          FROM fn_dblog(NULL, NULL)
                          WHERE AllocUnitName LIKE '%temp2IntoTestBaseTable%'
                          ORDER BY [Current LSN] DESC)
-- 2531362 log records generated

As we can clearly see that INSERT generates lots more log records then SELECT..INTO statemnet.

 So, After looking at these two examples we can conclude that SELECT.. INTO is faster than CREATE & INSERT. 

Tags: 


Chintak Chhapia
40 · 5% · 1470
1
 
0
Lifesaver
 
 
0
Learned
 
0
Incorrect



Submit

4  Comments  

  • Hi Chintak

    Can you please help me to understand:

    According to Microsoft, the SELECT…INTO statement is Minimally Logged operation ONLY when a DB Recovery Mode is set to Simple or bulk-logged. http://msdn.microsoft.com/en-us/library/ms188029.aspx

    SO if my DB is in Full recovery mode, the SELECT…INTO statement will be fully logged, right?

    IF so, how it still Faster than INSERT INTO?

    I will appreciate if you will explain it.

    Thanks Dan http://madebysql.blogspot.com/

    commented on Aug 21 2010 12:04PM
    DanP
    3016 · 0% · 2
  • Hi Den,

    You are right. Probably I might have created new database and not taken a full backup before runing the test and thats why even after setting recovery mode to FULL, we are getting this result.

    Thanks for pointing this. I will update the post accordingly.

    Thanks Again, Chintak

    commented on Aug 23 2010 8:45AM
    Chintak Chhapia
    40 · 5% · 1470
  • HI Chintak,

    Its too late to look into your post.

    And if we use SELECT * INTO NEW TABLE FROM OLD TABLE ,table structure will be same as old table but constrains will not be created in new table right.

    commented on Jan 10 2013 2:23AM
    Bala Krishna
    83 · 2% · 678
  • Yes, constraints as well as indexes will not be available in new table. For more information have a look at limits and restrictions here. Also, rowversions gets copied.

    commented on Jan 10 2013 11:58AM
    Chintak Chhapia
    40 · 5% · 1470

Your Comment


Sign Up or Login to post a comment.

"SELECT INTO is faster than CREATE & INSERT" rated 5 out of 5 by 1 readers
SELECT INTO is faster than CREATE & INSERT , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]