[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

[sqlite] Possible caching issue between connections with specific inserts

Joe Mistachkin wrote: > > Thanks for the report. I'm looking into it now. > Hi Joe, I hope you are doing well. Did you have a chance to get any feedback about this case? Can you confirm that it is a bug or kind of expected behavior? If it is a bug, is there any chance that it will be fixed

Re: [sqlite] JSON_EACH + recursive query = unexpected performance degradation

On 2/12/20, David Raymond wrote: > Not necessarily related to the question itself, but how did the attachments > actually come through with this mail? Every single other person to try and > attach something to this list has had it stripped off. Sometimes Mailman asks me for approval for messages

Re: [sqlite] Optimizer limitation with partial indexes

Does moving the expr3 work? SELECT * FROM Table WHERE ((expr1 > val1 AND AND expr3) OR (expr2 > val2 AND expr3)) Wout. On Wed, Feb 12, 2020 at 12:09 AM Jens Alfke wrote: > I'm running into a problem with partial indexes; apparently the query > optimizer isn't smart enough. > > I currently

Re: [sqlite] JSON_EACH + recursive query = unexpected performance degradation

Not necessarily related to the question itself, but how did the attachments actually come through with this mail? Every single other person to try and attach something to this list has had it stripped off. -Original Message- From: sqlite-users On Behalf Of ? Sent:

Re: [sqlite] [EXTERNAL] Optimizer limitation with partial indexes

This is documented here https://sqlite.org/partialindex.html and here https://sqlite.org/queryplanner.html Specifically, SQLIte does not prove theorems in first-order logic. To have a chance of using the partial indices, you would need to have your query translator formulate (expr1>val1 AND

Re: [sqlite] 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"

Re: [sqlite] [EXTERNAL] Optimizer limitation with partial indexes

> On Feb 12, 2020, at 5:30 AM, Hick Gunter wrote: > > This is documented here https://sqlite.org/partialindex.html > and here > https://sqlite.org/queryplanner.html > > Specifically, SQLIte does not prove theorems

Re: [sqlite] JSON_EACH + recursive query = unexpected performance degradation

> On Feb 10, 2020, at 10:27 PM, Хусаинов Динар > 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

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

If the trouble comes from a big "IN()", an approach can be to pass all the values in a JSON array (one parameter) and use json_each in the query. This is completely safe vs SQL injection, and IME quite efficient. IME using JSON + json_each is also very efficient to fill temporary tables (indexed

[sqlite] Documentation inconsistency

From here: https://www.sqlite.org/faq.html#q5 > (16) Why does ROUND(9.95,1) return 9.9 instead of 10.0? Shouldn't 9.95 round > up? This example seems to be no longer valid. Although the explanation is still valid in general, the particular example “SELECT ROUND(9.95,1)” actually returns 10.0

Re: [sqlite] JSON_EACH + recursive query = unexpected performance degradation

On Feb 12, 2020, at 10:53 AM, Jens Alfke wrote: > > 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. Do you not get the same effect by using the new generated columns feature, only without the manual work of

[sqlite] Issue with sqlite3 / import / strictly numerical table names

It would appear to me that sqlite and sqlite3 are out-of-sync in respect of the handling of tables with strictly numerical table names - please see the following sqlite3 log demonstrating the problem:

Re: [sqlite] [EXTERNAL] Optimizer limitation with partial indexes

>Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im >Auftrag von Jens Alfke >> On Feb 12, 2020, at 5:30 AM, Hick Gunter wrote: >> >> This is documented here https://sqlite.org/partialindex.html >> and here >>