Too many database calls per session

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 still.

Calling the database, whether 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 raised when the profiler notices that a single session is making an excessive number of calls to the database. This is usually an indication of a potential optimization in the way the session is used.

There are several reasons why this can be:

• A large number of queries as a result of a Select N + 1
• Calling the database in a loop
• Updating (or inserting / deleting) a large number of entities
• A 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 such a way that you are not required to call the database in that way.

Updating a 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 more interesting. We need to get data from several sources, and we issue multiple queries for that data. The problem is that we issue multiple separate queries to accomplish this, which has the problems listed above.

NHibernate provides a nice way to avoid this by using MultiQuery and MultiCriteria, 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 take a look at MultiQuery and MultiCriteria and see how to use them in your applications.