Does sqlcipher windows supports encryption for memory table?

Hello @rajendradewani - it would be best if you could put the sample application source code up on GitHub. Please make sure to include a permissive open source license statement along with the project. Once its up there we’ll take a closer look. Thanks!

Hi, @sjlombardo ,

I have uploaded the sample application source up on GitHub,

Hi @sjlombardo,

Any updates regarding testing using sample application or your findings. ?

Hello @rajendradewani - apologies for the delayed response. We did take a quick look at the project but had trouble getting it running on a recent version of Visual Studio. We’ll try to take a quick look again early next week and will get back to you with either additional feedback or questions.

@rajendradewani - I tried building the project that you posted up to github, but it does not appear to build in my environment. There is some baked-in dependency on VS 2010 build tools, and it doesn’t work on 2013, even after upgrading the project. I briefly reviewed the code as well, but it is fairly complicated, and not really a simple and standalone example of the problem.

As a result, I decided to approach this a different way by creating the simplest possible test scenario to verify SQLCipher’s behavior. First, I created a database with 134,217,728 rows at about 1.2 gb, with one standard SQLite copy, and one encrypted copy of the database. The number of rows is arbitrary, and was only selected to create a database large enough to offer measurable timings on test operations (i.e. a small database would be too fast to measure accurately).

Next, I constructed a simple test script to pipe to the SQLite or SQLCipher command line shell. This would output timings for various operations after opening a memory database. The test procedure follows:

  1. attach the file database
  2. query sqlite_master on the file database
  3. count the records in the file database table (forces an examination of each row)
  4. copy the records from the file database to a table in the memory database
  5. count the records in the memory database table

Each step is timed for comparison between encrypted and non-encrypted runs.

This is the script. Note that only the first line is adjusted depending on whether it is to be run on the standard sqlite database or the encrypted sqlcipher database:

--ATTACH DATABASE 'test-encrypted.db' AS a KEY 'test123';
ATTACH DATABASE 'test.db' AS a;

SELECT 'start query sqlite_master', strftime('%Y-%m-%d %H:%M:%f', 'now');
SELECT COUNT(*) FROM a.sqlite_master; -- force key on attached database
SELECT 'end query sqlite_master', strftime('%Y-%m-%d %H:%M:%f', 'now');

SELECT 'start query file database', strftime('%Y-%m-%d %H:%M:%f', 'now');
SELECT  count(*) from a.t1;
SELECT 'end query file database', strftime('%Y-%m-%d %H:%M:%f', 'now');

SELECT 'start table copy', current_timestamp;
CREATE TABLE t2 AS SELECT * FROM a.t1;
SELECT 'end table copy', current_timestamp;

SELECT 'start query memory database', strftime('%Y-%m-%d %H:%M:%f', 'now');
SELECT  count(*) from t2;
SELECT 'end query memory database', strftime('%Y-%m-%d %H:%M:%f', 'now');

DETACH DATABASE a;

The output of the run for a standard SQLite database follows:

start query sqlite_master|2016-02-03 17:39:07.485
1
end query sqlite_master|2016-02-03 17:39:07.488
start query file database|2016-02-03 17:39:07.490
134217728
end query file database|2016-02-03 17:39:13.855
start table copy|2016-02-03 17:39:13
end table copy|2016-02-03 17:40:05
start query memory database|2016-02-03 17:40:05.203
134217728
end query memory database|2016-02-03 17:40:05.597

The output for the run on the encrypted database run follows:

start query sqlite_master|2016-02-03 17:41:33.229
1
end query sqlite_master|2016-02-03 17:41:33.232
start query file database|2016-02-03 17:41:33.233
134217728
end query file database|2016-02-03 17:42:00.219
start table copy|2016-02-03 17:42:00
end table copy|2016-02-03 17:43:10
start query memory database|2016-02-03 17:43:10.452
134217728
end query memory database|2016-02-03 17:43:10.719

As expected, the time required to query the encrypted database is higher (27 seconds encrypted vs 6 second). Likewise the time required to “copy” from the encrypted database to the memory table was higher (70 seconds encrypted vs 52 seconds).

However, most importantly, you will note that the time to query the the memory table, once it was populated, was very close; so close that the difference is negligible (267 milliseconds encrypted vs 394 milliseconds).

The results of this explicit testing confirm my earlier statements that there should be no additional overhead from SQLCipher encryption when dealing with a standard (non-encrypted) memory database.

It is therefore likely that there are other factors at play in your case, possibly ranging from optimization of builds, application logic, or the execution of specific queries. Since your application code is not trivial, it is difficult to speculate at the underlying cause.

Thank you for sharing your findings.

requesting you to try with following changes.

if possible, use the SQL cipher file we shared.

the encryption key, more than 80 char.
A table having at least 40 fields.

instead of SELECT count(*) from t2;

something like

int counter=0
NOTE the start time here
for counter=0 ;counter<134,217,728;counter++
SELECT field1,field2…field40 from t2 where recid=counter;
loop
NOTE the end time here

Regarding the source code,
Yes, it is built with vs2010. if you are not able to build, there is an EXE which you can make sure of.

Hello @rajendradewan. In this case we’ve really exhausted our efforts via public support. The test we constructed convincingly disproves a measurable overhead for memory database access as a result of using SQLCipher on an attached encrypted database.

As stated previously, we’re not denying a performance difference in your application use case. However, you may be seeing performance differences for any number of reasons, potentially compounded by the application’s repeated querying of records on a row-by-row basis. Regardless, this had drifted into the realm of an application specific issue, and it is not possible to easily identify the culprit based on the information available.

If you’d like to continue working together on this privately, we could consider an enterprise advanced support agreement, which would allocate time specifically for working with and analyzing your codebase. If this is of interest, please feel free to reach out to us via the private support channel and we can discuss further.

Hi @sjlombardo
With the example I had shared earlier, we have 14728 records and we query one record at a time.
The test you did is also returns 1 record

26714728 = 3932376 = 65.5396 minutes
394
14728 = 5802832 = 96.71386667 minutes
differnce of 47%(approx)

In your test encrypted database is faster then the SQLite.

i would like to eliminate the option of “optimization of builds” and would like to try your prebuilt DLL for C++.
i am happy to purchase prebuild version or paid support if it is resolving the issue we are facing.

I have just fill up request for trial. ( with the same handler as the support is). requesting you to approve the request.
I will test the same and will let you know.
If the pre-build is resolving the issue, we will go ahead and purchase that.

-Regards
Rajendra Dewani

Hi @sjlombardo,

I have tried the Pre-build trial version of sqlcipher but is is showing up same performance as the one we built from Open source.

If you wish we can convert our sample to VS 2013 and share again,
If you wish we can open a support ticket.

Please share how shall we move ahead.
-Regards
Rajendra Dewani

Hi @rajendradewani

As mentioned earlier in the thread, we’ve pretty much exhausted our efforts via public support. The next step would be an enterprise advanced support agreement, which would allocate resources to working on this further. If you’d like to explore this option, reach out to us at support@zetetic.net and we can discuss costs, scoping, etc.