No noticeable benefit to Sqlite Pooled connections when using an encrypted database

[james1301]

Ask a question

I was happy to hear that SQLite now does pool connections and was hopeful that this would speed up my application, as it has an encrypted database creating the connections is quite a slowdown. But after some testing I have realised that there doesn't seem to be really any benefit, as the connection open time speeds up but then the query execution time for the first query is just increased instead. So before the upgrade I am getting creating the connection and running the query times of roughly 500ms on my machine, and after the upgrade I am getting the same. The only difference is that after the initial call to open the first connection, this slow down then moves to the query execution time. So overall it is still approx 500ms.

I first thought this was because it wasn't being cached but it seems like something is being cached but this is just deferred to the execution. I have initially mentioned this to @bricelam on #bricelam/bricelam.github.io#36.

Include your code

I have included a sample project here: ModularAppSQLiteProblem .net 6.zip. This shows that sharing a connection you can achieve <10 ms times, whereas using connection pooling or not the times are pretty much the same.

Include stack traces

N/A

Include verbose output

N/A

Include provider and version information

Microsoft.Dala.Sqlite version: 6.0
Database provider: (e.g. Microsoft.Dala.Sqlite)
Target framework: .NET 6.0
Operating system: Windows 10 64-bit
IDE: Visual Studio 2022

[james1301]

@ajcvickers is there any chance of this going into .net 6?

[ajcvickers]

@james1301 This needs to be fully investigated for 7.0. Pooled connections definitely do help performance a lot when using encrypted databases through common patterns. It's possible that there is something in this specific use that is preventing that, and if this is a bug that meets the bar for a patch, then it could get into an EF Core 6.0.x release, but I would be surprised if this is the case.

[james1301]

OK thanks @ajcvickers for getting back to me. Look forward to hearing more about this.

As of today our plan is not to upgrade to 7.0 as 6.0 is the long term release, but will need to analyse this closer to the time, as this would have a big performance boost for our application as it stands.

[bricelam]

Duplicate of #28774
Fixed in version 6.0.11 by PR #28967