NHibernate N+1 selects on lazy collections: batch-size in mappings or query-level Fetch for diagnosis and fix

Question

SymptomA screen that loads a list of parent entities and touches a lazy child collection per row issues one SQL statement for the parents plus one per parent row, visible in NHibernate.SQL output as repeated near-identical selects. The goal is to confirm the bottleneck is query count rather than query cost, and then pick a fix.Constraints and uncertaintyNHibernate supports two documented approaches: setting batch-size on the collection mapping, which replaces per-item lazy loads with batched IN-clause loads, and per-query fetching via Fetch/FetchMany in LINQ or QueryOver, or HQL join fetch. The unresolved decision is scope: a global mapping change may over-fetch on screens that never touch the collection, while per-use-case fetches must be repeated everywhere. Join-fetching more than one collection also risks a cartesian explosion, and combining join fetch with SetFirstResult/SetMaxResults pagination can behave incorrectly depending on version and dialect. A second-level cache could mask the symptom during testing.QuestionsIs counting statements via NHibernate.SQL logging on a representative screen sufficient to confirm N+1, or is a dedicated integration test asserting statement count the more reliable baseline?For multiple lazy collections, should batch-size be the default over join fetch to avoid cartesian products?What batch-size value is a defensible starting point given database parameter limits?

Tasadduq Burney · Answer

NHibernate N+1 on Lazy Collections: Diagnosis and Fix

Q1: Confirming the N+1 pattern

NHibernate.SQL logging on a representative screen is sufficient to identify the N+1 pattern during development, but an integration test asserting ADO.NET ExecuteNonQuery call count is the more reliable baseline, especially when a second-level cache is active.

Q2: batch-size vs join-fetch for multiple lazy collections

For scenarios where many lazy collections may be accessed, mapping-level batch-size is the safer default to avoid Cartesian products from join-fetch; per-query Fetch/FetchMany or HQL join fetch is appropriate when the collection is always needed for a given use-case.

Q3: Defensible batch-size starting point

Start with a batch-size between 10 and 25. This stays well below typical database IN-list limits (MySQL 65535 bytes, Oracle 1000 items). Adjust only if cardinality is known and the database supports larger lists, always testing with data exceeding the chosen size.

If the collection size can exceed the batch value, verify that no System.Data.SqlClient.SqlException arises from IN-list size limits.

NHibernate N+1 selects on lazy collections: batch-size in mappings or query-level Fetch for diagnosis and fix

Symptom

Constraints and uncertainty

Questions

1 answer

NHibernate N+1 on Lazy Collections: Diagnosis and Fix

Q1: Confirming the N+1 pattern

Q2: batch-size vs join-fetch for multiple lazy collections

Q3: Defensible batch-size starting point

Your perspective belongs here.

0 question comments