When performing transactions to insert data in batches, the efficiency of SQLcipher to insert data is much slower than that of sqlite

SQLCipher Issues
1

The efficiency of using SQLcipher to insert data using transactions is reduced by 70% compared to the efficiency of SQLite using transactions to insert data。

2

Hi @chenyongz

Thank you for your interest in SQLCipher. Can you provide a few additional details regarding your performance scenario, specifically:

  • SQLite version
  • SQLCipher version
  • SQLCipher runtime configurations differing from default
  • Target runtime platform
  • Scenario details:
    – Inserts per transaction
    – Data size contained in inserts
    – Journal mode used
    – Single or multi-threaded execution
    – Database size before and after all inserts run
3

Hi @developernotes
Thank you for your reply,the detailed information about my efficiency test is as follows:
1.SQLite version:3.31.0, SQLCipher version:4.4.0.
2.SQLCipher source code is compiled according to official documents,the default configurations are
not modified at runtime.
3.The target runs under the Linux platform.
4.Scenario details:
*Using a single thread to execute a transaction, 2.5 million inserts are cycled in the transaction,
and the inserted data is a string of less than 30 bytes.
*After the transaction is committed, the database size is about 225M.
*No log related settings were made during use.
Judgments based:
*Running the same piece of code, connecting to SQLCipher inserts about 60,000 records/sec, but
connecting to sqlite can reach about 200,000 records/sec.
Please let me know if I use it wrong.
Looking forward to your reply.

4

Hello @chenyongz

Thank you for providing some additional information. Unfortunately, those numbers are not consistent with our internal testing on the Linux platform given the specifications you have provided.

To expand on our questions a bit more:

  • What Linux version are you utilizing?
  • What are the hardware specs of the host machine running Linux?
  • What CPU architecture are you targeting?
  • What crypto provider are you utilizing in SQLCipher?
  • How did you build the crypto library being included with SQLCipher?
  • What is the version of the crypto library being used?

The answers to these questions may frame the difference in performance. Additionally, if you can share a specific test suite scenario in code, we would be happy to review this report further. Thanks!

5

Hi @developernotes
Thanks for replying to me again.The attachment is my test program. Since the size of a single attachment is limited to 2048kb, part of the library is divided into two attachments and uploaded.
My test environment: Linux version: CentOS Linux release 7.8.2003.
The encryption library and related libraries are in the accessory package.
The host hardware information is: Intel i3-9100F CPU@3.60GHz, 16GB RAM. I don’t think there are so many efficiency differences caused by the host hardware.

test.zip (1.9 MB) test1.zip (1.9 MB)

6

Hello @chenyongz - thanks for providing a sample. I took a quick look at this. There are likely a few things going on.

First, the test application is making very inefficient use of prepared statements on the insert test. It is creating, executing and destroying a new prepared statement for every iteration of the test loop, instead of reusing a single statement via sqlite3_reset. This is very intensive and repeatedly allocates and deallocates memory which, by default, SQLCipher is going to sanitize which is a particularly expensive operation. You should fix this test to properly use a single prepared statement across multiple iterations if you are benchmarking bulk-loading of data. Alternately, if you can’t do that for some reason, it would be sensible to disable memory security, via PRAGMA cipher_memory_security = OFF;, to make repeated allocation/deallocation cycles less expensive.

Second, I suspect you are not using optimization when you are building SQLCipher. I would add the -O2 to the CFLAGS you use to build libsqlcipher.a.

Third, it is possible you are not using an optimized OpenSSL. You should check to make sure that you are using a recent OpenSSL package, and that it is compiled with assembler optimization enabled.

For reference, with an optimized SQLCipher compilation, linked against an optimized OpenSSL library, and with proper use of prepared statements I was able to get 326,000+ records/sec with your test program.

$ ./testsqlcipher insert
insert
insert 2500000 configuration average effect: 326119.738645 records/sec
insert 2500000 spends: 7.665896 sec
7

Thanks a lot.Your suggestions are very useful to me.
As you said, after using the add -O2 optimization, the speed is greatly improved.