Welcome to EMC Consulting Blogs Sign in | Join | Help

Third Abnormal Form

Resolution to SQL Server problems. From the front line.

So you may have many different ways of Inserting Data - But which is the fastest?

After getting 100,000 rows of Data using the 4 main flavours of Insert Statement I attempted to determine which method was the fastest.. To ensure that the results were not skewed by database re-sizing actions or cached data. I ran each query multiple times and used the following command to clear the CACHE

CHECKPOINT

DBCC FREEPROCCACHE -- to clear the procedure cache

DBCC DROPCLEANBUFFERS -- to test queries with a cold buffer cache

I said 'attempted' because I found out that with Method 3 Insert with Select Values Clause and using 100,000 rows on my laptop I could not get the query to complete - It just 'hung' (I gave up after 40 minutes) I assume because it needed to materialise the 100,000 rows in memory before it could attempt to Insert them..

I decided that to make it a test whereby at least I got a result - I therefore limited the number of rows to 1000, this also mean't that it was within the limits of the Insert Statement with Row Constructor method. I put in a Statistics statements and executed the code a number of times.

The average results (10 runs) were as follows:

Method 1 Classic Insert:

 -- 274 ms

Method 2 Insert with row constructor:

-- 1413 ms

Method 3 Insert with Select Values Clause:

-- 1540 ms

Method 4 Merge Statement:

-- 1574 ms

As can be seen the fastest method (reduction of at least  80%) is the original Classic Insert Statement this was really surprisng to me, I wondered why - One obvious change is the locking involved. The Classic Insert Statement was taking Exclusive (X) on Index Key Locks and Intent Exclusive (IX) Locks on Pages and Tables while the other types seem to be taking Schema Stability  (Sch-S) Locks on Table and Metadata and Shared (S) Database Locks. Probably because they ALL have to materialise the Insert data in some form before they marshall it to the Insert statement... 

Conclusions

1) Many ways to Insert Data in SQL Server

2) Easiest to write probably Insert with Row Constructor - Though this is limited to 1000 rows per batch

3) Best for maintainability and control is the MERGE statement

4) Fastest method is Classic Insert - and Insert with Select Values Clause will probably never finish with a very large number of rows 

 

See also

 http://consultingblogs.emc.com/paulmcmillan/archive/2011/07/06/how-many-different-ways-can-you-write-a-sql-insert-statement-1-2-3-6.aspx

http://consultingblogs.emc.com/paulmcmillan/archive/2011/07/06/so-now-you-can-write-an-insert-statement-6-different-ways-why-do-you-need-to-know-this-and-why-is-merge-the-best.aspx

http://consultingblogs.emc.com/paulmcmillan/archive/2011/07/06/a-gotcha-with-the-insert-statement-row-constructor.aspx

 


Published Wednesday, July 06, 2011 2:53 PM by Paul.McMillan

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Vizzini said:

Can you add a sample code snippet illustrate each of the approaches?  Not all of your readers will know exactly what "Insert with row constructor" means, for example.

July 11, 2011 9:49 PM
 

Paul.McMillan said:

July 12, 2011 2:59 PM
 

Sanaz said:

I wanna to thank to Vizzini for his (her) request and then to Paul.

July 13, 2011 10:26 AM

Leave a Comment

(required) 
(optional)
(required) 
Submit
Powered by Community Server (Personal Edition), by Telligent Systems