SQLBulkCopy versus standard INSERT statements

Part of my current project involves several batch processes that are run after normal working hours.  Most of these jobs are doing either one of two things: importing data from a text file or exporting data to a text file.  (Since my project is replacing an existing set of applications, we were required to maintain compatibility with the other systems that are sending or receiving data to or from us.) Our data is stored in a typical SQL Server 2005 database.

net_log_h_3We decided to use .Net based console applications that would be kicked off by my client’s ControlM.  For imports, ControlM moves the file from whatever system it is coming from, to our server and then executes our console app.  For exports, it does the opposite.

Anyway, most of our console apps finish within a few minutes but we had one that was taking over twenty-five minutes to complete.  This was within our acceptable range and we were testing on servers much less powerful than our production hardware, so we didn’t investigate how to make it quicker.

Fast forward a few months and we’ve moved our application to production.  Our batch jobs are all running, but we noticed that the job that was taking twenty-five minutes was now taking forty minutes typically, and a couple of days it took almost an hour.  At this point, we decided to look at the code again.

I had coded this app to load data basically using SQL INSERT statements for each row in the table.  Unlike the other batch applications which were loading hundreds of rows, this app was loading hundreds of thousands of rows.  Luckily, searching for a quicker solution turned up this article ( http://www.4guysfromrolla.com/articles/102109-1.aspx ) describing SQLBulkCopy, which was something that I had never seen before.

ADO.NET’s SQL Bulk Copy class gives us a way to much more efficiently load lots of data into our SQL database.  So, I reworked our app to use bulk copy and boy, did it work as advertised.  Our load times went from 40-60 minutes down to 40 seconds!  In fact, the first time I ran it, I thought something wasn’t working since it finished so quickly.

Print | posted @ Friday, November 27, 2009 11:00 PM