System.Data.SQLite

Login
This project makes use of Eagle, provided by Mistachkin Systems.
Eagle: Secure Software Automation
Ticket Hash: 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: (text/html)
<p>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).</p>

<p>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. </p>

<p>
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):
</p>

<p>
using (var cmd = conn.CreateCommand("select * from order_header"))
{
  using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
  {
    reader.GetSchemaTable();
  }
}
</p>
<p>
I can send the log file and table DDL on request.
</p>

mistachkin added on 2014-04-03 05:01:17: (text/x-fossil-plain)
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: (text/x-fossil-plain)
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: