Microsoft.Data.Sqlite: Persist prepared statements across commands

[bricelam]

We can cache/pool these on the opened connection to remove the overhead of re-compiling SQL for every new command.

If we add a connection pool (#13837) they can even be persisted across connections.

[bricelam]

It looks like prepared statements aren't intended to be long-lived in SQLite. We'd need ericsink/SQLitePCL.raw#257 to fully avoid depleting the look-aside memory pool. Until then, we can keep the number of cached prepared statements relatively low.

We could also consider having a threshold before we cache (e.g. we only cache a prepared statement on its second execution). Explicitly calling SqliteCommand.Prepare() would cause us to cache it immediately.

[roji]

Noticed this a bit late.

FWIW Npgsql's automatic preparation cache also has an upper limit per physical connection (100 by default IIRC), since prepared statements represent physical server-side resources. And similar to what you say, explicitly-prepared commands (with Prepare()) don't count and are immediately prepared.

(In fact explicitly prepared commands remain prepared forever unless explicitly unprepared - they don't participate at all in the caching logic. It seemed to make sense at the time at least).

[bricelam]

I made a couple attempts at implementing this in bricelam:pool and bricelam:pool2 before switching my focus to connection pooling. Not sure if they're actually useful, but leaving myself a note here just in case.

[lukasf]

I noticed that statements that were prepared within a transaction cannot be used outside that transaction. And likely, statements prepared without transaction (or in a different transaction) cannot be used in a transaction. An InvalidOperationException is thrown otherwise. This effectively prevents usage of a prepared statement cache.

What is the reasoning behind that? Prepared statements in sqlite can be used across transactions without any limitations. Only on very very old versions of sqlite (more than 10 years old), this was not allowed. But this was resolved very long ago. So I do not understand why Microsoft.Data.Sqlite prevents this, when it is all fine from sqlite side.

I have been using prepared statements across transactions in an application for years, and I am surprised that I run into problems when moving to Microsoft.Data.Sqlite.