Alasql: Performance issues at AlaSQL database queries - response time of several minutes

[Pasc95]

Hello,

I am using AlaSQL in a Node.js application.

What I do in build time:

1. load JSON files into JSON objects.
2. create database with 3 tables based on that files.
3. fill the table with the JSON objects
   The total amount of data sets is about 2500.

What I do in runtime:
AlaSQL queries, which need to be synchronous and have 2 joins, so all 3 tables are included.

Issue:
When I query the data I get a response time of several minutes. Does anyone know how to fix that or how to do it better?

What I tried:

• adding primary key (index) to data sets
• reducing data
  Unfortunately did not work

Thank you in advance.

[mathiasrw]

There is something about the index not working when loading data directly. hmmm.

Can you try to load data via SQL and see if it works better?

Can you share the SQL to see what the structure is like?

[freyley]

I'm exploring performance with AlaSQL and not seeing much evidence that indexes do anything. I created a database with 256k records and attempted to query for a sorted subset, only to find it takes 500-1300ms to do so, and indexes do not seem to have any affect on this. I created a codepen - https://codepen.io/freyley/pen/GRREdom

[mathiasrw]

Hmm. Iteresting to see if the speed tricks is disabled for sorted subsets (it should not)

If you could make an exaple of a query that ou find strangly slow it would really be awesome.

[freyley]

@mathiasrw take a look at the codepen above, it was, last October, in the console, outputting its times to do pretty simple queries.

[tran4o]

I have the same issue and have very simple example to show how indexes are really not useful for any real nested query with simple joins :

alasql("CREATE TABLE test (id number, child number)");
for (var i=0;i<100000;i++)
alasql("INSERT INTO test VALUES ("+i+","+Math.floor(Math.random()*250)+")");
alasql("CREATE INDEX test_1 ON test(id)");
alasql("CREATE INDEX test_2 ON test(child)");

//---------------
alasql("SELECT a.id,b.id FROM test AS a JOIN test AS b ON (a.id = b.id) WHERE a.id = 10")
// 3+ seconds, neither 'Joined tables are pre-indexed' nor 'WHERE expressions are pre-filtered for joins'

// 3+ seconds, same result
alasql("SELECT a.id,b.id FROM test AS a JOIN test AS b ON (a.id = b.id)")

[tran4o]

I found the problem in the implementation in this case.
Following sql does works as expected also without indexes in some milliseconds :

alasql("SELECT a.id,b.id FROM test AS a JOIN test AS b ON a.id = b.id")

does not work :

alasql("SELECT a.id,b.id FROM test AS a JOIN test AS b ON (a.id = b.id)")

the problem seems to be in src/421join.js:312
the optimizer expects direct join condition, without dummy UniOp node

https://imgur.com/a/IzldRHU