Titanium JIRA Archive
Appcelerator Community (AC)

[AC-6207] EncryptedDB slow inserts on Android

GitHub Issuen/a
TypeBug
Priorityn/a
StatusResolved
ResolutionDone
Resolution Date2019-04-30T23:48:13.000+0000
Affected Version/sn/a
Fix Version/sn/a
Componentsn/a
Labelsn/a
ReporterNate Whitesell
AssigneeShak Hossain
Created2019-04-11T20:48:35.000+0000
Updated2019-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...

Comments

  1. Joshua Quick 2019-04-11

  2. Ryan Aston 2019-04-11

    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.
  3. Joshua Quick 2019-04-12

    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 and appcelerator.encrypteddatabase with and without transaction. The setting the top 2 variables isEncrypted and useTransactions to true/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.
       var isEncrypted = true;
       var useTransactions = true;
       var database = isEncrypted ? require("appcelerator.encrypteddatabase") : Ti.Database;
       if (isEncrypted) {
       	database.setPassword("password");
       }
       
       var dbConnection = null;
       try {
       	// Open the database file.
       	var dbName = isEncrypted ? "test_encrypted.db" : "test.db";
       	dbConnection = database.open(dbName);
       
       	// Drop the table created below if we're re-running the app.
       	dbConnection.execute("DROP TABLE IF EXISTS test;");
       
       	// Create the database table.
       	if (useTransactions) {
       		dbConnection.execute("BEGIN TRANSACTION;");
       	}
       	dbConnection.execute("CREATE TABLE test(id integer PRIMARY KEY, name TEXT);");
       	dbConnection.execute("CREATE INDEX test_name ON test(name);");
       	if (useTransactions) {
       		dbConnection.execute("COMMIT;");
       	}
       	
       	// Insert rows and measure performance.
       	Ti.API.info("@@@ DB inserts started.");
       	var startTime = new Date();
       	if (useTransactions) {
       		dbConnection.execute("BEGIN TRANSACTION;");
       	}
       	for (var index = 1; index <= 1000; index++) {
       		dbConnection.execute("INSERT INTO test(name) VALUES ('Row " + index + "');");
       	}
       	if (useTransactions) {
       		dbConnection.execute("COMMIT;");
       	}
       	Ti.API.info("@@@ DB insert duration: " + (new Date() - startTime) + " ms");
       } catch (err) {
       	// Database error occurred.
       	alert(err);
       	Ti.API.error(err);
       } finally {
       	// Close the database.
       	if (dbConnection) {
       		try {
       			dbConnection.close();
       		} catch (err) {
       			alert(err);
       			Ti.API.error(err);
       		}
       		dbConnection = null;
       	}
       }
       
  4. Ryan Aston 2019-04-12

    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.
  5. Joshua Quick 2019-04-12

    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.
  6. Ryan Aston 2019-04-12

    Again, we've already implemented those suggestions. We are still having massive performance issues with EncryptedDB.
  7. Joshua Quick 2019-04-13

    [~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.
  8. Nate Whitesell 2019-04-13

    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?
  9. Joshua Quick 2019-04-15

    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?
       dbConnection.execute("PRAGMA cipher_memory_security = OFF;");
       
  10. Nate Whitesell 2019-04-22

    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.
  11. Joshua Quick 2019-04-22

    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.

JSON Source