System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 6d8a515408f7724e84bd122a38ec129bbb447be1
Title: SQLiteDataReader.GetSchemaTable is very slow
Status: Deferred Type: Performance
Severity: Minor Priority: Low
Subsystem: Data_Reader Resolution: Under_Review
Last Modified: 2014-10-17 15:06:35
Version Found In: 1.0.92.0
User Comments:
anonymous added on 2014-04-02 22:54:46:

For a table with approx. 20 indexes a single call to the SQLiteDataReader.GetSchemaTable() method takes about 600ms on my computer (10 times longer than for a table with only 1 or 2 indexes).

The database contains about 50 tables, most of them have 1-2 indexes. I looked briefly at the implementation of the GetSchemaTable() and it looks like it tries to get detailed info about ALL indexes, not only unique (in my case I have only 1 unique index except of PK). Moreover, the implementation does a lot of strange repetitive selects from system tables.

I made a log file for SQLite <prepare> calls, and the single call to the GetSchemaTable() results in almost 7000 (!!!) entries in the log file. The test method is very simple ("order_header" is the name of my table):

using (var cmd = conn.CreateCommand("select * from order_header")) { using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly)) { reader.GetSchemaTable(); } }

I can send the log file and table DDL on request.


mistachkin added on 2014-04-03 05:01:17:
I think that improving the performance of this method while maintaining the
existing semantics may prove to be tricky.

mistachkin added on 2014-10-17 15:06:35:
Could you try to measure again with 1.0.94.0 please?  I believe most of the
performance issues you found should be fixed now.

Attachments: