Lesson Learned #166: How to reduce the time inserting 10 millions of rows

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Today, we had a very interesting service request that our customer requested to improve the process for inserting 10 millions of rows. 

In this type of situation, we need to think about the the time spent in networking roundtrips.

 

In order to explain this I developed three different Powershell Scripts that you could find out in this GitHub repository

 

  • InsertThis, basically is a Powershell command to insert the data and measure the time inserting every single row, using an INSERT command. 
  • InsertStore, basically is a Powershell command to insert the data and measure the time inserting every row, calling a parametrized query to reduce the amount of bytes sending to database.
  • InsertBulk, basically is a Powershell command to insert the data using batches and BulkCopy technology.

 

In all the tests done, InsertBulk was the process that much faster inserted the data. 

 

Enjoy!

 

 

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.