Your statements are so misleading I have to respond. Making absolute statements about performance without knowing the database schema and target platform is unwise, because you're just guessing without facts. But beyond that, even your generalizations are wrong.

The point behind a prepared statement is that the optimizer can plan the query, so subsequent invocations can run much faster, without having to re-compile and re-plan it. In the best case, you have a point query (e.g., one where you're using a unique index) so that re-executing the prepared statement only involves searching the index tree again; if the query spans the index, there's a good chance the index will stay in the server's cache, and subsequent executions won't even go to the disk. An IN clause on most platforms is planned as a series of OR clauses, which, depending on the number of targets and the engine, may result in the optimizer just giving up and scanning the table instead of using an index.

Obviously, one scan is better than multiples.

That's not comparing good to bad, that's comparing bad to worse. In general, table scans, except for very small tables, are the slowest way to retrieve data from a database.

I think what you're trying to say with your statement about "forcing multiples by putting the loop in perl" is that you should let the engine do as much server-side processing as possible. That's correct; the problem is when a host program retrieves a result set, then uses it as parameters to the next query. In such cases, yes, what you want to do is leave the result set in the database (a temporary table if you have to) rather than bring it to the client only for the purpose of sending it back to the server. However, I don't know the context of the original quey, nor where the values for the it are coming from.

To file a prepare-execute loop under "Inadvisable Ways to do Database Programming", write gbarr DBI maintainers and tell them to change the DBI documentation. This is the standard way to retrieve rows with DBI.

Finally, this statement

likely to get you the best performance since it gives the database engine everything it needs all at once

is just nonsense. There are so many other factors that are more important. Does the database support prepare_cached at all? Is there an index on that column? Is the table partitioned? Are you going across a WAN to reach the database? And so on.

I'll don my database hat here for a moment. I'm no expert on databases...

People turn to the Monastery for advice, and you could easily lead someone astray. Please don't guess when you know you could be guessing wrong.

Update

• Deleted incorrect Graham Barr reference.
• rdfield and htoug bring up excellent points below, the main one being what I was trying to say: You can't make absolute statements about performance without knowing specifics.

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.