1047818 - Avoid large IN clauses

Status: NEW

(Toolkit :: Places, defect, P3)

Description

[Marco Bonardo [:mak]]

we have some large IN clauses, they are very unefficient and cause of jank
we could either implement array binding in Storage or use temp tables.

Comment 1

[David Teller [:Yoric] - still alive but not very active]

As far as I can tell, there is no function for array binding in sqlite3, right?

Also, I don't see how a temporary table would be faster than a large IN clause (assuming that the IN row is indexed).

Comment 2

[Marco Bonardo [:mak]]

there is array binding, we didn't implement it. it's in a separate extension though, so it wouldn't work with out support of system sqlite.

we can ask support if they improved IN, btw, just the fact you might have a giant string to parse is a perf issue by itself.

Comment 3

[Marco Bonardo [:mak]]

or we can ask to put array binding in the core product.

Comment 4

[David Teller [:Yoric] - still alive but not very active]

I believe that having array binding would be good even without performance concern, just for the sake of safety, so I would vote for getting array binding. I have no clear opinion on whether it should be in the core product or we should adopt an extension.

Comment 6

[Marco Bonardo [:mak]]

We should first fix bug 483318.
We can also use the Slow SQL telemetry to measure how common slow queries are using large IN clauses, and determine a priority for this work.