The problem is that you **cannot pass arrays** in SQLite :)
OK, lets backup a little. When you use *text substitution* in a WHERE-IN
clause, you're force to reparse each time, which prevents the use of
prepared statements. But then, the SQLite parser and query planner is fast.
Because SQLite does not have a native array-type you can bind, you
typically have to resort to work-arounds, as outlined by *anonymous*
above, i.e. the equivalent of a join with a table containing the RHS
of the WHERE-IN, be it a temp-table, or the carray-extension (which
is an *eponymous virtual table*)
If you changed your queries to read **WHERE ... IN (:1, :2, ..., :N)** you
can then use prepared statements, but a different one for each N, taking
more memory, and you're still parsing and planning many queries, and
need lots of binding now too. And apparently that's 30% slower :)
Your best bet is probably carray, since your uuids are in text form,
and carray supports strings. So pack your N text uuids, with the null-
terminator, into a block of memory (an std::string in C++ would do, can
have embedded nulls), and pass the address and size to carray. Pseudo-code:
```
std::string packed_uuids;
std::vector<std::string> uuids; // that you got from somewhere
packed_uuids.reserve(uuids.size() * 37); // 36 + 1 for null-terminator
for (const std::string& uuid : uuids) {
packed_uuids.append(uuid.c_str(), uuid.size() + 1); // Note +1 to include null-terminator
}
prepare("SELECT ... FROM ... WHERE .. IN carray(:1, :2)");
sqlite3_bind_pointer(stmt, 1, packed_uuids.c_str(), "carray");
sqlite3_bind_int(stmt, 2, static_cast<int>(uuids.size())); // NOT packed_uuids.size()
```
I've not used carray in code yet, but I believe the speudo-code above
might work. It at least represents my current understanding of its workings.
See also [](https://www.sqlite.org/bindptr.html) and [](https://www.sqlite.org/carray.html) of course.
You'll need a recent version of SQLite for bind-pointer, and carray compiled-in too of course.
If it works out, please share, and tells us if it's any faster. If not, use the more traditional
*fill-temp-table-then-join-with-it* work-around.