SQLite Forum

SQLite .NET: CommandBuilder performs really bad on tables with a lot of indexes
Login

SQLite .NET: CommandBuilder performs really bad on tables with a lot of indexes

(1) By Ted (Ted145) on 2021-01-12 11:48:08 [link] [source]

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:

   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

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

(2) By Larry Brasfield (LarryBrasfield) on 2021-01-12 18:51:01 in reply to 1 [link] [source]

What makes you think that the stretches of code between your timer initialization and various reads of the timer are doing anything whatsoever in the SQLite library or accessing your database? I see nothing, in either your code or the docs for the SQLite.SQLiteDataAdapter class, to make me believe the SQLite library code is even reached during the time those stretches execute.

I must add something else, (just to save some time reaching this issue, which will surely come up): When you design a table with nearly a thousand columns and gobs of indices, do you truly expect to find the DBMS to have been optimized in any way for such an unusual table structure? My expectation would be to find that some name searches are slow for such a use case because the more voluminous code needed for large hash or btree symbol tables is not worth the execution time or code space in the much more common case where tables represent real world relations. To be blunt: Your 900+ column table represents poor schema design.

(3) By Ted (Ted145) on 2021-01-12 20:00:46 in reply to 2 [source]

Hello Larry,

don't blame the schema design. I know, that it is bad and I would never create such a column monster. I inherited 20 years of work of my predecessor and haven't a chance, to change that on a fast way ... so currently I have to live with it.

To your question:

The 40 seconds are consumed in the call to SQLiteCommandBuilder.GetInsertCommand (respectively the first call to any GetXXXCommand of the CommandBuilder). So ... if I call the SQLiteCommandBuilder method ... I am pretty sure, that the SQLite dotNet-Library is involved in that call. Would be very strange if not. ^^ Also ... if I open the database before this call a second time exclusive then the call will result in a "database locked" exception. I would say, that this supports my assumption.

I know, that SQLiteCommandBuilder inherits from Microsofts Data.Common.DbCommandBuilder and so it may be, that there is an awful entanglement with the Microsoft code. Anyway ... all this doesn't happen with MSSql, MySql, Oracle or Access.

Also note the parallelism (parallel NOT exponential) between time measurements and the number of indexes and columns. It seems not to be a question of "optimization for 900+ columns". It seems to be a performance problem small enough to be not noticed before (compared with the operations that follow normally).

(4) By Warren Young (wyoung) on 2021-01-12 20:44:15 in reply to 3 [link] [source]

don't blame the schema design

Why not? If a better schema design would solve the speed problem, then it's a fair target for criticism.

I have to live with it.

Part of that means abandoning any reasonable expectation that someone else's code will magically make your DB design perform well.

I mean, it's great if the SQLite developers find a way to make that happen, but if not, it's unreasonable to be upset that SQLite remains unoptimized for pathological use cases.

Realize however that SQLite cannot be so optimized until the developers have a reproducible test case, which you haven't provided.

The 40 seconds are consumed in the call to SQLiteCommandBuilder.GetInsertCommand

The next step I'd take is to get Microsoft.Data.Sqlite out of the way: is the query just as slow from the sqlite3 command shell?

If it's fast, then the problem's in Microsoft's .NET class, not in SQLite, so this isn't the right place to report it.

(5.2) By Ted (Ted145) on 2021-01-12 23:32:43 edited from 5.1 in reply to 4 [link] [source]

So far, the argument leads to nothing, except for accusations that bring us no closer to any solution. But okay ... let's just continue with that.

Why not? If a better schema design would solve the speed problem, then it's a fair target for criticism.

Maybe. But if a possible solution (currently) not affordable, I have to seek for an other.

Realize however that SQLite cannot be so optimized until the developers have a reproducible test case, which you haven't provided.

What does "reproducible" means for you? Why don't you feel able to execute the code above and get comparable results? Did you notice that the code above should run everywhere without any requirements except Visual Studio and the SQLite.net-library?

is the query just as slow from the sqlite3 command shell?

Did you see any query in "SQLiteCommandBuilder.GetInsertCommand" which could be executed in the sqlite3 command shell?

I don't. And without investigating the source code of SQLiteCommandBuilder myself I will not be able to know, what happens in there.

So ... asking somebody who knows the source code should be a valid way, don't you think so? If the developers are of the opinion that it is not worth pursuing, then I can still familiarize myself with the source code or develop my own CommandBuilder or work around.

If it's fast, then the problem's in Microsoft's .NET class, not in SQLite, so this isn't the right place to report it.

All CommandBuilders (MSSql, Access, MySQL, SQLite) inherit from Microsoft's DbCommandBuilder. 3 of them run fast, 1 comparatively slow. Do you really believe it makes sense to investigate Microsofts part first?

AddOn:

Oh ... I missed, you wrote "Microsoft.Data.Sqlite". I don't use the "Microsoft.Data.SQLite" components, I use only the "System.Data.SQLite" components from the mentioned "SQLite-netFx46-static-binary-x64-2015-1.0.112.0"-Library. But: as all ado.net components also these components inherits from Microsofts "System.Data.Common".

(6.1) By Warren Young (wyoung) on 2021-01-12 23:25:53 edited from 6.0 in reply to 5.1 [link] [source]

What does "reproducible" means for you?

You haven't given us TestDatabase.sdb or the SQL needed to create it.

Why don't you feel able to execute the code

I'm not a VB.net programmer, and I don't want to fire up a Windows VM just to build your example. You're the supplicant here: it's considered polite to boil your problem down to something that reproduces with the sqlite3 shell, rather than make those that would help you reproduce your build system first.

Even if I were willing and able to do as you request, lacking the DB in question, the program would fail on the first substantial line of code, which tries to open this DB file you've chosen not to provide.

without any requirements except Visual Studio and the SQLite.net-library

Maybe if you ported it to .NET Core, you could make a good argument that everyone should be able to build your program, since I suspect Windows doesn't have nearly the dominance among users of this forum as it does in the corporate world.

Even those of us with Windows VMs might not have installed Visual Studio, since it's so monstrously huge. .NET Core is a much easier "ask."

Did you see any query in "SQLiteCommandBuilder.GetInsertCommand" which could be executed in the sqlite3 command shell?

I don't speak VB.net, so I didn't read your code carefully enough to see that.

However, your point seems to be that opening the DB and preparing to execute the first query costs over 40 seconds of run time. So, what happens when you open the DB in the shell and execute a trivial query? If it completes in much less time than 40 seconds, the delay can't be in SQLite proper.

Do you really believe it makes sense to investigate Microsofts part first?

Each subclass has code unique to it, so if you can eliminate SQLite proper, then yes, you should be looking into that subclass's code. It could very well be doing something suboptimal, which has no equivalent in the other 3 subclasses.

(8) By Ted (Ted145) on 2021-01-13 00:32:18 in reply to 6.1 [link] [source]

I'm not a VB.net programmer, and I don't want to fire up a Windows VM just to build your example.

Okay. Let me explain that the "TestDatabase.sdb" will be created by the "iSQLCon.Open()" command, if it doesn't exist. An empty database is enough for the code above, so it isn't necessary to provide one. But you can use also any database you want to use. (See "DROP TABLE IF EXISTS {TABLENAME};")

it's considered polite to boil your problem down to something that reproduces with the sqlite3 shell

It's not possible to reproduce the problem of a car with the road below it. And so it is not possible to reproduce a "SQLite dotnet library" problem with the SQLite shell. The code above has anything to reproduce the problem, so I think I fulfilled the polite consideration already.

Maybe if you ported it to .NET Core

That doesn't make sense in first step. The problem seems to be in the .NET40 library and a .NET Core version would rely on other binaries. Maybe, that the problem exists there also, but if not (and currently we don't know), porting isn't purposeful.

I don't speak VB.net, so I didn't read your code carefully enough to see that.

Okay. The "GetInsertCommand" method is part of the SQLite library provided by the sqlite.org team.

However, your point seems to be that opening the DB and preparing to execute the first query costs over 40 seconds of run time. So, what happens when you open the DB in the shell and execute a trivial query? If it completes in much less time than 40 seconds, the delay can't be in SQLite proper.

... the problem is not the road ... it's the car. And sqlite.org also provides this car ^^. Let me explain it a bit more:

The method SQLiteCommandBuilder.GetXXXCommand takes an SELECT statement, determines the table schema and then creates the corresponding INSERT/DELETE/UPDATE-statements (like "INSERT INTO [main].[sqlite_default_schema].TestTable VALUES (@param1, @param2, @param3)"). For this job it will need nothing more than the column names, perhabs the column types and also the primary keys (in case of UPDATE).

A query to get the schema will surely take only a few ms in the sq3lite shell (= road). The question is: why does the sqlite dotnet library (=car) needs 40 seconds for that job, while requesting the schema will take only a part of an second?

if you can eliminate SQLite proper

See my AddOn to my previous post. I think, we missed us in the details. As you pointed out: "Microsoft.Data.Sqlite" is developed by Microsoft, but I don't use that library. I use "System.Data.SQLite", developed by the SQLite team. And the SQLiteCommandBuilder is a part of that library. So I can't eliminate the (inherited) Microsoft base from that.

(10) By Warren Young (wyoung) on 2021-01-13 02:45:39 in reply to 8 [link] [source]

An empty database is enough for the code above

That cannot possibly be true. An empty database will open instantaneously, no matter the host language.

It must be these "tables with several hundred columns and up to a hundred indexes" that cause the problem so please provide the minimal schema necessary to demonstrate the symptom.

If one of us simply creates a test database with our own bright ideas of what these hundreds of tables and indices look like, and it performs fine, then we've spent a lot of effort to prove nothing. Whereas if one of us takes your schema and opens it with our chosen tools, then we can say something useful about what happens next.

(11) By Warren Young (wyoung) on 2021-01-13 02:47:41 in reply to 10 [link] [source]

Incidentally, what happens if you clone the DB, drop all the data, leaving the schema intact, tables empty? Is it still slow?

What if you drop half the indices?

What if you drop the other half? Can you bisect your way towards a performant subset of indices, or is it simply that the opening speed drops as a direct function of the number of indices?

(14) By Ted (Ted145) on 2021-01-13 04:01:27 in reply to 11 [link] [source]

Incidentally, what happens if you clone the DB, drop all the data, leaving the schema intact, tables empty? Is it still slow?

Jepp.

What if you drop half the indices?

doubles the speed.

What if you drop the other half?

doubles the speed.

or is it simply that the opening speed drops as a direct function of the number of indices?

Not the "opening", is slow - that performs in < 10ms. It's the creation of the SQL statements that takes the huge time. And yes, the speed depends directly to the number of indices.

(12.1) By Keith Medcalf (kmedcalf) on 2021-01-13 03:50:26 edited from 12.0 in reply to 10 [link] [source]

My understanding from reading the documentation of the CommandBuilder object is that it takes a "select statement" as a template for an in-memory client-side result set and by preparing the select statement then retrieves a crapload of data about the columns and sources and inspection of the indexes associated with each item in the select list to determine the "candidate keys" so that it can automagically generate the rest of the CRUD statements.

That is given the "R" it will automagically generate the "CUD" statements that will make the in-memory result set consistent with the actual source rows.

This would mean that the time taken is proportional to the number of columns in the select and the number of indexes in which those columns participate.

While this may be an efficient crutch for the inherently incompetent, I would consider such practices to be inherently unsound. This is the "sort of shit" that causes almost everything designed by Microsoft or using dot Snot to be slower than molasses running uphill in an Edmonton winter -- such things as the various Microsoft Active Directory tools in which you need to open them the week before you need to use them, and only on a machine with gigabytes of RAM, because the idiots that designed that crap are used to "itty bitty" directories and programs with 3 products and 5 customers but that totally fall to bits and are completely unusable when presented with "real world" data.

(15) By Ted (Ted145) on 2021-01-13 04:17:53 in reply to 12.1 [link] [source]

Absolutly correct.

And for the SQLite Command Builder this "CUD" generation takes an unusually long time. (Or rather the "GetSchema" method of the SQLite Data Reader, as I know now).

(13.1) By Ted (Ted145) on 2021-01-13 04:34:50 edited from 13.0 in reply to 10 [link] [source]

That cannot possibly be true.

But it is the truth.

An empty database will open instantaneously, no matter the host language.

Also true. And the code above confirms that also:

The code above explained:

  • Create a completly NEW database

  • Create the "TestTable"

  • Add ColumnPK / Integer / primary key

  • Add Column001 to Column900 / Integer

  • Add 45 indexes

Upto DataAdapter Select: 0s

All these steps and also executing "SELECT * FROM TestTable;" executes nearly instantaneously without any problem. Also adding, deleting or updating commands will be executed in an acceptable time (but the code above doesn't do any operations, because that works without a problem).

But: With Ado.net it is usual to create the needed SQL commands for delete/insert/update out of the select command. Thatfor the CommandBuilder class exists. And exactly the call to generate an command burns the 40 seconds.

Meanwhile I took a deeper look into the internal source code of SQLiteCommandBuilder. The CommandBuilder executes the "SELECT * FROM TestTable;"-statement with the "SchemaOnly" and "KeyInfo"-option. The following call to "SQLite.DbDataReader.GetSchemaTable" takes 40 seconds. Currently I can't say, which statements are executed in there, but there are some nested selection-loops inside to collect the schematable.

(Please note again: I am talking about the SQLite dotnet API "System.Data.SQLite Downloads" from this page )

If one of us simply creates a test database with our own bright ideas of what these hundreds of tables and indices look like, and it performs fine, then we've spent a lot of effort to prove nothing.

Also correct - because you would test the road and the problem isn't the road. The time wasting occures in the "SQLite to ADO.NET"-layer (car). And that's why the detailed schema doesn't bother. I don't know anymore, how I could explain it better.

(7) By Larry Brasfield (LarryBrasfield) on 2021-01-13 00:20:53 in reply to 3 [link] [source]

FWIW, I agree with Warren's comments to your post 3. I would not be so quick to dismiss schema (and corresponding data) restructuring to solve a performance issue. After all, all those "mumble###" columns are bound to be highly similar to each other and subject to a readily automated disassociation into another table. You've probably seen the old saw about a patient telling his doctor that it hurts when he does X, then being advised to stop doing X.

Getting back to your narrowly defined problem: There is clearly no actual querying, updating, inserting or deleting going on in that timed stretch of VB code. If (as you argue) the calls are reaching the SQLite.NET library, I would think that the most work being done then would be for the adapter layer to be creating some prepared statements. If you could just post here the SQL that goes into that process, (as verbose as it will be), that SQL could at least be submitted to the SQLite CLI shell so folks here can do something beyond gaze at VB.NET code and wonder if anybody really expects somebody to get Visual Studio and VB.NET installed just for that purpose. (We see very little VB code here, and I expect familiarity with it is lacking here. The only reason I can read it is past experience; I never install VB.NET because C# is such a clearly superior way of targeting the .NET CLR.)

I think that once somebody determines that, yes, hundreds of columns bog down statement preparation, the response is likely to be that a schema change is needed. I doubt that the code asked to deal with hundreds of columns and a multitude of indices is going to be sped up soon, and probably never will be if that would slow down more reasonable use cases or require more memory for them. I am highly skeptical that getting to a more reasonable schema would be all that difficult for whoever owns it, (unless they are mentally challenged.) Of course, if it is something set by a separate corporate entity, (or even an IT group), it may be cast in concrete for you. (In that case, I hope I do not own any of the company's stock.)

(9) By Ted (Ted145) on 2021-01-13 01:26:57 in reply to 7 [link] [source]

wb Larry.

You can be sure ... in the long term I will certainly optimize the database structure. But since we're talking about a productive system made up of 13 million lines of (VB6) code, I can't do that on the fly ... as much as I would like.

If I had a performance problem adding, querying, deleting or editing rows in such monster tables, I would have no other choice. However, all of these processes take place in an absolutely acceptable time. Only the creation of the necessary SQL statements with the CommandBuilder takes an incomprehensible amount of time and I cannot think of any reasonable reason why this should be the case.

And please ... don't start a pointless "C#" vs "VB.net" discussion. As long as both languages lead to the same IL and remain translatable into the other, that's only a matter of taste ;-)

(16) By BoonDock on 2021-01-13 06:08:37 in reply to 1 [link] [source]

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)


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

(17) By Ted (Ted145) on 2021-01-13 09:10:18 in reply to 16 [link] [source]

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.

Okay, thanks for confirming this. This leads me to believe that it has more to do with drive speed than CPU speed. In sum I think, there are to many (and perhabs unnecessary) iterations inside the DataReader.GetSchema method.

(20) By mistachkin on 2021-01-14 04:50:30 in reply to 17 [link] [source]

Thanks for the report.

I've looked at the command builder class and didn't notice anything obvious
that explains the times you are seeing; however, I would like to note that
it relies heavily upon the DbCommandBuilder class provided by .NET itself.

That being said, I'm open to suggestions on how to make it faster as long
as we can retain backwards compatibility.

(21) By Ted (Ted145) on 2021-01-14 23:17:25 in reply to 20 [link] [source]

Hello mistachkin,

thanks for your response. Please note my text from above:

Meanwhile I took a deeper look into the internal source code of SQLiteCommandBuilder. The CommandBuilder executes the "SELECT * FROM TestTable;"-statement with the "SchemaOnly" and "KeyInfo"-option. The following call to "SQLite.DbDataReader.GetSchemaTable" takes 40 seconds. Currently I can't say, which statements are executed in there, but there are some nested selection-loops inside to collect the schematable.

I didn't investigated it any further until now, because I wrote a workaround meanwhile. But I will have a deeper look into the GetSchemaTable-method as soon as I will find some time.

(18) By Warren Young (wyoung) on 2021-01-13 22:26:45 in reply to 16 [link] [source]

I decided to run the tests. I get pretty much identical results

Until you wrote that, I was under the impression that we needed the SQLite DB file with the schema built in order to see the problem. I didn't see that this program created the schema.

Yes, above, Ted said this program created the DB, but I took that to be "created" in the trivial sense: SQLite will create a new empty DB file by default if it doesn't already exist. Then there's all that back-and-forth about whether the problem occurs with an "empty" DB, which confused the issue. I don't consider the TestDatabase.sql file resulting from running this program to be "empty": it has a large schema resulting in a DB that's 212 kB even after VACUUMing with page_size=512. That's not "empty" to my way of thinking.

With the confusion swept aside, I tried it here on my Mac with the latest dotnet packaged for Homebrew, and I see the symptom also. Repro steps:

brew install dotnet
dotnet new console -lang=vb -n=many-index-test
cd many-index-test
pbpaste > Program.vb         # save VB.net code above from clipboard
dotnet add package System.Data.SQLite
dotnet build
dotnet run

Same speed profile as originally posted.

I apologize for the confusion. However, I think it should stand as a lesson that providing the above reproduction command sequence would've avoided a lot of the confusion up-front.

This also shows that my original guess was correct: the problem isn't in SQLite proper, it's in the SQLite-specific .NET interface class.

Further proof:

$ time sqlite3 TestDatabase.sdb 'select * from TestTable'

real	0m0.013s
user	0m0.008s
sys	0m0.004s

That is, opening the resulting SQLite DB in the shell and executing a trivial query is fast, regardless of the ridiculous schema. This shows it's the .NET code doing something suboptimal, not SQLite.

I've temporarily uploaded a copy of the resulting DB here for those who want to try other experiments without going through the dotnet steps, though I'm not sure what you can learn, given that the problem seems to be in System.Data.SQLite.

(19) By Warren Young (wyoung) on 2021-01-13 23:12:18 in reply to 18 [link] [source]

Forgot to mention: this Mac has an SSD, so the problem isn't due to RPM-limited fsync() on spinning disks.

(22.1) By Ted (Ted145) on 2021-01-14 23:26:32 edited from 22.0 in reply to 18 [link] [source]

Hi Warren,

thanks for your report and your clarification. I appreciate that a lot. And also a big thank you, that you tested it with your mac.

And sorry, if I seemed to be upset. It's not easy to lead an argumentation in a foreign language but finally we worked it out. :-)

Regs, Ted

(23) By Ted (Ted145) on 2023-10-18 13:35:59 in reply to 22.1 [link] [source]

The behavior was tested and reproduced using the "System.Data.SQLite.Core V1.0.118" NuGet package. The timings are slightly better, but still unacceptable.

:-(