IDBCursor#__find can cause CPU peg in Safari's sqlite3 implementation

[pixelcort]

When using IDBCursor#__find as part of walking through many items (calling continue after each item), currently performance is limited by massive amounts of CPU work in Safari's sqlite3 implementation (which runs on a thread).

This results in much slower performance when using IndexedDBShim as compared to Chrome's native IDBCursor's continue method.

[pixelcort]

The calls to tx.executeSql inside IDBCursor#__find look like:

"SELECT * FROM  'by-sequence' WHERE  key  NOT NULL  ORDER BY  key LIMIT 1 OFFSET 359"

I'm not sure, but I think all these calls are pegging the sqlite3 thread in Safari.

[pixelcort]

Adding an SQL index for the key column on the table speeds this up a bunch:

CREATE INDEX 'by-sequence-key-temporary-index' on 'by-sequence' ('key');

Is it worth creating an index for each database the shim creates?

[axemclion]

The keys are marked as primary keys, and I though that it was already indexed .... looks like your experiments say that they are not. Are you able see an improved speed if an index is created for primary key column in every table ?

[pixelcort]

Very much so, especially for a large OFFSET with an ORDER BY. Without an ORDER BY a separate index doesn't seem to be necessary. I'm guessing that, even if there is some internal indexing on PRIMARY KEY, it's not used for ORDER BY.

I haven't tested using a cursor on an IDBIndex, but I wouldn't be surprised if those other columns would benefit from SQL indexes as well.

[axemclion]

In that case, I think its worth creating indexes for primary key and also for index columns.

However, still need to decide on indexes if we go the conventional way to have a separate table (like chrome and firefox) for indexes so that multi entry is supported, or continue this path. Any thoughts on that ?

[pixelcort]

No thoughts on multiEntry; I'm considering PouchDB which doesn't use IDBIndex at the moment anyways.

One other thought came to mind: is there a reason key is BLOB instead of TEXT? Not sure if that makes a difference when using ORDER BY or not... maybe I'll run some perf tests between them and see.

[pixelcort]

Okay I did a test where each table's key column is of type TEXT with a manual index:

CREATE TABLE 'by-sequence' (key TEXT , inc INTEGER PRIMARY KEY AUTOINCREMENT , value BLOB);
CREATE TABLE 'document-store' (key TEXT PRIMARY KEY , value BLOB, seq BLOB);
CREATE INDEX 'temporary-by-sequence-key' ON 'by-sequence' ('key');
CREATE INDEX 'temporary-document-store-key' ON 'document-store' ('key');

IDBCursor is extremely fast now. I am able to replicate two PouchDB idb databases where the source contains 1000 records in under 2 seconds in Safari now. Before adding the index and changing the key to TEXT it was taking over 10 minutes.

[JamesMessinger]

This should be fixed now (as of version 1.0.5). Please give it a try and let us know if you find any problems. Thanks!