On Tuesday, 4 February, 2020 12:14, Deon Brewis <de...@outlook.com> wrote:
>WHERE x IN (?1,?2,?3,?4...,?1000 ) That would be a really silly construct to use. Why are you bothering to name all the parameters? Anonymous parameters are merely an array of pointers to values. When you give the parameters names then a linkage between the "name" and the "position" needs to be kept, as well as a hash table so that the "name" can be looked up. When you refer to duplicate anonymous parameters you have to use a name for the one of them that is not the next anonymous parameter in line, but sheesh, naming them all? Why? >And the IN clause is filled by a list or array that's held inside the >calling application memory rather than in SQLITE. The VDBE program still needs an array of pointers for all used parameter range (from 1 to the highest parameter used). That means that if you use something like: select ?, ?1000000; then the VDBE program will allocate an array to hold 1000000 parameter pointers (assuming that number of parameters were allowed). And bind parameters are only stored in application memory if they are TEXT or BLOB type and you make the bind call providing a de-allocator (ie, not SQLITE_TRANSIENT) and even then if and only if no conversions need to be performed (for example from you external encoding to the internal database encoding). >The alternate to this is to create a virtual table wrapper over the >internal datasets of the app. Which is of course better, but harder. (We >need an STL for SQLite. SqliteTL?). see the carray extension ... >PS: Doesn't SQLITE internally order an IN list and do a join across it? >It seems to perform better than I would expect from a flat array. Sort of. When you do an IN (?,?,?,?) or IN (value, value, value ...) the values or parameters are loaded one after each into a without rowid table (effectively, that looks like "create temporary table temptable (variable primary key variable) without rowid") and then the table.variable IN (...list...) is treated as a "table JOIN temptable ON table.variable == temptable.variable". NULLS in the IN list are silently discarded of course since they cannot be used with IN (which is defined as == not IS for each in turn). -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
