Unbounded result set

An unbounded result set is where a query is performed and does not explicitly limit the number of returned results using SetMaxResults() with NHibernate, or TOP or LIMIT clauses in the SQL. Usually, this means that the application assumes that a query will always return only a few records. That works well in development and in testing, but it is a time bomb waiting to explode in production.

The query may suddenly start returning thousands upon thousands of rows, and in some cases, it may return 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 the unbounded result set warning:

session.CreateQuery("from OrderLines lines where lines.Order.Id = :id")
.SetParameter("id", orderId)
.List();

If the order has many line items, we are going to load all of them, which is probably not what was intended. This can be fixed fairly easily by using pagination:

session.CreateQuery("from OrderLines lines where lines.Order.Id = :id")
.SetParameter("id", orderId)
.SetFirstResult(0)
.SetMaxResults(25)
.List();

Now we are assured that we only need to handle a predictable, small result set, 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 the unbounded result set problem from directly traversing the object graph, as in the following example:

var order = session.Get(orderId);
DoSomethingWithOrderLines(order.OrderLines);

Here, again, we are loading the entire set (which is, in fact, identical to the query we issued before) without regard for how big the result set may be. NHibernate provides a robust way of handling this scenario using filters:

var order = session.Get(orderId);
var orderLines = session.CreateFilter(order.OrderLines, "")
.SetFirstResult(0)
.SetMaxResults(25)
.List();

DoSomethingWithOrderLines(orderLines);

This allows us to page through a collection very easily, and saves us from having to deal with unbounded result sets and their consequences.