Alasql vs WebSQL perf test - actually store data?

[nolanlawson]

Hey, really cool library. I tried out the Alasql vs WebSQL perf test, though, and I was kinda surprised to see that it didn't seem to be actually storing anything in LocalStorage/IndexedDB/anywhere.

So apparently it's using the in-memory version of the database? If so, the test is a little misleading, because WebSQL is actually writing stuff to disk (and it's asynchronous), whereas Alasql is just doing everything in memory.

[agershun]

This is absolutely true. Initally Alasql was designed as "in-memory" database, and it was compared just on standard set of operation in memory. For some people (like me), this is was the point - to have fast sorting and groupng procedures on the client side (even without storage operations). And of course, like any other tests, this test is applicable to very specific situation, defined with a number of SQL statements, described in the test.

To do not mislead anyone in future, I will mention this fact in README.md file. Thank you!

Some more facts about Alasql and WebSQL:

1. Actually, WebSQL also load data in memory and works with them on "in-memory" basis (e.g. I use SSD for disk), so it should not not affect too much on the results.
2. Alasql also has async interface (and the libraryr use it for file storage operation)
3. Even WebSQL is written and is compiled with C++ (and it should be faster by default), Alasql (written on JavaScript) has some optimization technichs, and in some cases win over WebSQL - at least the test in jsPerf has no any "tricks" except JOIN and WHERE optimization and SQL-statement precompilation (which are "fair" IMHO).
4. Now I am in the process of development filestorage for Alasql. I do not think it will be fast, because HTML file storage operations are slow in JavaScript, but I am going to prepare extened tests to perform all operations including loading and saving data in file.

[agershun]

I modified the test to allow the both databases work pure in memory (test compares only SELECT statement) and remove any caching functionality (i.e. cache for precompiled SELECT statements and preliminary prepared indexes):

http://jsperf.com/alasql-js-vs-websql/5

[nolanlawson]

Personally I'd be more interested in seeing how it works when it's actually writing things to disk, especially given that (in PouchDB anyway) the big bottleneck is always writes rather than reads. But thanks for making it clearer what's going on! :)

[agershun]

Here s one more test with preindexed WebSQL database:

http://jsperf.com/alasql-js-vs-websql/7

Could you share or send a link to PouchDB tests to make comparable tests for the same data sets?

[nolanlawson]

Sure, our performance docs are here: https://github.com/pouchdb/pouchdb/blob/master/TESTING.md#performance-tests

Unfortunately we don't have any JSPerf tests set up, so you have to run them manually. (At the time we wrote our tests, JSPerf still didn't support async tests.) JSPerf tests are definitely on my TODO list, though. 😛

[agershun]

Thank you for the tests!

What do I think: I can write adapter between Alasql and PouchDB, so users
can use strong side of the both databases: Storage engine of PouchDB and
SELECT/JOIN engine of Alasql. I will write the prototype and send it to you
on this week.

It will looks like:

alasql('ATTACH POUCHDB DATABASE one; USE one; SELECT * FROM table_1 JOIN
table_2;DETACH DATABASE one;',[],function(res){
console.log(res[3[);
});

This disk operations are the bottleneck of HTML/JS, and PouchDB has a
significant advantage on it and much more well developed then current state
of Alasql.

Andrey

2015-01-26 19:41 GMT+03:00 Nolan Lawson notifications@github.com:

Sure, our performance docs are here:
https://github.com/pouchdb/pouchdb/blob/master/TESTING.md#performance-tests

Unfortunately we don't have any JSPerf tests set up, so you have to run
them manually. (At the time we wrote our tests, JSPerf still didn't support
async tests.) JSPerf tests are definitely on my TODO list, though. [image:
😛]

—
Reply to this email directly or view it on GitHub
#47 (comment).

[agershun]

BTW: this jsPerf test it async.
http://jsperf.com/alasql-js-vs-websql/7

Try to edit it and you will see the async flag. Then add
deferred.resolve()__; to finish the test.

2015-01-26 20:26 GMT+03:00 Andrey Gershun agershun@gmail.com:

Thank you for the tests!

What do I think: I can write adapter between Alasql and PouchDB, so users
can use strong side of the both databases: Storage engine of PouchDB and
SELECT/JOIN engine of Alasql. I will write the prototype and send it to you
on this week.

It will looks like:

alasql('ATTACH POUCHDB DATABASE one; USE one; SELECT * FROM table_1 JOIN
table_2;DETACH DATABASE one;',[],function(res){
console.log(res[3[);
});

This disk operations are the bottleneck of HTML/JS, and PouchDB has a
significant advantage on it and much more well developed then current state
of Alasql.

Andrey

2015-01-26 19:41 GMT+03:00 Nolan Lawson notifications@github.com:

Sure, our performance docs are here:
https://github.com/pouchdb/pouchdb/blob/master/TESTING.md#performance-tests

Unfortunately we don't have any JSPerf tests set up, so you have to run
them manually. (At the time we wrote our tests, JSPerf still didn't support
async tests.) JSPerf tests are definitely on my TODO list, though. [image:
😛]

—
Reply to this email directly or view it on GitHub
#47 (comment).

[agershun]

I added comments to the test in README.md

[mathiasrw]

Did you ever got further into the PouchDB test idea?

[agershun]

Not yet. But thank you for reminding.
Anyway all standard database performance test more or less are the same: 1000 inserts/1000 deletes/1000 updates/some selects (see this).

[githorse]

Any chance anyone has a performance comparison between Alasql and IndexedDB to share?

[mathiasrw]

Really? On a 9 year old issue?