On 9/13/19, Jens Alfke <j...@mooseyard.com> wrote: > If I have a set of primary keys (let's say a few hundred) and need to fetch > data from the table rows with those keys, what's the fastest way to do so? > The options seem to be: > > (a) Execute "SELECT … FROM table WHERE key=?", once for each key. > (b) Execute "SELECT key, … FROM table WHERE key IN (…)", including all of > the key strings. > > If I do (a), I can pre-prepare the statement and save the overhead of > compilation. But SQLite has to go through the rest of its work (starting the > virtual machine, b-tree lookup, etc.) once for each key. > > If I do (b), SQLite has less setup work to do, and it could potentially > optimize the b-tree lookup. On the downside, I have to prepare a statement > every time since the RHS of an "IN" isn't substitutable. > > Does anyone have intuition or actual knowledge about which approach is > better? Or know of a 3rd better approach?
A third option is to use the carray-extension to create an IN query that is substitutable. https://www.sqlite.org/src/file/ext/misc/carray.c -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
