The performance of a prepare with repeated executes compared to a repeated prepare-execute is highly driver, database engine and platform dependent. You should carefully test and bendhmark before choosing.

The optimizer that fails over to a tabel scan for an IN clause should be regarded as underpowered. A usefull optimizer should be able to fiund out when it is beneficial to do a tabel sacn and when a repeated series of index/key lookups based on the query. If you areunfortunate enough to be hampered by an optimzer of this kind, then you have to carefully craft your queries to aid the optimizer (and yes, it might be useful to just fetch one value pr execute).

This is one of the main reasons that is it impossible to state anything about performance of queries.

One of the things that irks me most when reading about DBI in the monestary is that people assume that preparing a statement actually speeds things up. This is not always true. The Ingres optimizer does its work based on the values in the query, and can therefore not do its work before the values actually are given (in the execute call), so a prepare does not help very much here!

Otoh I am given to understand that Oracle and others do some optimization without knowing the datavalues.

In short: YMMV. But if you discover that the prepare execute loop is faster than the select with IN, then you should probably consider changing database vendor ;-) It should be faster to issue one large query rather than a series of small queries - give or take a bit.

BTW: Don't bug gbarr about the DBI documentation. The author is Tim Bunce