Original query:
SELECT ... FROM mytable WHERE field_a IN (<list of values>)AND field_b IN (<list-of-values>) AND <key constraints>
This is sometimes (when mytable is a virtual table that offers to handle "field_a =" and "field_b =" constraints internallly) resolved as
- create table eph_a
- create table eph_b
- full table scan eph_a
- full table scan eph_b
- scan table mytable index #n
Target query:
WITH (...) SELECT ... FROM mytable LEFT JOIN cte_a ON (mytable.a = cte_a.a) LEFT JOIN cte_b ON (mytable.b = cte_b.b) WHERE <key constraints>
Which should resolve as
- create table eph_a
- create table eph_b
- scan table mytable index #n
- lookup mytable.a in eph_a
- lookup mytable.b in eph_b
The cost of a partial index scan is O(log n) to locate the first record and O(m) for retrieving m consecutive records.
The cost of a lookup in an ephemeral table is O(log n).
The cost of a full table scan for an ephemeral table is O(n)
The cost of the first query plan is therefore O(a * b * (m + log n)) or O(a *b *m) + O(a * b * log n)
The cost of the second query plan is only O(log n + m * (log a + log b)) or O((log a*b) * m) + O(log n)
This makes the second query plan much less costly for IN lists of 2 or more elements.
-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:sqlite-users-***@mailinglists.sqlite.org] Im Auftrag von Dominique Devienne
Gesendet: Donnerstag, 06. Dezember 2018 11:32
An: General Discussion of SQLite Database <sqlite-***@mailinglists.sqlite.org>
Betreff: Re: [sqlite] [EXTERNAL] Re: sqlite3_bind_text() and WHERE x IN (?)
Post by Hick GunterMaybe someone can come up with a CTE that works for this...
How so? I'm not following you. Ryan already provided a CTE to transform a string into values, but that involves string parsing, and is kinda ugly (no offence Ryan :) ), and unlikely to be that efficient relative to carray. The point is that binding is *scalar only* in SQLite.
If OTOH, we could write:
...prepare...(db, "select ... from t where c in (?)", ..., &stmt, ...); ...bind_array(stmt, 1, SQLITE_INTEGER, vec.size()); for (auto elem : vec) { ...bind_int(stmt, 1, elem); }
...bind_array would instantiate the same array the carray() extension supports.
Existing typed bind APIs would fill in that array, with usual SQLite conversions, error checking against declared array-type.
And ...prepare would transparently transform WHERE c IN (?) with WHERE c IN
(carray(?))
with the internally managed C array.
Of course, at prepare time, SQLite doesn't know yet ? is not a scalar as usual, so perhaps a different notation is necessary, like the TABLE() operator in Oracle SQL for example.
But given carray() and the existing infrastructure, I naively fail to see how the above wouldn't work.
My $0.02. --DD
[1] https://www.sqlite.org/c3ref/c_blob.html
_______________________________________________
sqlite-users mailing list
sqlite-***@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___________________________________________
Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0
May be privileged. May be confidential. Please delete if not the addressee.