" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
 - Mahatma Gandhi

  1. Home
  2. .Net Development Tips
  3. Gearing up performance in ADO.NET

Gearing up performance in ADO.NET

While working in a project around a year back, I was facing trouble while downloading data from a SQL server to sqlite database. The SQL server DB contained  millions of records (CAd/CAM refernece data) in some table that I needed to download to the SQLite DB.In fact the records were inserted to the SQL server from some other SQLite DB (using SQLBulkCopy, which was amazingly fast).
 
But the sad point was that the SQlite provider didn’t offer any functionalities to bulk copy large amount of data into SQlite DB. I tweaked the code many ways, but everything was in vain . Copying all data from SQL server to Sqlite tables was taking around 9-10 minutes.
 
Finally, I adopted some simple mechanisms and I was in the 5th gear . Copying same number of records took just 1-2 minutes .
 
Here is the code snippet for your reference :

(though it uses SQLite, you can also try the same for anyother db)

[Each time I was retrieving a fixed amount of record from SQL server and using this code]

using (SQLiteTransaction dbTransaction = dbConnection.BeginTransaction())
       {
        using (System.Data.SQLite.SQLiteDataAdapter dbAdapter = new SQLiteDataAdapter())
         {
         using (SQLiteCommand dbCommand = dbConnection.CreateCommand())
          {
          dbCommand.Transaction = dbTransaction;
          dbCommand.CommandText = "Select * from " + sqliteTableName + " Where 1 = 2";
          dbAdapter.SelectCommand = dbCommand;
           using (SQLiteCommandBuilder cb = new SQLiteCommandBuilder())
             {
              //set the data adapter property
             cb.DataAdapter = dbAdapter;
             using (dbAdapter.InsertCommand = (SQLiteCommand)((ICloneable)cb.GetInsertCommand()).Clone())
              {
              // unregister the 
                cb.DataAdapter = null;
                foreach (DataRow dbRow in dbTable.Rows)
                 {
                   dbRow.SetAdded();
                 }
                 dbAdapter.Update(dbTable);
                 dbTransaction.Commit();
                }
              }
            }
          }
       if (dbConnection.State != ConnectionState.Closed)
           {
            dbConnection.Close();
           }
   }
The code snippet may not be that much of interset to you, but here are the key points to rememeber : 
  1. Always use USING clause during db operation
  2. Use TRANSACTION object while you make large amount of data transfer
  3. Try to save the insert / update command of command builder and reuse it (as using CommandBuilder may hinder the performance)
 And I’m pretty sure, you’ll see the difference .
Rabinarayan Biswal , Dot Net Developer, Mindfire Solutions.
Related Tags: ADO.NET, BulkCopy, CommandBuilder, NET, Performance, Sqlite
Author : Rabinarayan Biswal

Leave a Reply