System.Data.SQLite

Login
This project makes use of Eagle, provided by Mistachkin Systems.
Eagle: Secure Software Automation
2014-08-19
18:01 Ticket [58ed318f2f] GetValue hits the xml config file every time status still Closed with 3 other changes artifact: df892565d7 user: mistachkin
17:58
Add per-connection caching of the 'Use_SQLiteConvert_DefaultDbType' and 'Use_SQLiteConvert_DefaultTypeName' settings. Pursuant to [58ed318f2f]. check-in: 948fd5b3a3 user: mistachkin tags: trunk
2014-08-12
19:40
Revise and enhance tests for [58ed318f2f]. check-in: 984343ce67 user: mistachkin tags: trunk
19:20 Ticket [58ed318f2f] GetValue hits the xml config file every time status still Closed with 3 other changes artifact: 0d0e610d0b user: mistachkin
19:19
Add NoConvertSettings connection flag to disable querying of runtime configuration settings from within the SQLiteConvert class. Pursuant to [58ed318f2f]. check-in: 59d43c48bb user: mistachkin tags: trunk
2014-07-30
05:22 Ticket [58ed318f2f] GetValue hits the xml config file every time status still Closed with 3 other changes artifact: 5fa234ff06 user: mistachkin
00:40 Closed ticket [58ed318f2f]. artifact: 8cb30bcfbc user: mistachkin
00:39
Minimize usage of the 'Use_SQLiteConvert_DefaultDbType' and 'Use_SQLiteConvert_DefaultTypeName' settings. Fix for [58ed318f2f]. check-in: 69065c8e3b user: mistachkin tags: trunk
2014-07-29
22:53 Ticket [58ed318f2f] GetValue hits the xml config file every time status still Open with 3 other changes artifact: b297f95088 user: mistachkin
22:48
For the debug build configuration only, keep track of how many times each runtime configuration setting is read and have the test suite report on same. Pursuant to [58ed318f2f]. check-in: d0e28511f3 user: mistachkin tags: trunk
22:06 Ticket [58ed318f2f] GetValue hits the xml config file every time status still Open with 4 other changes artifact: be6351b473 user: mistachkin
22:05 Ticket [58ed318f2f]: 6 changes artifact: 6d74443d32 user: mistachkin
21:46 New ticket [58ed318f2f]. artifact: d077f59a68 user: anonymous

Ticket Hash: 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.