Diagnosing and Fixing NHibernate Lazy‑Loading N+1 Select Issues

Recognizable Condition

The application shows a sudden rise in the number of SQL statements executed for a single use case. When loading a parent entity and iterating over its lazy‑loaded collection, the log reveals a pattern of one initial SELECT for the parent followed by many identical SELECT statements – one for each child – often proportional to the collection size. Response times increase noticeably as the collection grows.

Cause/Diagnostic Table

Symptom	Likely Cause
Repeated SELECT statements for the same entity type	Lazy loading triggered without eager fetch or batching
Mapping shows lazy="true" (default) and no fetch join	Missing fetch strategy in mapping or query
No batch‑size or second‑level cache configured	Each lazy load results in a separate database round‑trip

Ordered Checks

1. Verify the N+1 pattern in the SQL log – enable NHibernate SQL logging (see Verification section) and run the use case; count SELECT statements for the child entity.
2. Inspect the querying code – look for HQL, Criteria, or LINQ statements that load the parent but do not specify a fetch mode for the collection.
3. Review the mapping file – check the <bag>, <set>, or <list> element for the collection; note the lazy attribute and any fetch or batch-size settings.
4. Test with a small dataset – record the baseline query count, then apply a candidate fix and re‑measure.

Fixes Tied to Findings

1. Eager fetch via mapping

If the collection is almost always needed together with the parent, change the mapping to eagerly load it:

<bag name=\"OrderItems\" table=\"OrderItem\" lazy=\"false\">
    <key column=\"OrderId\" />
    <one-to-many class=\"OrderItem\" />
</bag>

2. Eager fetch at query time

Keep lazy loading in the mapping but override it for specific queries:

var orders = session.CreateQuery(\"from Order o left join fetch o.OrderItems\")
                    .List<Order>();

Or using the Criteria API:
var orders = session.CreateCriteria(typeof(Order))
                    .SetFetchMode(\"OrderItems\", FetchMode.Eager)
                    .List<Order>();

3. Batch‑size loading
When eager loading would pull too much data, configure a batch size to reduce the number of round‑trips:
<bag name=\"OrderItems\" table=\"OrderItem\" lazy=\"true\" batch-size=\"20\">
    <key column=\"OrderId\" />
    <one-to-many class=\"OrderItem\" />
</bag>

4. Subselect loading
For collections where a single subselect can fetch all children efficiently:
<bag name=\"OrderItems\" table=\"OrderItem\" lazy=\"true\" subselect=\"where OrderId in (select Id from Order where ...)\">
    <key column=\"OrderId\" />
    <one-to-many class=\"OrderItem\" />
</bag>

5. Second‑level cache (read‑only data)
If the collection data is mostly static, enable NHibernate’s second‑level cache for the entity or collection region.
Escalation Criteria

After applying eager fetch, batch‑size, or subselect loading, the SELECT count remains high (e.g., still proportional to the number of parents).
Memory usage spikes noticeably when switching to eager loading.
The domain model requires complex graphs that are better served by DTOs or projection queries.

In these cases, consider:

Rewriting the use case to use a projection (e.g., select new OrderDto(o.Id, o.Date, ...)) that avoids loading the collection altogether.
Using a stateless session for bulk read‑only scenarios.
Involving the performance‑testing team to validate changes under realistic load.

Verification Steps

Configure log4net (or another logger) to output NHibernate SQL at DEBUG level:
<logger name=\"NHibernate.SQL\">
    <level value=\"DEBUG\" />
</logger>

Run a test that loads a single parent with its collection (e.g., an Order with its OrderItems).
Count the number of SELECT statements for the OrderItem table in the log.
Apply a fix (e.g., add lazy=\"false\" or a fetch join) and repeat the test.
Confirm that the SELECT count drops to the expected number (typically 1 for the parent plus 0 or 1 for the collection, depending on the strategy).
Optionally, use a profiling tool such as NHProf or SQL Server Profiler to verify that no extra batches appear.

Limitations and Practical Check
Eager fetching can increase the size of the result set and memory footprint; monitor memory usage (e.g., via a memory profiler) when changing lazy to false. Batch‑size reduces round‑trips but still issues multiple SELECTs; ensure the chosen size balances network latency against memory consumption. Second‑level cache introduces consistency concerns; verify that cache regions are correctly invalidated when the underlying data changes.
A practical way to check the result after a change is to run the same use case under a load‑testing tool (e.g., Visual Studio Load Test or JMeter) and assert that the average response time improves while the SQL statement count stays within the expected bound.