Microsoft.Data.Sqlite: Performance Testing

[bricelam]

While we've been conscious of performance during development, we've also avoided making any (premature) optimizations that would compromise the simplicity of the code base.

It would be good to do some profiling and see if there are any easy wins.

Even if we don't make any improvements, it would also be good to establish a baseline so regressions an be avoided in the future.

[ajcvickers]

@smitpatel How much work would it be run the EF perf tests using the SQLite provider?

[smitpatel]

It should be easy as changing provider registration

[bricelam]

That's probably sufficient to catch significant regressions. Filed #9681. We should keep this on the backlog to actually optimize it someday.

[bricelam]

cc @sebastienros

[buybackoff]

RE: Performance

There is missing low-level layer that repeats and publicly exposes SQLite API in a "safe as in .NET's unsafe" manner, i.e. pointers, span, etc. wrapped as safe handles, but API is not crafted for ADO.NET (as of now) but allows for low-level fast access without DbDataReader on every call and other allocations such as Queue of statements for every query, boxing for parameters bind, etc. I had to write my own low-level layer based on this library because the current design just so wasteful for calling a very hot query that is prepared and reused forever during application lifetime. Interesting thing is that you already have all the low-level parts, they are just hidden in implementation of ADO.NET stuff.

Simple "SELECT ?X;" query is 2.3x faster as prepared one with binding ?X to a counter value in a loop. Just SELECT 42; is also faster by c.30%.
https://github.com/Spreads/Spreads.SQLite/blob/a9edcd70b4afad87b1d61e09ee2c4c673dc29a0b/src/Spreads.SQLite/Fast/FastQuery.cs

As for connection pooling and automating re-creation of prepared statements (#518) in the current design, it all could be done at application level via some generic object pool. E.g. I added a pool here but there is no reason for it to be in this library. I always need to configure connections with a number of pragmas and had to add a virtual InitConnection method to the pool, which already means that I have to derive the pool in my apps and use separate pools for different use cases. Instead a helper method in an app or a factory lambda in a generic object pool could do all this.

[bricelam]

@buybackoff It sounds like you're looking for the SQLitePCL.raw API. We build on top of this, and you can always drop down to it using SqliteConnection.Handle or SqliteDataReader.Handle.

[bricelam]

We can probably close this issue. I think our biggest wins will come from things we're tracking in other issues:

• Pooling connections Microsoft.Data.Sqlite: Pool Connections #13837
• Re-using prepared statements more Microsoft.Data.Sqlite: Persist prepared statements across commands #14044
• Stop boxing Consider avoiding boxing in SqliteDataReader.GetFieldValue<T>() aspnet/Microsoft.Data.Sqlite#149
• Optimizing SQLitePCL.raw
  • Span<T>
  • UTF-8