Extremely poor performance compared to indexeddb

[Chozzle]

I've been trying to use this adapter as recommended by Nolan's guide to prebuilt databases on pouchdb. It's horrible! I'm not sure if it's the adapter, cordovasql, or some other problem, but it uses 15%cpu on my app constantly (this might be from building views in the background I'm not sure) and is slow to get a document. Sometimes it fails to return a query promise. It makes my app unusable. Ive had to replicate the sql database to an standard indexeddb one and now it is snappy with only approximately 2% CPU usage. This is on a galaxy note 4.

[nolanlawson]

Yup, Cordova SQLite has poorer performance than IndexedDB, especially in scenarios like PouchDB's. The goal of this project is to provide native SQLite access to folks who absolutely need it; everyone else should use IndexedDB. I have some thoughts on this here: https://nolanlawson.com/2016/04/10/introducing-the-cordova-sqlite-plugin-2/

I would however be interested to know where you were using Cordova-sqlite-storage or cordova-plugin-sqlite-2 since you didn't specify.

[nolanlawson]

Also yeah if you are doing queries then you will be hit pretty hard by perf issues; keep in mind that Cordova has to send string-based messages back and forth between the WebView and the native context for just about every interaction between PouchDB and the database, and PouchDB builds the index via a lot of reading and writing. (This will be fixed with native secondary indexes, but we're only going to implement that for IDB via the "idb-next" project.)

[Chozzle]

Hey Nolan, thanks for the insight. I thought I would put this warning here for people so they try to avoid the sqlite adapter. I'm using cordova-plugin-sqlite-2. I assume the high CPU usage (and up to (500 mb of ram usage) was due to the indexing. But it never seemed to finish. I was building 2 different indexes on 6000 documents just emitting a small array.
By the way, thanks so much for all your work on pouchdb. My app would not have been possible without it. The

[nolanlawson]

Thanks for the feedback! I implemented cordova-plugin-sqlite-2 to be as performant as possible, so you're probably just hitting limits of the PouchDB design and limitations in how Cordova WebView communication works.

Another good strategy you can try: you can prebuild indexes as well as the main database. Just copy over the mrview-*.sqlite files. 😃

[Chozzle]

Interesting. I assume the indexes would also be copied over when I replicate to an indexedDB based PouchDB?

[nolanlawson]

Nope, indexes are not copied during replication. But you can copy them using the "prebuilt SQLite" strategy.

[Chozzle]

Ok thanks. I tried out the sqlite adapter again. It takes approproximately 2-3minutes to build the index. After its finished it's actually not too bad. However one query with a startkey and endkey on 2 keys (a string and an array) takes just a bit too long for a good user experience. it varies between 2 and 10 seconds. So I'm going to keep my strategy to replicate to and indexeddb pouchdb. I might try pouchdb.load() from a dump file to see if that is faster than the replication, which takes about minute

[nolanlawson]

Yeah I'm sorry, but IndexedDB just naturally has the best performance on Android. Hopefully you can find the best settings for your app. I worked really hard to make it as performant as possible, but some workloads are just very difficult to make performant.

You may want to try using Dexie for your index and PouchDB for your replication. PouchDB is optimized for replication but not for indexing.

[Chozzle]

Hi Nolan. I just tried db.load() from a dump file as per your prebuilt database guide and it completes in 10seconds, compared to 1 minute for a replication from sqlite db to indexedDB. Nice! That's a better welcome to my app. As an added bonus, the text file is half the size of the sqlite db.
I think at this stage I have to leave everything as sqlite for iPhone with the device storage space issues. I'm yet to test on an iPhone, but I'm getting the vibe that sqlite might be faster there than on Android.