I’m using SQLCipher for UWP and i’m currently trying to find out why the create table query is almost 5x slower then without encryption.
I execute the following code 10 times:
var sqLitePlatform = new SQLitePlatformWinRT(“123456”);
for (var i = 0; i < databasesToCreate; i++)
{
var dbName = $“EncrypedDatabase{i}.sqlite”;
var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, dbName);
var dbConnection = new SQLiteConnection(sqLitePlatform, dbPath);
dbConnection.ExecuteScalar<int>($"PRAGMA cipher_license = '{key}';");
dbConnection.Execute("create table t1(a, b);");
dbConnection.Close();
}
It takes 0.5 seconds to perform on average for each database to create.
If I remove the key (123456) from the SQLitePlatformWinRT constructor and use an empty string. It takes around 0.1 seconds to perform on average for each database.
Is there a reason why this is so much slower when encryption is turned on?
You are seeing a slow down in performance because you are creating the SQLiteConnection within the for loop. Opening a connection to a SQLCipher database is slow by design due to the key derivation process. Try moving the block for creating the connection and applying the license (this only needs to be once) outside of the for block. More information regarding SQLCipher performance can be found here.
The SQLiteConnection is created every time in the for loop because it creates a new database every time. To check wether it’s still slow when I run the code 10 times.
But the opening of the connection itself is not slow. It’s relly the creation of the table that is causing the performance issue. If I remove this line of code:
dbConnection.Execute(“create table t1(a, b);”);
The execution will be really fast. Something like 0.001 seconds for making the connection.
Therefor I want to know if I can do something to speed up the table creation. If it’s not possible I have to do things differently.
Hello @Sander - as @developernotes mentioned, the reason that the statement is processing slowly is because of the Key Deviation process (for context, Key Derivation is a security feature that hardens SQLCipher databases against brute force, dictionary, and pre-computation attacks). The reason you are seeing it slow down the create table statement is that key deviation occurs on the first access of the database after the key is set - in practice this means the first time the connection is used, not when the connection is opened.
If you were to use a single connection, created outside of the loop, you would notice that the first table creation is slower than all subsequent calls. Please give this a try.
Alternately, you could, for the purposes of testing, disable key derivation by using a test key with RAW key semantics. Try passing this value into the SQLitePlatformWinRT constructor as the key: "x'2DD29CA851E7B56E4697B0E1F08507293D761A05CE4D1B628663F411A8086D99'". You should find that SQLCipher skips key derivation and the statement runs faster.
Finally, it is also possible to reduce the key derivation rounds, which would speed things up considerably. However, we recommend against that in almost all cases, and instead recommend that applications avoidopening and closing connections when using SQLCipher, in accordance with the applicable performance tuning recommendations.