SQLite Forum

SQLite .NET: CommandBuilder performs really bad on tables with a lot of indexes
Login
I was curious, so I decided to run the tests. I get pretty much identical results and I am on a VERY low end Win 10 laptop.

I took your code and put into a VB.Net Console app using .NET Core. 
Here are my results from two runs.

```
C:\Users\alcal\source\repos\dbtest\bin\debug\netcoreapp3.1> ./dbtest
Starting Program!
Database : TestDatabase.sdb
Table Name : TestTable
Drop Table if Exists
Create Table
Create Columns
Create a bunch of indexes
Start ...
Upto DataAdapter Select: 0s
Upto DataAdapter Insert: 49.3s
Upto DataAdapter Update: 49.5s
Upto DataAdapter Delete: 49.6s
Elapsed: 49.6s
Close Connection
PS C:\Users\alcal\source\repos\dbtest\bin\debug\netcoreapp3.1> ./dbtest
Starting Program!
Database : TestDatabase.sdb
Table Name : TestTable
Drop Table if Exists
Create Table
Create Columns
Create a bunch of indexes
Start ...
Upto DataAdapter Select: 0s
Upto DataAdapter Insert: 48.5s
Upto DataAdapter Update: 48.7s
Upto DataAdapter Delete: 48.8s
Elapsed: 48.8s
Close Connection
PS C:\Users\alcal\source\repos\dbtest\bin\debug\netcoreapp3.1>

```

Just for completeness, here is the full source of the app that compiles in VS Community (latest version)

``` VB.NET

Imports System
Imports System.Data.SQLite
Module Program
    Public Sub Main(args As String())
        Console.WriteLine("Starting Program!")

        '--------------------------------------------------------------------------------------------
        Const DBFILE As String = "TestDatabase.sdb"
        Console.WriteLine("Database : " & DBFILE)
        Const TABLENAME As String = "TestTable"
        Console.WriteLine("Table Name : " & TABLENAME)

        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 Data.SQLite.SQLiteCommand(SQLText, iSQLCon)
                                          iSQLCmd.ExecuteNonQuery()
                                      End Using
                                  End Sub
            '·························································································
            iSQLCon.Open()
            '-----------------------------------------[Creating a new datatable with a lot of columns]
            Console.WriteLine("Drop Table if Exists")
            ExecuteNonQuery($"DROP TABLE IF EXISTS {TABLENAME};")
            Console.WriteLine("Create Table")
            iSb.Append($"CREATE TABLE {TABLENAME} (")

            iSb.Append(" ColumnPK Integer PRIMARY KEY")
            Console.WriteLine("Create Columns")
            For iCtr = 1 To 900
                iSb.Append($", Column{iCtr:000} Integer")
            Next

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

            '--------------------------------------------------------------[Create a bunch of indexes]
            Console.WriteLine("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 ...")
            Console.WriteLine("Start ...")
            iSw.Start()

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

                iDA.SelectCommand = iSQLCmd
                Debug.Print($"Upto DataAdapter Select: {(iSw.ElapsedMilliseconds \ 100) / 10}s")
                Console.WriteLine($"Upto DataAdapter Select: {(iSw.ElapsedMilliseconds \ 100) / 10}s")

                iDA.InsertCommand = iCB.GetInsertCommand
                Debug.Print($"Upto DataAdapter Insert: {(iSw.ElapsedMilliseconds \ 100) / 10}s")
                Console.WriteLine($"Upto DataAdapter Insert: {(iSw.ElapsedMilliseconds \ 100) / 10}s")

                iDA.UpdateCommand = iCB.GetUpdateCommand
                Debug.Print($"Upto DataAdapter Update: {(iSw.ElapsedMilliseconds \ 100) / 10}s")
                Console.WriteLine($"Upto DataAdapter Update: {(iSw.ElapsedMilliseconds \ 100) / 10}s")


                iDA.DeleteCommand = iCB.GetDeleteCommand
                Debug.Print($"Upto DataAdapter Delete: {(iSw.ElapsedMilliseconds \ 100) / 10}s")
                Console.WriteLine($"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")
            Console.WriteLine($"Elapsed: {(iSw.ElapsedMilliseconds \ 100) / 10}s")

            iSQLCon.Close()
            Console.WriteLine("Close Connection")

        End Using
        '--------------------------------------------------------------------------------------------
    End Sub
End Module

```