Re: JSON_EACH + recursive query = unexpected performance degradation

The easiest way is to phrase the query such that the table is internally materialized.  In query1.sql the easiest way to do that is to change the:

WITH
------------------------build a in memory table with parent-child relations from 3 json arrays _which have the same size_
"objects_in_memory" AS (
   SELECT "id"."value"     AS "id",

to read like this:

WITH
------------------------build a in memory table with parent-child relations from 3 json arrays _which have the same size_
"objects_in_memory" AS (
   SELECT DISTINCT "id"."value"     AS "id",

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users <[email protected]> On
>Behalf Of ???????? ?????
>Sent: Monday, 10 February, 2020 23:28
>To: [email protected]
>Subject: [sqlite] JSON_EACH + recursive query = unexpected performance
>degradation
>
>I have a dataset of about 300 rows which have parent-child relations.
>Due to factors unrelated to the issue I build the rows by zipping JSON
>arrays with values from each column.
>Then I run a simplest recursive query on it to get the whole tree(ends
>up being ~4 levels).
>
>Problem: the query takes 3000 ms (3 seconds) on my machine to complete.
>If I create a real table with the SAME structure, insert the SAME data
>into it, and run the SAME query, get the SAME result back, it takes
>10-15 ms (200-300 TIMES faster).
>
>
>I attached both queries(don't require schema to run)




_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users