System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 58ed318f2fadb5e0feff145b15cdfdbf3ab5fe60
Title: GetValue hits the xml config file every time
Status: Closed Type: Performance
Severity: Important Priority: Medium
Subsystem: Convert Resolution: Fixed
Last Modified: 2014-08-19 18:01:30
Version Found In: 1.0.93.0
User Comments:
anonymous added on 2014-07-29 21:46:44:
Upgrading from version 1.0.80.0 the SQLiteDataReader's performance of SQLiteDataReader.GetValue(i) has dropped significantly. It used to be 0.011 ms per value retrieved and is now in the range of 0.463 ms per value retrieved.

Looking into what is taking time it is the handling of getting the default db type setting value, particularly having to attempt to read it from the xml file every time. I have tried providing the settings file with those settings provided but this just trades the file not found delay for loading and parsing the xml file. I have also tried to provide those values on the connection string but it did not resolve the repeated hit to the xml settings file.

Going over the code I see a the GetSettingsValue calls "Environment.GetEnvironmentVariable(name)" with the value of "Use_SQLiteConvert_DefaultDbType", if the value is not null it will skip loading the value from the xml file. However, I don't see anywhere where this is put to the environment from either the xml file or from the SQLConnection.

Even if I was to get the override on the connection's DefaultDbType right it would still attempt to get the value from the xml file first because the TypeNameToDbType method always calls GetDefaultDbType() at the start of the method.

The environment variable is the only to avoid the massive overhead of a missing file or even reading the xml file for every field being converted this way. Yet this is never set or changed by SQLite.

Am I missing some call to putenv in the interop/c layer or Environment.SetEnvironmentVariable in the managed layer?

Also shouldn't the XML file settings be cached per connection or have the overrides checked before needing to read the settings file?

mistachkin added on 2014-07-29 22:05:42:
The SQLiteConvert.TypeNameToDbType method should only be called once per
column, when the affinity stored in "typ.Affinity" has the value
"TypeAffinity.Uninitialized".  Since "typ" is from the "_fieldTypeArray",
array this value should be cached between calls.

I'll investigate further just to be sure; however, it should be calling
SQLiteConvert.TypeNameToDbType at most once per column.

mistachkin added on 2014-07-29 22:53:55:
Check-in [d0e28511f3] on trunk adds some debugging code to track and report
on how many times each runtime configuration setting is read, for testing
purposes.

mistachkin added on 2014-07-30 00:40:43:
Fixed on trunk via check-in [69065c8e3b].

mistachkin added on 2014-07-30 05:22:21:
To clarify: the fix prevents reads of the setting when the per-connection
value is specified in the connection string (DefaultDbType property).

mistachkin added on 2014-08-12 19:20:52:
Also see check-in [59d43c48bb].  The NoConvertSettings connection flag can now
(as of 1.0.94.0) be used to completely disable using the GetSettingValue method
from within the SQLiteConvert class.

mistachkin added on 2014-08-19 18:01:30:
As of check-in [948fd5b3a3] on trunk, per-connection caching of these settings
is now enabled.