Query Count as the Real Design Smell

A batch job that assembles documents from a relational database was taking hours to run. The obvious suspect was a missing index. Surprise, It wasn't.

The bottleneck wasn't a slow query — it was dozens of separate round trips per record, several of them were re-fetching data another query in the same job had already pulled. Query count, not query speed, was the design smell worth chasing first.

Adding a profiling pass on one document type found over thirty distinct database calls per record proved that almost all of them fast individually. The wall-clock cost was pure round-trip overhead, multiplied across tens of thousands of records. A few patterns kept recurring:

• Redundant re-fetching. The same lookup table got queried multiple times per record because different parts of the code didn't know about each other's needs.
• Sequential independent work. Queries that had no dependency on each other still ran one after another, because nothing coordinated them to run at the same time.
• Data that could be derived, not re-fetched. Some "extra" queries existed only to pull a value that could be computed directly in memory.

Fixing all three — batching duplicate lookups, running independent queries concurrently, and deriving what could be derived — cut the query count for that document type by two-thirds and turned a multi-hour run into well under an hour. The same 3-pattern checklist, applied to a much heavier, CTE-based query for a different part of the system, produced a similar multiple-x improvement once it was broken into smaller queries that could run in parallel.

Consider a job that, per record, separately queries "get the author," "get the translated title," and "get the backup title" — except the backup title is just the translated title with formatting stripped, computable in application code from data already fetched. That's one query removed for free, with zero risk, before touching anything harder.

Remember: before optimizing a slow query, count how many queries you're actually running per unit of work — the fix is often "stop asking twice," not "ask faster."