>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] ([ColumnPK], [Column001], [Column002]) 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.