Cached commands

[draveckym]

Hi, is there a way to cache commands sent to sqlite? It could improve performance quite a lot when commands are used repeatedly.

I am using library to simulate monthly accounting closures of insurance account over 40 years to predict its development to client... it means small set of parameterized calculation commands (dozen at max) called 480 times each. Alternatively same code can run for monthly closure of all contracts signed by clients, which would mean dozen commands used thousands times...

Hint: if I read your code right way, what is needed is storing sqlite3_prepare_v2() statement out params in Dictionary<CommandText, Statement>

Maybe adding such Dictionary as property to Get/Set from client side? Or maybe it wont help at all... :D

Thanks a lot for answering.

[buybackoff]

Please also see #235

[draveckym]

Thanks. Any progress on that since then?

Btw - can You explain closer (I have no experience with this), where this unmanaged memory problem is? Is statement variable somehow unmanaged or what? If we distribute it optionally through Property (CommandText setter could clear that property, so if not set afterwards, new statement is prepared instead), will it be still unmanaged? It wouldn't be nice to use and entire caching would be left on shoulders of programmer, but any usability is better than none O:)

[rowanmiller]

Dupe of #235

[bricelam]

@draveckym This hasn't been high enough priority for me to work on yet, but we'd be happy to accept a community PR for it.

To illustrate the issue:

using (var connection = new SqliteConnection("Data Source=:memory:"))
{
    connection.Open();

    var command = connection.CreateCommand();
    command.CommandText = "SELECT 1;";

    command.Prepare(); // Allocates an unmanaged sqlite3_stmt
}

// command.Dispose() wasn't called, so the sqlite3_stmt is leaked (until the garbage collector
// gets around to it)

The implementation of #235 would be to keep a reference to the sqlite3_stmt in the SqliteConnection so that when it's disposed we could free any of them that leaked beyond the lifetime of their SqliteCommand object. This would reduce the amount of time they remained allocated tying their lifetime to (at most) the lifetime of the SqliteConnection object.