ColdFusion N+1 Query Problem With ORM: Detection, Hibernate Tuning, and Eager Loading

ColdFusion ORM is Hibernate under the hood, so the N+1 query problem — one query to load a list of parents, then N more queries to load each parent’s relationship — is really a Hibernate problem, and the durable fixes live at the Hibernate-tuning layer, not just in your CFML. You detect it by turning on logSQL (in this.ormSettings) and watching the same SELECT repeat once per parent; you diagnose whether it's lazy loading firing per-parent selects or eager loading issuing a secondary select per association; and you fix it by choosing the right Hibernate fetch strategy (join fetch to load parents and children in one outer-join query, batch fetching to collapse N selects into a few, or subselect), by tuning the ORM session so entities load and flush efficiently, and — Hibernate's own "completely different approach to N+1" — by enabling the second-level (secondary) cache so repeated relationship loads hit EHCache instead of the database. Eager loading is a tool, not a cure: naive eager loading over-fetches and can cause N+1 if you don't join-fetch. This guide goes deep on detection, Hibernate fetch and session tuning, the secondary cache, and using eager loading deliberately.
Read More