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 <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of ???????? ????? >Sent: Monday, 10 February, 2020 23:28 >To: sqlite-users@mailinglists.sqlite.org >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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
