SQL: Loop single select vs one select with IN clause

Question

I'd like to ask you what is faster if use

1. Loop array and call select XXX where id=

2. Call select XXX where id IN (list value of array)

Answer 1

The second one is almost always faster. Remember that in the first option, the client (usually) has to do a full database connection, log in, send the query, wait for the query to get parsed, wait for the query to get optimized, wait for the query to execute and then wait for the result to get back. In the second option, all of these steps are done once.

There may be cases where the first option is actually faster if your index-schema is bad and can't be fixed or the server is seriously wrong about how to run the disjunction that is the IN-clause and can't be told otherwise.