SQLite Insert very slow?

user_357037 0
Viewed 60423

I recently read about SQLite and thought I would give it a try. When I insert one record it performs okay. But when I insert one hundred it takes five seconds, and as the record count increases so does the time. What could be wrong? I am using the SQLite Wrapper (system.data.SQlite):

dbcon = new SQLiteConnection(connectionString);
dbcon.Open();

//---INSIDE LOOP

 SQLiteCommand sqlComm = new SQLiteCommand(sqlQuery, dbcon);

 nRowUpdatedCount = sqlComm.ExecuteNonQuery(); 

//---END LOOP

dbcon.close();
system.data.sqlite sqlite c#
4 Answers

See "Optimizing SQL Queries" in the ADO.NET help file SQLite.NET.chm. Code from that page:

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();
}
user_5840220
Related