System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 7b0e16c1d1e452ba00fa1360e513796e17950795
Title: Performance of GetSchemaTable() depends heavily on number of indizes in the database
Status: Deferred Type: Performance
Severity: Important Priority: Medium
Subsystem: Data_Reader Resolution: Need_More_Info
Last Modified: 2017-01-05 05:47:15
Version Found In: 1.0.101.0
User Comments:
anonymous added on 2016-09-02 07:12:18:
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

mistachkin added on 2016-09-02 15:40:10:
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.

anonymous added on 2016-09-05 07:10:10:
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?

mistachkin added on 2016-09-05 20:15:50:
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.

anonymous added on 2016-09-07 12:44:34:
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?

mistachkin added on 2016-09-07 20:07:28:
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).

anonymous added on 2016-09-08 04:59:08:
Sounds good to me

anonymous added on 2017-01-04 12:25:15:
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?

mistachkin added on 2017-01-04 21:39:44:
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.

anonymous added on 2017-01-05 05:47:15:
OK, thnak you for the info