Ayende @ Rahien

My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:


+972 52-548-6969

, @ Q c

Posts: 6,128 | Comments: 45,548

filter by tags archive

NH Prof AlertsExcessive number of rows returned

time to read 2 min | 357 words

This is a bit from the docs for NH Prof, which I am sharing in order to get some peer review.

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


This is a common mistake when you are binding to a UI component (such as a grid) that perform its own paging. This is a problem is 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 over the network
  • We have higher memory footprint than we should have
  • In extreme cases, we may crash as a result of out of memory exception

None of those are good things, and like the discussion on unbounded result sets, this can be easily prevented by applying a limit at the database level to the number of rows that we will load.

But it is not just simple queries without limit that can cause issue, another common source of this error is Cartesian product when using joins. Let us take a look at this query:

	.SetFetchMode("OrderLines", FetchMode.Join)
	.SetFetchMode("Snapshots", FetchMode.Join)

Assuming that 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 product doesn't tend to stop at 500, but escalate very quickly to ridiculous number of rows returned for 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 to several queries, and send them all to the database in a single batch using Multi Queries.

More posts in "NH Prof Alerts" series:

  1. (31 Dec 2008) Use statement batching
  2. (30 Dec 2008) Too many database calls per session
  3. (29 Dec 2008) Excessive number of rows returned
  4. (29 Dec 2008) Unbounded result set
  5. (28 Dec 2008) Use of implicit transactions is discouraged
  6. (28 Dec 2008) Select N + 1



well, assume you don't want to get just a plain list of Orders, but instead you add some restrictions for the orders, like all orders within the last 4 weeks.

So how do you get only the Snapshots and OrderLines needed for the orders of the last 4 weeks?

Ayende Rahien


Take a look at Session.CreateFilter

Comment preview

Comments have been closed on this topic.


  1. The worker pattern - 3 days from now

There are posts all the way to May 30, 2016


  1. The design of RavenDB 4.0 (14):
    26 May 2016 - The client side
  2. RavenDB 3.5 whirl wind tour (14):
    25 May 2016 - Got anything to declare, ya smuggler?
  3. Tasks for the new comer (2):
    15 Apr 2016 - Quartz.NET with RavenDB
  4. Code through the looking glass (5):
    18 Mar 2016 - And a linear search to rule them
  5. Find the bug (8):
    29 Feb 2016 - When you can't rely on your own identity
View all series



Main feed Feed Stats
Comments feed   Comments Feed Stats