SQLcipher vs stock SQLite: Performance

[Denis Cormier]

Hi everyone,

I was running some tests with SQLite version 3.7.11 and SQLcipher
version 3.7.10 (without encryption) to see if I would get the same
execution times between the two. I was expecting the same execution
time between SQLite and SQLcipher without encryption. The test is the
same as tool/crypto-speedtest.tcl except I run tests for 3 different
databases instead of 2. I was wondering what factors could influence
the execution times (read/write done in pages, etc.) as both SQLite
and SQLcipher were compiled using default parameters (./configure for
SQLite and ./configure --enable-tempstore=yes CFLAGS="-
DSQLITE_HAS_CODEC" LDFLAGS="-lcrypto" for SQLcipher).

Here are the results from one of the tests. The same test was executed
12 times with negligible differences between the execution times. The
trends were the same.

Test 1: 1000 INSERTs

CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));
INSERT INTO t1 VALUES(1,13153,'thirteen thousand one hundred fifty
three');
INSERT INTO t1 VALUES(2,75560,'seventy five thousand five hundred
sixty');
... 995 lines omitted
INSERT INTO t1 VALUES(998,66289,'sixty six thousand two hundred eighty
nine');
INSERT INTO t1 VALUES(999,24322,'twenty four thousand three hundred
twenty two');
INSERT INTO t1 VALUES(1000,94142,'ninety four thousand one hundred
forty two');
Stock SQLite: 1.346
SQLcipher (no encryption): 1.347
SQLcipher (with encryption): 1.495


Test 2: 25000 INSERTs in a transaction

BEGIN;
CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));
INSERT INTO t2 VALUES(1,298361,'two hundred ninety eight thousand
three hundred sixty one');
... 24997 lines omitted
INSERT INTO t2 VALUES(24999,447847,'four hundred forty seven thousand
eight hundred forty seven');
INSERT INTO t2 VALUES(25000,473330,'four hundred seventy three
thousand three hundred thirty');
COMMIT;
Stock SQLite: 0.400
SQLcipher (no encryption): 0.700
SQLcipher (with encryption): 0.754


Test 3: 100 SELECTs without an index

SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<1000;
SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<1100;
SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<1200;
... 94 lines omitted
SELECT count(*), avg(b) FROM t2 WHERE b>=9700 AND b<10700;
SELECT count(*), avg(b) FROM t2 WHERE b>=9800 AND b<10800;
SELECT count(*), avg(b) FROM t2 WHERE b>=9900 AND b<10900;
Stock SQLite: 0.569
SQLcipher (no encryption): 1.269
SQLcipher (with encryption): 1.323


Test 4: 100 SELECTs on a string comparison

SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one%';
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%two%';
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%three%';
... 94 lines omitted
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety eight%';
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety nine%';
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one hundred%';
Stock SQLite: 1.707
SQLcipher (no encryption): 4.919
SQLcipher (with encryption): 4.921


Test 5: Creating an index

CREATE INDEX i2a ON t2(a);
CREATE INDEX i2b ON t2(b);
Stock SQLite: 0.165
SQLcipher (no encryption): 0.330
SQLcipher (with encryption): 0.444


Test 6: 5000 SELECTs with an index

SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<100;
SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<200;
SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<300;
... 4994 lines omitted
SELECT count(*), avg(b) FROM t2 WHERE b>=499700 AND b<499800;
SELECT count(*), avg(b) FROM t2 WHERE b>=499800 AND b<499900;
SELECT count(*), avg(b) FROM t2 WHERE b>=499900 AND b<500000;
Stock SQLite: 0.356
SQLcipher (no encryption): 0.536
SQLcipher (with encryption): 0.518


Test 7: 1000 UPDATEs without an index

BEGIN;
UPDATE t1 SET b=b*2 WHERE a>=0 AND a<10;
UPDATE t1 SET b=b*2 WHERE a>=10 AND a<20;
... 996 lines omitted
UPDATE t1 SET b=b*2 WHERE a>=9980 AND a<9990;
UPDATE t1 SET b=b*2 WHERE a>=9990 AND a<10000;
COMMIT;
Stock SQLite: 0.240
SQLcipher (no encryption): 0.484
SQLcipher (with encryption): 0.534


Test 8: 25000 UPDATEs with an index

BEGIN;
UPDATE t2 SET b=271822 WHERE a=1;
UPDATE t2 SET b=28304 WHERE a=2;
... 24996 lines omitted
UPDATE t2 SET b=442549 WHERE a=24999;
UPDATE t2 SET b=423958 WHERE a=25000;
COMMIT;
Stock SQLite: 1.005
SQLcipher (no encryption): 1.436
SQLcipher (with encryption): 1.645


Test 9: 25000 text UPDATEs with an index

BEGIN;
UPDATE t2 SET c='four hundred sixty eight thousand twenty six' WHERE
a=1;
UPDATE t2 SET c='one hundred twenty one thousand nine hundred twenty
eight' WHERE a=2;
... 24996 lines omitted
UPDATE t2 SET c='thirty five thousand sixty five' WHERE a=24999;
UPDATE t2 SET c='three hundred forty seven thousand three hundred
ninety three' WHERE a=25000;
COMMIT;
Stock SQLite: 0.730
SQLcipher (no encryption): 1.274
SQLcipher (with encryption): 1.539


Test 10: INSERTs from a SELECT

BEGIN;
INSERT INTO t1 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t1;
COMMIT;
Stock SQLite: 0.363
SQLcipher (no encryption): 0.577
SQLcipher (with encryption): 0.931


Test 11: DELETE without an index

DELETE FROM t2 WHERE c LIKE '%fifty%';
Stock SQLite: 0.343
SQLcipher (no encryption): 0.458
SQLcipher (with encryption): 1.094


Test 12: DELETE with an index

DELETE FROM t2 WHERE a>10 AND a<20000;
Stock SQLite: 0.301
SQLcipher (no encryption): 0.519
SQLcipher (with encryption): 0.702


Test 13: A big INSERT after a big DELETE

INSERT INTO t2 SELECT * FROM t1;
Stock SQLite: 0.288
SQLcipher (no encryption): 0.486
SQLcipher (with encryption): 0.666


Test 14: A big DELETE followed by many small INSERTs

BEGIN;
DELETE FROM t1;
INSERT INTO t1 VALUES(1,29676,'twenty nine thousand six hundred
seventy six');
... 2997 lines omitted
INSERT INTO t1 VALUES(2999,37835,'thirty seven thousand eight hundred
thirty five');
INSERT INTO t1 VALUES(3000,97817,'ninety seven thousand eight hundred
seventeen');
COMMIT;
Stock SQLite: 0.084
SQLcipher (no encryption): 0.118
SQLcipher (with encryption): 0.307


Test 15: DROP TABLE

DROP TABLE t1;
DROP TABLE t2;
Stock SQLite: 0.049
SQLcipher (no encryption): 0.055
SQLcipher (with encryption): 0.166

Any insight into this would be greatly appreciated. Thanks.

[Stephen Lombardo]

Hi Denis,

Are you using the latest version of speedtest off master? If so, it looks like I made a mistake, committing a change related to internal performance checking of HMAC. Thus, the script on master is actually comparing the performance difference between SQLCipher with HMAC and SQLCipher without HMAC. As a result,  both of the test cases would actually create encrypted databases. If you used that test verbatim, your tests named SQLCipher(no encryption) are probably using encryption too, resulting in the big difference.

I've fixed the speedtest on master now (https://github.com/sqlcipher/sqlcipher/commit/b6302e30a24ea3cf5ce26a9a4a04c4aa6d3c893d). 

You could make the same changes on your script too, or just post your script to a gist (http://gist.github.com) and we'll suggest how to modify it to match.

Please let us know if this is the issue. Thanks!

Cheers,

Stephen

[Denis Cormier]

Hi Stephen,

Thanks for the response. Of course, I should have made clear the
changes I made to crypto-speedtest.tcl.

Yes, I took crypto-speedtest.tcl from Master. No, I did not use the
test verbatim.

It would have been more accurate to say I did a new .tcl file based on
crypto-speedtest.tcl. Here are my modifications: https://gist.github.com/2635406

Basically, in my modification, the differences between the 3 databases
used for testing are that one uses SQLite's sqlite3 executable, one
uses SQLCipher's sqlite3 executable and another one uses SQLCipher's
sqlite3 executable while beginning each test case with a key (to
perform encryption).

If you need more clarification, or if I made a mistake in the script,
please let me know. Thank you for your time.

- Denis

On May 7, 11:11 am, Stephen Lombardo <sjlomba...@zetetic.net> wrote:
> Hi Denis,
>
> Are you using the latest version of speedtest off master? If so, it looks
> like I made a mistake, committing a change related to internal performance
> checking of HMAC. Thus, the script on master is actually comparing the
> performance difference between SQLCipher with HMAC and SQLCipher without
> HMAC. As a result,  both of the test cases would actually create encrypted
> databases. If you used that test verbatim, your tests named SQLCipher(no
> encryption) are probably using encryption too, resulting in the big
> difference.
>

> I've fixed the speedtest on master now (https://github.com/sqlcipher/sqlcipher/commit/b6302e30a24ea3cf5ce26a9...
> ).

>
> You could make the same changes on your script too, or just post your
> script to a gist (http://gist.github.com) and we'll suggest how to modify
> it to match.
>
> Please let us know if this is the issue. Thanks!
>
> Cheers,
> Stephen
>

[Stephen Lombardo]

Hi Denis,

Thank you for the clarification and providing the script. 

Since we can rule out encryption being the issue, are you by any chance using different optimization settings between your stock SQLite build and SQLCipher? For instance, compiling the stock build with -Os but without it for the SQLCipher build it could definitely lead to these sorts of results. 

If not, there are a few other things that could cause a performance difference, most notably the synchronous setting. 

Or, it could be that the startup of the sqlcipher sqlite3 process could be taking significantly longer than the stock build. You could baseline this by timing how long it takes each to run a simple command, e.g.

time sh -c "echo 'SELECT count(*) FROM sqlite_master;' | sqlite3 test.db"

If none of those pan out, it might be helpful to know what SQLite distribution you downloaded (amalgamation, full src), what platform you're on, some of your machine details, etc.

For comparison, I ran your speed comparison with a stock SQLite built from the 3.7.11 amalgamation. The times I get seem more consistent than your results (most are fairly close, and SQLCipher is faster on some tests, perhaps because of using in-memory temp space).

https://gist.github.com/2639010

Cheers,

Stephen

[Denis Cormier]

Hi Stephen,

I compiled SQLite (3.7.11 amalgamation) using ./configure and make, and I compiled SQLCipher (latest code from git, version 3.7.10) with ./configure --enable-tempstore=yes CFLAGS="-DSQLITE_HAS_CODEC" LDFLAGS="-lcrypto" and make (no -O optimizations).

I was playing with the synchronous settings and by turning it off in the 3 databases, I get the same results for all the test cases except the first one.

Test 1: 1000 INSERTs

CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));
INSERT INTO t1 VALUES(1,13153,'thirteen thousand one hundred fifty three');
INSERT INTO t1 VALUES(2,75560,'seventy five thousand five hundred sixty');
... 995 lines omitted
INSERT INTO t1 VALUES(998,66289,'sixty six thousand two hundred eighty nine');
INSERT INTO t1 VALUES(999,24322,'twenty four thousand three hundred twenty two');
INSERT INTO t1 VALUES(1000,94142,'ninety four thousand one hundred forty two');

Stock SQLite:	0.560
SQLcipher (no encryption):	0.519
SQLcipher (with encryption):	0.658

The times obtained with PRAGMA synchronous=0; (off) are approximately half of the results with PRAGMA synchronous=2; (full). I get the same times whether I set synchronous to off at the start of every test case or only once after the database is initialized.

Using the example command you provided, both sqlite3 executables take the same amount of time to run.

As far as my computer's specs, I'm running Mac 10.7.3 with a 2.4 GHz Intel Core 2 Duo processor with 4 GB 667MHz DDR2 SDRAM memory.

I feel like there's something in the script that I missed, or that my computer's not doing properly to set itself up for test cases beyond the first one.

- Denis

[Stephen Lombardo]

Hi Denis,

I took a really quick peek at the amalgamation configure script and makefile. It is adding -O2 by default, whereas the configure script in the SQLCipher package is not.

Can you try configuring SQLCipher and building like this:

./configure --enable-tempstore=yes CFLAGS="-O2 -DSQLITE_HAS_CODEC" LDFLAGS="-lcrypto"

make 

Then run your test and let us know the results. Thanks!

Cheers,

Stephen

[Denis Cormier]

Boom! That's it! I tried it and the results between stock SQLite and SQLCipher are very similar.

Thanks for looking into this with me.