Fastest Bulk Inserts

Fastest universal way to insert data using standard ADO.NET constructs Now that the slow stuff is out of the way, lets talk about some hardcore bulk loading. Aside from SqlBulkCopy and specialized constructs involving ISAM or custom bulk insert classes from other providers, there is simply no beating the raw power of ExecuteNonQuery() on a parameterized INSERT statement.


 
internal static void FastInsertMany(DbConnection cnn)
{

  using (DbTransaction dbTrans = cnn.BeginTransaction())

  {

    using (DbCommand cmd = cnn.CreateCommand())

    {

      cmd.CommandText = "INSERT INTO TestCase(MyValue) VALUES(?)";

      DbParameter Field1 = cmd.CreateParameter();

      cmd.Parameters.Add(Field1);

      for (int n = 0; n < 100000; n++)

      {

        Field1.Value = n + 100000;

        cmd.ExecuteNonQuery();

      }

    }

    dbTrans.Commit();

  }

}

Simple, elegant, clean.  100,000 inserts on my machine in 1.4 seconds.  Yes, that’s 10 times more inserts than the DataAdapter/DbCommandBuilder/DataTable method, and at very nearly the same speed as example 1!

Do not, under any circumstances, bulk insert data by building your own command text over and over and over again.  That is the surest way to drag performance into the dirt.  Build your CommandText one time, build your parameters one time, and set their values over and over and over instead.  SQLite runs much faster, SQL Server runs much faster, and every database known to mankind will run much faster with a prepared, parameterized insert statement.

source: Fastest Bulk Inserts

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s