Re: JSON_EACH + recursive query = unexpected performance degradation
Jens Alfke <jens-mAcipnqRhZf2eFz/[email protected]>
| Newsgroups | gmane.comp.db.sqlite.general |
|---|---|
| Message-ID | <[email protected]> |
> On Feb 10, 2020, at 10:27 PM, Хусаинов Динар <[email protected]> wrote: > > 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). It sounds like SQLite is much faster at traversing real tables than it is at traversing JSON arrays. Which I would expect. This is exacerbated by the fact that the recursive CTE must be doing a lot of traversals of the JSON. You should be able to speed this up by creating temporary tables from the JSON first, and then changing the CTE to use those tables. —Jens _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users