Improving Read Performance (Primarily Android)

[jfspencer]

Here is link of two projects, one built with websql the other with sqlite. android(import into Android Studio) and iOS projects have been pre-compiled and are located in their respective platforms/[ios/android] folder for each project. Both projects were produced by ionic.

test hardware
iPhone 6 - iOS 8.3
Nexus 6 - Android 5.1

Results Summary:
Android Falls significantly behind iOS by several seconds in all tests. Android SQLite falls behind Android webSQL with a smaller margin. iOS shows SQLite is slightly faster than webSQL (awesome!).

Tests:

• 1: Read 1k docs in sequence
• 2: Read 1k docs in random order
• 3: Read 1k random docs in parallel groups of 10 - (this test is similar to a use case in the production app I am currently developing. )
• 4: Read 1k docs in sequence using pouch allDocs - (this test reflects a use case in the production app I am currently developing)

Test Results in ms:
1: iOS webSQL: 1755 -- iOS SQLite: 1748 -- Android webSQL: 5271 -- Android SQLite: 6665
iOS SQLite is 73% faster than Android SQLite
Android webSQL is 21% faster then Android SQLite

2: iOS webSQL: 1813 -- iOS SQLite: 1748 -- Android webSQL: 5316 -- Android SQLite: 7227
iOS SQLite is 76% faster than Android SQLite
Android webSQL is 26% faster then Android SQLite

3: iOS webSQL: 1862 -- iOS SQLite: 1768 -- Android webSQL: 6262 -- Android SQLite: 7980
iOS SQLite is 77% faster than Android SQLite
Android webSQL is 21% faster then Android SQLite

4: iOS webSQL: 51 -- iOS SQLite: 48 -- Android webSQL: 210 -- Android SQLite: 220
iOS SQLite is 78% faster than Android SQLite
Android webSQL is equal Android SQLite

I am personally interested in improving android performance. Hopefully getting it into the iOS performance range. After that I would be interested in further tuning both platforms. Are there any initial thoughts on where I should start?

[nolanlawson]

Probably this Java file.

I do seem to remember that, when I was testing this last summer, I found that our switch from using Android foreground threads (aka UI thread, aka application thread) to backgrounds threads actually caused a significantly decrease in performance. However, it was already slower than WebSQL at that point. :) Also the background thread has the advantage of not blocking UI interactions.

That being said, I'd be more concerned about the fact that this plugin is not passing the PouchDB test suite. :) I would first look there, and it's pretty easy to run: steps are described here.

[brodycj]

Thanks @jfspencer for your help so far-excellent work!

@nolanlawson, as I said in #255, the problems with the PouchDB test suite should really be my responsibility, and I will deal with it sometime early next week.

I am not surprised that the Android version is measured slower, due to some factors that I will describe sometime later.

[brodycj]

The Android version actually contains two different implementations as described in README.md:

• by default, using https://code.google.com/p/sqlite4java/, which includes its own build of sqlite3.c
• the androidDatabaseImplementation option can be used to select the built-in Android database classes, which may be needed in certain cases

I am assuming that using https://code.google.com/p/sqlite4java/ should not be any slower than using the built-in Android database classes, but we should measure to be sure.

At some point, we should measure the iOS version with the FTS3/FTS4/R-Tree compile-time options enabled. I do not expect much difference.

I also recommend we run some other major scenarios such as bulk writing and perhaps combination of reading and writing.

And don't forget to use profiling to measure where we should consider making improvements.

[brodycj]

Some things that can make the Android version slower than the iOS version:

• iOS version is written in Objective-C, which links directly to C to execute the sqlite functions. The Android version is written in Java, and uses JNI glue code to execute the sqlite functions.
• The Android version uses Java JSON; there is also GSON which is supposedly faster. I have reproduced memory problems with Java JSON (and GSON as well) in the past when handling large data sets, due to the number of object allocated to handle the JSON data. This may incur a significant performance penalty as well.

A couple ideas that could make the Android version faster:

• simplify the JSON SQL data interface between the Javascript part and the platform-specific part
• I made a very light-weight bridge between Java and sqlite C library in https://github.com/sqlg/SQLiteGlue but it is very poorly documented and may have some problems with newer versions of Android. Perhaps it could give us better performance as well.

[nolanlawson]

The Android version uses Java JSON; there is also GSON which is supposedly faster.

According to Instagram anyway, the fastest JSON serializer/desierializer on Android is their IG-JSON.

[jfspencer]

Thanks for this feedback, I will add write and read/write tests to the test app. I'll start on the JSON library first then. I'll compare GSON and IG-JSON libraries and see what affect they have.

Thanks!

[jfspencer]

So it looks like I had a whitelisting configuration issue that was silently failing the tests and made performance worse with in the android sqlite tests. here are the new baseline results.

Summary:
Android SQLite is 23% faster than webSQL when doing parallel batch(groups of 10) operations. webSQL is still 15% faster at ordered serial reads. Random serial reads and allDocs usage are now on par with one another.

Tests:
1: Read 1k docs in sequence
2: Read 1k docs in random order
3: Read 1k random docs in parallel groups of 10 - (this test is similar to a use case in the production app I am currently developing. )
4: Read 1k docs in sequence using pouch allDocs - (this test reflects a use case in the production app I am currently developing)

Test Results in ms:
1: iOS webSQL: 1755 -- iOS SQLite: 1748 -- Android webSQL: 5271 -- Android SQLite: 6235
iOS SQLite is 71% faster than Android SQLite
Android webSQL is 15% faster then Android SQLite

2: iOS webSQL: 1813 -- iOS SQLite: 1748 -- Android webSQL: 5316 -- Android SQLite: 5137
iOS SQLite is 64% faster than Android SQLite
Android webSQL is 3% slower then Android SQLite

3: iOS webSQL: 1862 -- iOS SQLite: 1768 -- Android webSQL: 6262 -- Android SQLite: 5072
iOS SQLite is 63% faster than Android SQLite
Android webSQL is 23% slower then Android SQLite

4: iOS webSQL: 51 -- iOS SQLite: 48 -- Android webSQL: 210 -- Android SQLite: 197
iOS SQLite is 74% faster than Android SQLite
Android webSQL is slightly slower than Android SQLite

having looked in to gson/ig-json/jackson I am going to start with jackson for two reasons. first, looking at benchmarks of projects that migrated from org.json to jackson saw 100% performance increase. and Second and almost more compelling is, jackson provides interoperability with org.json so the migration will be pretty smooth. Cordova has an override for execute that provides the raw string. If jackson proves to live up to these reports I'll open a an issue with cordova to see if they will swap out or add support for jackson so the entire bridge can be optimized, since they are using org.json as well.

[brodycj]

Looks like you copy-pasted the same results as before, or am I mistaken somehow?

[brodycj]

Also, you are welcome to explore JSON alternatives but I highly recommend you start with profiling to measure how significant the JSON processing time really is.

[jfspencer]

I did copy the test section and then updated the android SQLite numbers and associated percentage differences. I will do some tracing, thanks!

[brodycj]

I just updated the iOS version to build with support for FTS3/FTS4, column metadata, and R-Tree. I don't expect this to have a major effect but cannot say for sure.

[nolanlawson]

@jfspencer interesting results! I'd be interested to see a comparison of iOS WebSQL to iOS SQLite, though. iOS to Android is not really an apples-to-apples comparison. ;)