Gokul's Blog


Leave a comment

Caching


Advertisements


Leave a comment

Executing 100000 DB Inserts (Sqlite)

Open the csv file via System.data.OleDb
Open the sqlite database via System.Data.SQLite
Begin a transaction on the sqlite connection
loop over the rows in the csv, inserting them into the sqlite tables
Commit the transaction

Which code below is faster

Code 1:

     using (SQLiteCommand mycommand = new SQLiteCommand(myconnection))
      {
        int n;

        for (n = 0; n < 100000; n ++)
        {
          mycommand.CommandText = String.Format("INSERT INTO [MyTable] ([MyId]) VALUES({0})", n + 1);
          mycommand.ExecuteNonQuery();
        }
      }

Code 2:

 using (SQLiteTransaction mytransaction = myconnection.BeginTransaction())
      {
        using (SQLiteCommand mycommand = new SQLiteCommand(myconnection))
        {
          SQLiteParameter myparam = new SQLiteParameter();
          int n;

          mycommand.CommandText = "INSERT INTO [MyTable] ([MyId]) VALUES(?)";
          mycommand.Parameters.Add(myparam);

          for (n = 0; n < 100000; n ++)
          {
            myparam.Value = n + 1;
            mycommand.ExecuteNonQuery();
          }
        }
        mytransaction.Commit();
      }