System.Data.SQLite

I have a DB with several tables, one of which has 90,000 entries.  I use VB.NET's datatables to manipulate it.  I ran a test where I inserted 100 new rows using datatable.newrow, setting the values, then datatable.rows.add.  When I do the update to commit them, on JET it takes 15 ticks (using Environment.Ticks) and on SQLite it takes 5366 ticks.  To make sure that JET wasn't doing some of the work in the insert, I wrapped the timing around the entire Open/Add/Close, and on JET it takes 2168 ticks and on SQLite it takes 7924.

I'll readily admit to not being a DB Guru, so I might be doing something stupid.  The large table defintion is:

CREATE TABLE Movies ( ID integer primary key, MovieYear integer,  stars single, genres string, series integer, strings integer, length smallint, type smallint, updated integer, flags integer ) ;

Any idea why the large difference?

Did you use a transaction?

 Not unless .NET does it for me in the background.  I do the following (leaving out the other 6 small tables):

        Dim objCB As SQLite.SQLiteCommandBuilder
        SQLconnect.ConnectionString = "Data Source=" & "c:\downloads\test.db3" & ";"
        SQLconnect.Open()

        daMovies = New SQLite.SQLiteDataAdapter("SELECT * FROM Movies", "Data Source=" & DBName)

        ds = New DataSet
        daMovies.Fill(ds, "Movies")
        objCB = New SQLite.SQLiteCommandBuilder(daMovies)
        Dim objColMovies() As DataColumn = {ds.Tables("Movies").Columns.Item("ID")}
        ds.Tables("Movies").PrimaryKey() = objColMovies

        tblMovies = ds.Tables("Movies")

        For i = 0 To 100
            row = tblMovies.NewRow
            row("ID") = i

            ' set the other fields
            tblMovies.Rows.Add(row)
        Next
        daMovies.Update(ds, "Movies")

        SQLconnect.Close()
 

 Is there any other information I could gather that would help understand this?

DanHi:

 Is there any other information I could gather that would help understand this?

Yeah, for CRUD operations on multiple rows it's doggy squat slow if you don't use transactions.

 Again, I apologize for not really understanding this stuff all this well.  What I did was wrap the update in a BEGIN; and then a COMMIT; and it actually slows things down.  Is there a different way I should be doing this?

Look at:

 http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx

 for example code. Just change

SqlCommand 

to 

SQLiteCommand  

and 

SqlTransaction 

to

SQLiteTransaction  

etc.

 I did the following:

        Dim SQLcommand As SQLite.SQLiteCommand
        Dim transaction As SQLite.SQLiteTransaction

        SQLcommand = SQLconnect.CreateCommand
        transaction = SQLconnect.BeginTransaction(IsolationLevel.Serializable, True)
        SQLcommand.Connection = SQLconnect
        SQLcommand.Transaction = transaction

        daMovies.Update(ds, "Movies")

        transaction.Commit()
        SQLconnect.Close()
 

And it's still slower than not having a transaction.

You don't need to bother with a data adapter to insert rows, use ExecuteNonQuery on the command.

Here is a quote from the System.Data.SQLite help file and I can assure you it's correct information. 

============= quote =================

So lets rewrite that code slightly:

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

Now this is a blazing fast insert for any database engine, not just SQLite. The SQL statement is prepared one time -- on the first call to ExecuteNonQuery(). Once prepared, it never needs re-evaluating. Furthermore, we're allocating no memory in the loop and doing a very minimal number of interop transitions. Surround the entire thing with a transaction, and the performance of this insert is so far and away faster than the original that it merits a hands-on-the-hips pirate-like laugh.

=========== end quote ===================