14

Can anyone point me to an article on, or preferably provide some experience of performance of IndexedDB (ideally in Chrome) - what is the fetch, insert and update performance like?

There seems to be reasonable amount of opinion that its pretty much unusable for data sets of more than a few thousand records but I'm not sure if this isnt just due to a lack of indexing - surely conceptually it cant be slower than web storage as both presumably use key-value storage internally?

Thanks

Sidebp
  • 770
  • 1
  • 10
  • 26
  • This is an interesting question. I'll do some testing in the next few weeks and post an update here when I've got some answers. – buley Dec 03 '11 at 02:22

4 Answers4

13

I recently did some performance comparisons between WebSQL and IndexedDB. Surprisingly, IndexedDB won (which I wasn't expecting).

http://blog.oharagroup.net/post/16394604653/a-performance-comparison-websql-vs-indexeddb

Edit: the above URL is down, but available on archive.org: http://web.archive.org/web/20160418233232/http://blog.oharagroup.net/post/16394604653/a-performance-comparison-websql-vs-indexeddb

In summary:

WebSQL takes on average between ~750-850ms to complete the query and render the results; and IndexedDB takes on average ~300-350ms to render the exact same results.

Abhi Beckert
  • 32,787
  • 12
  • 83
  • 110
Scott
  • 1,518
  • 1
  • 15
  • 23
  • This is a good comparison between web sql and indexed db. Can we have performance matrix of indexed db with respect to large amount of data? – Juzer Ali Mar 22 '12 at 07:24
  • Very thorough response. Thanks. – Sidebp Apr 11 '12 at 19:59
  • @Scott: I note that you recently changed the query you used for testing so that it no longer uses rowid. Did you redo the tests — with indexes — after this change? I suggest you have an index on every key column (`Program(ProgramID)`, `Series(SeriesID)` and `Episode(EpisodeID)`). You can use [EXPLAIN QUERY PLAN](http://www.sqlite.org/eqp.html) to see whether SQLite is making appropriate use of indexes. – Marcelo Cantos Jun 26 '12 at 11:25
  • 1
    I am afraid the page is not available anymore. Could you please fix it? – Sergei Basharov Nov 15 '12 at 08:39
  • could you make a jsperf example that would be easier to test and understand – markasoftware Nov 28 '13 at 04:46
11

The only performance write-up I've seen is the one produced by @Scott (author of the other answer to this question). Unfortunately, his article doesn't do Web SQL Database justice, since it uses an inefficient HAVING clause to restrict the size of the result set. I tweaked Scott's SQL, replacing HAVING, GROUP BY and LEFT JOIN with (almost) equivalent WHERE and sub-selects:

SELECT p.Name AS ProgramName,
       s.rowid,
       s.Name,
       s.NowShowing,
       s.ProgramID,
       (SELECT COUNT(*) FROM Episode WHERE SeriesID = s.rowid AND STATUS IN ('Watched', 'Recorded', 'Expected') OR STATUS IS NULL) AS EpisodeCount,
       (SELECT COUNT(*) FROM Episode WHERE SeriesID = s.rowid AND STATUS = 'Watched') AS WatchedCount,
       (SELECT COUNT(*) FROM Episode WHERE SeriesID = s.rowid AND STATUS = 'Recorded') AS RecordedCount,
       (SELECT COUNT(*) FROM Episode WHERE SeriesID = s.rowid AND STATUS = 'Expected') AS ExpectedCount
  FROM Program p
  JOIN Series s ON p.rowid = s.ProgramID
 WHERE s.NowShowing IS NOT NULL OR
       EXISTS (SELECT * FROM Episode WHERE SeriesID = s.rowid AND STATUS IN ('Recorded', 'Expected'))
ORDER BY CASE
           WHEN s.NowShowing IS NULL THEN 1
           ELSE 0
         END,
         s.NowShowing,
         p.Name

This is about 28 times faster than the original — 20 ms vs 560 ms on my computer — which, by extrapolation from Scott's numbers, makes it about 10 times faster than the equivalent IndexedDB. I wasn't able to confirm this because the IndexedDB code doesn't work in my browser, seemingly due to API changes.

I should explain the "(almost)" I wrote above. Scott's original SQL and mine have subtly different meanings: a gratuitous WHERE clause on my EpisodeCount — which has the effect of replacing a table scan with an index search — may fail to count some episodes if it doesn't cover all possible Status values. Removing this clause erases the difference at the expense of doubling execution time to 40 ms.

Note that, earlier, I discussed with Scott a smaller change to his SQL that also achieves a 40 ms time.

UPDATE: Many thanks to Scott for updating his article to acknowledge the discussion we had.

Community
  • 1
  • 1
Marcelo Cantos
  • 181,030
  • 38
  • 327
  • 365
2

Doing some performance comparison between IndexeDB and other client side and server side databases. The performance depends of the browser as Firefox implementation of IndexeDB API is much more ahead than Chrome or IE. Firefox is using SQLlite as a backend database, so IndexedDB is implemented on the top of it. You can find many articles of IndexedDB performance, but mostly reserches and developers are saying that IDB perform faster with SQL as a backend. Comparing to Chrome implementation where IDB is implemented on the top of LevelDB(which is NOSQL) is much more slower comparing to Firefox. On the another end WEBSQL(depreciated) is performing fast in Chrome, in Firefox not supported anymore.

I have published a paper with some IndexedDB performance results. https://www.researchgate.net/profile/Stefan_Kimak/publication/281065948_Performance_Testing_and_Comparison_of_Client_Side_Databases_Versus_Server_Side/links/55d3465c08ae0a3417226302/Performance-Testing-and-Comparison-of-Client-Side-Databases-Versus-Server-Side.pdf

stefan
  • 175
  • 1
  • 10
1

I had problems with massive bulk insert (100.000 - 200.000 records). I've solved all my IndexedDB performance problems using Dexie library. It has this important feature:

Dexie has a kick-ass performance. It's bulk methods take advantage of a not well known feature in indexedDB that makes it possible to store stuff without listening to every onsuccess event. This speeds up the performance to a maximum.

Dexie: https://github.com/dfahlander/Dexie.js

BulkPut() -> http://dexie.org/docs/Table/Table.bulkPut()

Francesco
  • 1,128
  • 12
  • 22
  • 2
    I have published a paper with some performance results. https://www.researchgate.net/profile/Stefan_Kimak/publication/281065948_Performance_Testing_and_Comparison_of_Client_Side_Databases_Versus_Server_Side/links/55d3465c08ae0a3417226302/Performance-Testing-and-Comparison-of-Client-Side-Databases-Versus-Server-Side.pdf – stefan Sep 19 '17 at 09:36