System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: a99252c1160505bd4f912351658f335bf7cf98fd
Title: Performance issue when using multiple SQLiteCommandParameter
Status: Deferred Type: Performance
Severity: Important Priority: Medium
Subsystem: Statement Resolution: Under_Review
Last Modified: 2017-10-16 03:08:03
Version Found In: 1.0.105.1
User Comments:
anonymous added on 2017-06-28 14:06:02:
When using multiple SQLiteCommandParameter, the performance is really slow on device with low hardware. If instead of using SQLiteCommandParameter, we use a raw SQL string or we use directly SQLite without the C# wrapper, performance is really fast.

C# wrapper with 100 SQLiteCommandParameter ~ 15ms
C# wrapper with 500 SQLiteCommandParameter ~ 250ms
C# wrapper with raw sql string ~ 1ms
SQLite without the wrapper but with CommandParameter ~ 1ms

The more SQLiteCommandParameter we used, the less the performance are good, it's not linear.

We suspect the issue is inside the SQLiteStatement.MapParameter method (used by SQLiteParameterCollection), the algorithm used seems to be N^2.

mistachkin added on 2017-06-30 18:22:11:
It's going to take some time to figure out what the underlying assumptions are
regarding the MapParameter method.  Currently, it does not appear to make sense;
however, there could be some subtle behavior that is being relied upon to keep
backward compatibility.