SQLite Forum

Performance issues with query parameters vs. static strings
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.