System.Data.SQLite

The time a call of SQLiteDataReader.GetSchemaTable() needs depends on the size of the database. I found a related ticket with id 6d8a515408f7724e84bd122a38ec129bbb447be1 and think we encounter the same problem. We have big databases with datamodels defined by our customers who sometimes use up to 700 tables with ~75 columns, sometimes even more than 100 and about 10 to 15 indizes per table.

When we try to validate the defined datamodel against the database we use the GetSchemaTable() method and in case of big databases the validation takes over an hour.

I made a test project which creates a few tables with indizes and the more indizes the database contains of, the longer one call to GetSchemaTable needs.

See test results for example. I can provide the test project if needed. The time given in the screenshot is the time, each call of GetSchemaTable needs.
Results -> http://fs5.directupload.net/images/160902/uw7rvibn.png

Do you happen to know which part of GetSchemaTable is taking the most time?

To a certain extent, it makes sense that GetSchemaTable would take more time on a
larger database.

Also, I'm not sure how much it can be changed and still maintain backward
compatibility; however, I'm open to using a better algorithm inside it, if
possible.

Finally, given that this issue is not critical, it will not be a blocker for
1.0.103.0.

I downloaded the sources of SQLite to profile what part of GetSchemaTable takes the most time but when I open the solution Visual Studio shows no source files in the projects ...

But as mentioned before i could send you my test project to reproduce the situation. Or can I somehow attach the testproject to this ticket?

There are files in the project; however, Visual Studio does not display them
because they are included via another file.  This does not impact the compilation
process and you should still be able to run the Visual Studio profiling tools.

I did some profiling and debugging now. Most of the time is used for actually querying the databse in SQLite3.Step(). In my case about 75% of the total time is used there as you can see in this screenshot -> http://fs5.directupload.net/images/160907/4st9ppfe.png

I logged the (different) statements which were executed and counted how often they were executed and found out, that most statements are just executed once, but three statments are executed very often, the last number is the call count  -> http://fs5.directupload.net/images/160907/ulxdxo6q.png

The statements which are called a lot of times originate from SQLiteConnection.Schema_IndexColumns(). Is it possible to cache the result of the queries to not requery them so often?

It seems like the GetSchemaTable method could benefit from caching.  I'll look
into adding that for the 1.0.104.0 release (as it's a bit late in this release
cycle to add something that extensive).

Sounds good to me

Hello again,
since in the meanwhile Version 104 is released but there were no changes made to this ticket. I'm guessing this Issue isn't addressed yet? Do you if changes will be made for version 105?

Unfortunately, there was not enough time in the 1.0.104.0 release cycle to
implement and test this enhancement.  However, it's still on the radar and
will likely be part of a larger "database metadata" refactoring effort.

OK, thnak you for the info