[AC-6207] EncryptedDB slow inserts on Android
GitHub Issue | n/a |
---|---|
Type | Bug |
Priority | n/a |
Status | Resolved |
Resolution | Done |
Resolution Date | 2019-04-30T23:48:13.000+0000 |
Affected Version/s | n/a |
Fix Version/s | n/a |
Components | n/a |
Labels | n/a |
Reporter | Nate Whitesell |
Assignee | Shak Hossain |
Created | 2019-04-11T20:48:35.000+0000 |
Updated | 2019-04-30T23:48:15.000+0000 |
Description
Most android devices are far too slow when handling batch inserts on an encrypted database. Here is a link to an example project that highlights the issue:
[https://drive.google.com/file/d/1FFXryxVuyL6eCNcAY3hwRhllnv6LCT3Q/view?usp=sharing]
This example runs a batch insert of 10000 rows on both an encrypted DB and a non encrypted DB and logs the time taken for each. I have tried decreasing batch size to 1000 rows and running 10 of those queries and the performance doesn't improve.
Running the example on a Nexus 4 with Android 6 gave the following results:
[ERROR] Took 45.502s to execute on encrypted DB...
[ERROR] Took 0.081s to execute on non encrypted DB...
Hey Joshua, we don't have permission to view that comment. We're seeing these performance issues with a single SQL statement, not multiple statements. Also, SQLCipher should implicitly create transactions, however we've also attempted to use explicit BEGIN/COMMIT statements to manage the transactions ourselves and see no difference in performance for the use case stated in this JIRA. We're seeing a significant discrepancy between the EncryptedDB module and the native Ti.Database module with identical schemas and queries. I understand encryption adds overhead, however even if we use the module without an encryption key in a non-encrypted manner we are still seeing the performance issues. I also understand that Ti.Database uses the system's native SQLite library, but we don't understand why the discrepancy would be this dramatic.
Oh shoot. I didn't realize I was linking to a private ticket. Here's a copy and paste of some of the key comments below. bq. We use the SQLCipher library to implement encrypted SQLite database support on Android and iOS. They provide the following guidance in improving performance... https://discuss.zetetic.net/t/sqlcipher-performance/14 bq. So, transactions should be used when doing multiple INSERTs and UPDATEs as a single batch. And you should be using indexes to reduce the SELECT, INSER, and UPDATE execution times as well. Also, opening the database file is expensive as well and you should leave it open if you are actively using it. Don't open and close it per database transaction. bq. Note that execution times on Android are going to be a bit slower compared to iOS because there is "language-interop" involved. On iOS, the JavaScript, SQLite, and encryption APIs are all implemented in C, so there is no language interop or extra string copies that need to happen. On Android, there's additional overhead with copying the JavaScript C/C++ strings to/from Java strings (involves transcoding to/from UTF-8 and UTF-16 too). [~ryan@mvretail.com], below is the code I use to demo the performance differences between
Ti.Database
andappcelerator.encrypteddatabase
with and without transaction. The setting the top 2 variablesisEncrypted
anduseTransactions
totrue
/false
allows you to see the performance differences. The "BEGIN TRANSACTION"/"COMMIT" does significantly improve the performance. You should also keep the database connection open, because opening/closing the database file has significant overhead as well.Joshua, have you looked at our example project at all? We are not running individual insert statements. We are running a single query and seeing the performance issues mentioned. We are already aware of the recommendations for running multiple insert statements in a single connection/transaction. This is a different scenario altogether.
bq. have you looked at our example project at all? No. I'm providing general guidance on how to get the best performance out of the 3rd party "SQLCipher" library which is what the EncrypteDB wraps. Effectively this... https://discuss.zetetic.net/t/sqlcipher-performance-optimization/14 The other performance improvement you can make is keep the database connection open and re-use. There is a major performance hit every time you open/close the encrypted database file as well.
Again, we've already implemented those suggestions. We are still having massive performance issues with EncryptedDB.
[~ryan@mvretail.com], then the performance bottleneck you are most likely running into is the transcoding of your very large SQL string to/from UTF-8/UTF-16. Because this involves transcoding the JavaScript UTF-8 string to a Java UTF-16 string, which is then passed to the "SQLCipher" library, which in the end will be transcoded back to UTF-8 when sent to the C/C++ to be written to the database. With the code I posted above, doing 1000 separate inserts with "transactions" is pretty reasonable between the encrypted and unencrypted database modules. The average times for me on a physical Pixel 2 device are: *
appcelerator.encrypteddatabase
: 102 ms *Ti.Database
: 42 ms I suggest that you do the same and split your giant INSERT SQL string into separate INSERT strings surrounded by a begin/end transaction call. This is proven to be far more efficient.Joshua, Thanks for addressing this promptly. I ran your code on a Nexus 4 with Android 6 (one of the environments I described as having this problem) and the results were: * appcelerator.encrypteddatabase: @@@ DB insert duration: 9604 ms * Ti.Database: @@@ DB insert duration: 130 ms And when I increased the row size to 10000 like in my sample project, I got the following results: * appcelerator.encrypteddatabase: @@@ DB insert duration: 104572 ms * Ti.Database:@@@ DB insert duration: 1070 ms This shows the problem exists still even with your suggestions. As I stated in the original bug report, this isn't happening on all android devices. Even the large batch inserts on my sample projects are fast on most devices with android 8 and above. While your suggestions on optimization are helpful, they are no match for the issue on these environments. Could you please investigate why it is so slow on the devices and android versions specified?
I ran my code on a physical Nexus 4 (Android 4.4) device: * appcelerator.encrypteddatabase: 559 ms * Ti.Database: 328 ms I'm pretty sure my Nexus 4 device is older/slower than yours. I've never seen such awful times like you're reporting before. I'm curious if you're running into the same performance issue mentioned to the SQLCipher team below. The claim is that happens with the newest SQLCipher 4.0.1 library version, which is what the "appcelerator.encrypteddatabase" module uses. https://github.com/sqlcipher/android-database-sqlcipher/issues/411 In the above link, they were able to work-around the performance issue by executing the following pragma. Would you mind trying this on your Nexus 4?
Turning off the cipher_memory_security seems to have stopped the extreme slow down. Thanks for digging into this! I will update if this work-around ever stops working or seems to create new issues.
Oh good. I'm glad it worked. And sorry if I came across as being difficult here. We'll keep an eye out for any potential fixes for this on the SQLCipher library's end too. Thanks.