NH Prof AlertsToo many database calls per session

time to read 2 min | 353 words

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

One of the most expensive operations that we can do in our applications is to make a remote call. Going beyond our own process is an extremely expensive operation. Going beyond the local machine is more expensive yet again.

Calling the database, whatever to query or to write, is a remote call and we want to reduce the number of remote calls as much as possible. This warning is being raised when the profiler notice that a single session is making an excessive number of calls to the database. This is usually an indicative of a potential optimization in the way the session is used.

There are several reasons why this can be:

  • Large number of queries as a result of a Select N+1.
  • Calling the database in a loop.
  • Updating (or inserting / deleting) large number of entities
  • Large number of (different) queries that we execute to perform our task.

For the first reason, you can see the suggestions for Select N+1. Calling the database in a loop is generally a bug, and should be avoided. Usually you can restructure the code in a way that doesn't require you to do so.

Updating large number of entities is discussed in Use statement batching, and mainly involves setting the batch size to reduce the number of calls that we make for the database.

The last issue is the more interesting one, in which we need to get data from several sources, and we issue multiple queries for that. The problem is that we issue multiple separate queries for that, which has the aforementioned issues.

NHibernate provide a nice way of avoiding this, by using Multi Query and Multi Criteria, both of which allow you to aggregate several queries into a single call to the database. If this is your scenario, I strongly recommend that you would take a look Multi Query and Multi Criteria and see how you can use them in your application.

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