The First Rule of Program Optimization: Don't do it.
The Second Rule of Program Optimization - For experts only: Don't do it yet.
Michael A. Jackson
...premature optimization is the root of all evil.
Donald Knuth
I'm writing software that, in its barest essence, manipulates lists of files. Some of those lists are long, and sometimes they need to be sorted. Most of the properties they need to be sorted on are stored in a database. Thus, the records referring to the documents in question need to be loaded from the database.
I don't like databases. To paraphrase an unknown person - when faced with a data storage problem, the server applications programmer instinctively reaches for a database; now he has two problems. Therefore, I like to wrap them up, often in multiple layers - in this case the loading of document details is done through a document database interface. For some reason, when I wrote this and its SQL implementation (which itself does very little direct database access), I didn't offer a way to load multiple documents at once.
So, when it came to making the control that sorts a list of files work, loading details took far too long - four seconds or so for the results of a search with 14,000 hits. I needed to write that function to load multiple documents at once, so I added one that built a long SQL IN clause, assuming that it would be fast enough and I could do the standard trick of using a few prepared statements with various length lists of parameters in their IN clause later.
It took 14 seconds to load the same set of documents. One query was taking close to four times as long as 14,000 queries to get the same information. Worse, there was no particular reason why I hadn’t simply skipped the step of running 14,000 separate queries - it's blatantly stupid. For once, I hadn't prematurely optimised.
Luckily, databases offer an awkward Swiss army knife in their ability to create temporary tables and use them in joins. Joe Celko dislikes these because they turn a set processing system into a batch processing one, and I agree. They also tend to come with a long list of caveats attached. In this case, creating a table, dumping 14,000 identifiers into it using a batched insert and using it in an INNER JOIN to filter a query takes less than a second, so it's what gets done.