I have been performing a lot of research on a performance issue we are having and wanted to get input whether it may even be resolvable. Most update/insert commands perform moderately well – around 200 ms or less, which in most cases not a big deal. However, we do have some inserts/updates that contain very large BLOB byte arrays. These can be as large as 100 MB and will take 6+ seconds to save. I have read all the performance recommendations about transactions and connections, but this appears to be a raw encryption issue. If I turn off encryption there is no speed issues. I have a single connection and I have multiple inserts/updates that are sent one at time. The larger the BLOB the longer it takes.
Is there any way to overcome this performance issue?
Hello @pratthinds - Can you please try to disable the memory security feature and let us know what the timing looks like?
PRAGMA cipher_memory_security = OFF;
Can you also provide some additional information on what platform you are running on?
Yes, I am currently running with that PRAGMA set. Turning off the memory security did make a difference. It changed some updates from taking 10 seconds down to 7.
Also, I am developing on an i7-8650U with 16GB RAM and an SSD drive running on Win10. Not sure it would make a difference but we run Bitlocker on the SSD’s. The production systems this application will run on will be quite less so it is quite unusable to take 30+ seconds to save a set of records to a table.
Is it taking 7 seconds to insert a single record, or are you inserting multiple records at once? Are you using the default page size?
I should also ask, are you using the official SQLCipher commercial edition libraries for Windows?
No, I am using Entity Framework Core and the best way I found to encrypt the data was to use the SQLitePCLRaw SQLCipher provider.
I am inserting/updating multiple records in one connection. Records with smaller blobs take 1-2 seconds and the records with the largest blobs take upwards of 7 seconds. Typically I am modifying 15-20 records at a time and it takes 10 - 35 seconds respectively. 10 seconds being a smaller collection of smaller blobs and 35 seconds being a larger collection containing larger blobs.
Hello @pratthinds - We don’t directly support the SQLitePCLRaw.bundle_sqlcipher, as that is a third party build. That said we do offer official Commercial Edition builds of SQLCipher for Windows that work with Entity Framework Core (see https://www.zetetic.net/sqlcipher/sqlcipher-for-dotnet) using SQLitePCLRaw.bundle_zetetic. You can request a free trial here, choosing SQLCipher for Windows .NET as the platform:
https://www.zetetic.net/sqlcipher/trial
It might be worth trying our optimized builds to see if they perform better for you.
Outside of that, my primary recommendation would be to ensure you are running multiple updates or inserts within a transaction.
I have a request in for the trial, does that usually take a full day?
Hello @pratthinds - we’ve just approved the trial request. Apologies for the delay, but we have to review each request individually and track it for export compliance.
I am well aware of those requirements in my industry 
Thanks!