SQLite Forum

For our project we have to work with given tables with several hundred columns and up to a hundred indexes. In comparison to other database systems, the execution speed is unusually slower.

After investigation I was able to break it down: The internal way to get the schema seems to take up to one additional second for each index in the corresponding table. This happens also, if there are no records in that table.

As a consequence this slows down the CommandBuilder and also the dataadapter.fill, if it is used with "MissingSchemaAction.AddWithKey"-Option.

(Behaviour has been tested and reproduced with "SQLite-netFx46-static-binary-x64-2015-1.0.112.0" and also with the actual NUGet pakage "System.Data.SQLite.Core V1.0.113.7".)


Sample code:

<pre>
   Public Sub Test()
      '--------------------------------------------------------------------------------------------
      Const DBFILE As String = "TestDatabase.sdb"
      Const TABLENAME As String = "TestTable"
      Dim iSw As New Stopwatch
      Dim iSb As New System.Text.StringBuilder
      '--------------------------------------------------------------------------------------------

      Using iSQLCon As New SQLiteConnection($"Data Source={DBFILE};Version=3;")
         '·························································································
         Dim ExecuteNonQuery = Sub(SQLText As String)
                                  Debug.Print($"Executing {SQLText} ...")
                                  Using iSQLCmd As New SQLite.SQLiteCommand(SQLText, iSQLCon)
                                     iSQLCmd.ExecuteNonQuery()
                                  End Using
                               End Sub
         '·························································································
         iSQLCon.Open()
         '-----------------------------------------[Creating a new datatable with a lot of columns]
         ExecuteNonQuery($"DROP TABLE IF EXISTS {TABLENAME};")
         iSb.Append($"CREATE TABLE {TABLENAME} (")
         iSb.Append(" ColumnPK Integer PRIMARY KEY")

         For iCtr = 1 To 900
            iSb.Append($", Column{iCtr:000} Integer")
         Next

         iSb.Append("); ")
         ExecuteNonQuery(iSb.ToString)

         '--------------------------------------------------------------[Create a bunch of indexes]
         For iCtr = 10 To 900 Step 20
            ExecuteNonQuery($"CREATE INDEX {TABLENAME}Index{iCtr:000} ON TestTable (Column{iCtr:000});")
         Next

         '------------------------------------------------------------------[Using command builder]
         Debug.Print("Start ...")
         iSw.Start()

         Using iSQLCmd As New SQLite.SQLiteCommand($"SELECT * FROM {TABLENAME};", iSQLCon),
            iDA As New SQLite.SQLiteDataAdapter,
            iCB As New SQLite.SQLiteCommandBuilder(iDA)

            iDA.SelectCommand = iSQLCmd
            Debug.Print($"Upto DataAdapter Select: {(iSw.ElapsedMilliseconds \ 100) / 10}s")
            iDA.InsertCommand = iCB.GetInsertCommand
            Debug.Print($"Upto DataAdapter Insert: {(iSw.ElapsedMilliseconds \ 100) / 10}s")
            iDA.UpdateCommand = iCB.GetUpdateCommand
            Debug.Print($"Upto DataAdapter Update: {(iSw.ElapsedMilliseconds \ 100) / 10}s")
            iDA.DeleteCommand = iCB.GetDeleteCommand
            Debug.Print($"Upto DataAdapter Delete: {(iSw.ElapsedMilliseconds \ 100) / 10}s")

            'Using iDT As New DataTable
            '   iDA.MissingSchemaAction = MissingSchemaAction.AddWithKey
            '   iDT.BeginLoadData()
            '   iDA.Fill(iDT)
            '   iDT.EndLoadData()
            'End Using
         End Using

         iSw.Stop()
         Debug.Print($"Elapsed: {(iSw.ElapsedMilliseconds \ 100) / 10}s")

         iSQLCon.Close()
      End Using
      '--------------------------------------------------------------------------------------------
   End Sub


(Essential) output:
Start ...
Upto DataAdapter Select: 0s
Upto DataAdapter Insert: 43,2s
Upto DataAdapter Update: 43,3s
Upto DataAdapter Delete: 43,4s
Elapsed: 43,4s
</pre>



Time get's wasted on first call of CommandBuilder.GetInsert/Update/DeleteCommand.


Did I miss something or is there any way around this performance pit?


Regs,
Ted