NH Prof AlertsUnbounded result set
This is a bit from the docs for NH Prof, which I am sharing in order to get some peer review.
Unbounded result set is perform a query without explicitly limiting the number of returned result (using SetMaxResults() with NHibernate, or using TOP or LIMIT clauses in the SQL). Usually, this means that the application is assuming that a query will only return a few records. That works well in development and testing, but it is a time bomb in production.
The query suddenly starts returning thousands upon thousands of rows and in some cases, it is returning millions of rows. This leads to more load on the database server, the application server and the network. In many cases, it can grind the entire system to a halt, usually ending with the application servers crashing with out of memory errors.
Here is one example of a query that will trigger unbounded result set warning:
session.CreateQuery("from OrderLines lines where lines.Order.Id = :id") .SetParameter("id", orderId) .List();
If the order have many line items, we are going to load all of them, which is probably not what we intended. A very easy fix for this issue is to add pagination:
session.CreateQuery("from OrderLines lines where lines.Order.Id = :id") .SetParameter("id", orderId) .SetFirstResult(0) .SetMaxResult(25) .List();
Now we are assured that we need to only handle a predictable number, and if we need to work with all of them, we can page through the records as needed. But there is another common occurrence of unbounded result set, directly traversing the object graph, as in this example:
var order = session.Get<Order>(orderId); DoSomethingWithOrderLines(order.OrderLines);
Here, again, we are loading the entire set (in fact, it is identical to the query we issued before) without regard to how big it is. NHibernate does provide robust handling of this scenario, using filters.
var order = session.Get<Order>(orderId); var orderLines = session.CreateFilter(order.OrderLines, "") .SetFirstResult(0) .SetMaxResults(25) .List(); DoSomethingWithOrderLines(orderLines);
This allow us to page through a collection very easily, and save us from having to deal with unbounded result sets and their consequences.