The excessive number of rows returned warning is generated from the profiler when...a query is returning a large number of rows. The simplest scenario is that we loaded all the rows in a large table, using something like the following code snippet:

session.CreateCriteria(typeof(Order))
    .List();

This is a common mistake when you are binding to a UI component (such as a grid) that performs its own paging. This is a problem on several levels:

• We tend to want to see only part of the data
• We just loaded a whole lot of unnecessary data
• We are sending more data than necessary over the network
• We have a higher memory footprint than we should
• In extreme cases, we may crash as a result of an out of memory exception

None of these are good, and like the discussion on unbounded result sets, this problem can be easily prevented by applying a limit at the database level to the number of rows that we want to load at any given time.

But it is not just simple queries without limit that can cause this issue. Another common source of this error is the Cartesian product when using joins. Check out this query:

session.CreateCriteria(typeof(Order))
    .SetFetchMode("OrderLines", FetchMode.Join)
    .SetFetchMode("Snapshots", FetchMode.Join)
    .List();

Assuming we have ten orders, with ten order lines each and five snapshots each, we are going to load 500 rows from the database. Mostly, they will contain duplicate data that we already have, and NHibernate will reduce the duplication to the appropriate object graph.

The problem is that we still loaded too much data, with the same issues as before. Now, we also have the problem that Cartesian products don't tend to stop at 500, but escalate very quickly to ridiculous numbers of rows returned for a trivial amount of data that we actually want.

The solution for this issue is to change the way we query the data. Instead of issuing a single query with several joins, we can split this into several queries, and send them all to the database in a single batch using Multi Query or Multi Criteria.