System.Data.SQLite

Dear Team,

We uses an encrypted Sqlite db for locally storing data in our WPF  .Net application. We updated the System.Data.SQLite library to the latest version 1.0.111.0 from previous 1.0.94.0 and it was observed to be the queries taking enormous amount of time (10 times compared to 1.0.94.0) to respond. 

Upon detailed analysis it was found out that until 1.0.99.0 the issue is nonexistent   and with 1.0.100.0 or higher versions it is occurring. It happens only with the encrypted database while querying non-index columns.

What type of queries are slower, e.g. SELECT, INSERT, UPDATE, DELETE, etc?

Are you using the connection pool?

How many threads are accessing the database?

What type of queries are slower, e.g. SELECT, INSERT, UPDATE, DELETE, etc?
We observed only in SELECT Statements when the column is not indexed.

Are you using the connection pool?
We dont use the connection Pool. We just use the normal SQLiteConnection. 

How many threads are accessing the database?

Ours is a multithreaded and mult-processes application. However, we were able to reproduce the issue with a single-threaded test application. So it occurs in all cases.

Did you see any slowdown without encryption enabled?

What type of encryption are you using?

Also, is there an example schema and query that clearly shows the problem?

Without encryption, there is no slowdown in query execution. The encryption used is the defauld encyption using the SqlConnection.ChangePassword() method.

Following is the schema of the sample database we used to replicate the defect :

"ID"	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"F1"	INTEGER,
"F2"	INTEGER,
"F3"	VARCHAR,
"F4"	VARCHAR,
"F5"	VARCHAR,
"F6"	VARCHAR,
"F7"	VARCHAR,
"F8"	VARCHAR,
"F9"	VARCHAR,
"F10"	VARCHAR,
"F11"	VARCHAR,
"F12"	VARCHAR,
"F13"	VARCHAR,
"F14"	VARCHAR,
"F15"	VARCHAR,
"F16"	VARCHAR,
"F17"	VARCHAR,
"F18"	VARCHAR,
"F19"	VARCHAR,
"F20"	VARCHAR,
"F21"	VARCHAR,
"F22"	VARCHAR,
"F23"	INTEGER,
"F24"	INTEGER

We have attached the sample solution to replicate the issue. There are 2 databases, one is encrypted and  the other one is unencrypted. The testTable contains 15k mocked rocords.

 The console application uses the system.Sqlite.Data V1.0.100.00  to query both DB and displays the time to execute a select query. We observed a latency of 4-5 times in the encrypted db compared to the unencryped db.

Also we found out that, the critical change in 1.0.100.00  which is the update of  Sqlite core library to the 3.12.1. As explained in the below link, the default page size has been changed to 4096 bytes and the default cache size is set to -2000 (which is 2000 KB). 

https://www.sqlite.org/pgszchng2016.html 

When we set the cache size to higher value (-100000) in the query string, the queries ran without any slowdown. Is this is an expected concequense of the update 3.12.1.

It is a potential consequence.  If you reset the page size manually and the issue
goes away, perhaps your workload works better with the old page size?

We did not change the page size, instead we only set a higher values to the Cache size in the connection string as shown below:

@"data source=C:\data\testenc.sqlite;Read Only=False;FailIfMissing=True;cache_size=-100000"

And this fixed the issue. The queries run without any slowdown in the encrypted db.

However, we still dont have the actual reason why the issue occured in the encrypted database only. Also, will there be any sideeffets with the fix we made?

I think using the higher cache size is the simplest solution for this issue.

Other than the additional memory usage, there should not be any side-effects
associated with this.

Also, in case you are not aware, the CryptoAPI-based encryption included
with System.Data.SQLite is a legacy feature, has known issues, and is
officially unsupported.  It is being retained only for the purpose of
backward compatibility with legacy applications that make use of it.

Alternatively, you might want to look into the commercial SEE extension,
which does work with System.Data.SQLite and is fully supported for use
with it.