In the application startup (Application_Start in web applications, Program.Main in console applications, or the App constructor for WPF applications), make the following call (after initializing NHibernate but before querying the database):
To view the internal statistics in NHibernate, modify your NHibernate configuration and set generate_statistics to true. Here is how do it using the standard NHibernate configuration mechanism:
One of the DLLs that comes with the profiler is HibernatingRhinos.Profiler.Integration. This DLL provides programmatic access to the profiler object model. On NuGet it'savailable as NHibernateProfiler.Integration package. For example, you can use it inside your unit tests to assert the behavior of your code.
var captureProfilerOutput = new CaptureProfilerOutput(@"/path/to/profiler.exe");
captureProfilerOutput.StartListening();
NHibernateProfiler.Initialize();
// run code that uses NHibernate
Report report = captureProfilerOutput.StopAndReturnReport();
// Assert / inspect on the report instance
Create a new CaptureProfilerOuput() file and pass it the path to the nhprof.exe executable.
Call StartListening() to start listening to yourapplication's behavior.
Execute your code
Call StopAndReturnReport() to get the report and assert or inspect it.
The profiler supports the following command line interface, which allows you to execute the profiler as part of your continuous integration builds:
/CmdLineMode[+|-] (short form /C)
/File:<string> (short form /F)
/ReportFormat:{Json|Html|Xml} (short form /R)
/Port:<int> (short form /P)
/InputFile:<string> (short form /I)
/Shutdown[+|-] (short form /S)
Starting up with no options will result in the UI being displayed.
Here is how we can get a report from our continuous integration build:
NHProf.exe /CmdLineMode /File:report-output /ReportFormat:Json /ReportFormat:Html # starts listening to applications
xunit.console.exe Northwind.IntegrationTests.dll
NHProf.exe /Shutdown # stop listening to applications and output the report
This approach should make it simple to integrate into your CI process. The JSON and XML output allow programmatic access to the report, while the HTML version is human-readable. You can generate JSON, XML, and HTML reports by specifying the ReportFormat option multiple times.
One thing you might want to be aware of is that writing the report file is done asynchronously, so the shutdown command may return before the file writing is completed. If you need to process the file as part of your build process, you should wait until the first profiler instance completes. Using PowerShell, this is done like this:
NHProf.exe /CmdLineMode /File:report-output /ReportFormat:Json /ReportFormat:Html
xunit.console.exe Northwind.IntegrationTests.dll
NHProf.exe /Shutdown
get-process NHProf | where {$_.WaitForExit() } # wait until the report export is completed
Licensing
Please note that from a licensing perspective, the CI mode is the same as the normal GUI mode. On one hand, this means that you don't need to do anything if you already have the profiler and want to run the CI build on your machine. On the other hand, if you want to run it on a CI machine, you would need an additional license for that.
NHibernate Profiler can aggregate related sessions under the same scope. By default, in web applications, we aggregate sessions that are opened in the same web page under the same scope. However, we provide an API that you can use to aggregate related sessions in your application using a different strategy.
To start a new scope and dispose of the returned object to end the scope, you can use:
In addition, you can override the ProfilerIntegration.GetCurrentScopeName() method in order to set the current scope using your application-specific details:
By default, the profiler logs are sent from an application via TCP, although sometimes your application might not be able to reach the profiler over the network. Using the Azure Storage Channel feature, you can configure the appender to send the logs as blobs to the Azure storage container and analyze them in NHibernate Profiler. This way, you can profile your NHibernate queries from Azure Functions.
How it works
The idea behind the Azure Storage Channel is that profiler logs are uploaded as blobs to an Azure container. In Azure, you can configure a subscription so that, on each BlobCreated event, an appropriate message will be created in the Azure storage queue.
The NHibernate Profiler will listen to those queue messages, download the blobs, analyze them, and display your queries in real time. You can also load existing profiler logs later on.
Setup on Azure
To make the uploaded logs available for NHibernate Profiler, you need to execute the following steps:
create a storage account,
create a container,
create a storage queue,
create a subscription to enqueue messages about blob creation.
You can define it using the Azure portal as shown here , or use an Azure CLI script (Bash, Powershell)
Appender configuration
When initializing the appender, you need to specify the AzureStorageToLogTo property and provide the connection string and container name where the profiler log blobs will be sent.
HibernatingRhinos.Profiler.Appender.NHibernate.NHibernateProfiler.Initialize(new NHibernateAppenderConfiguration()
{
AzureStorageToLogTo = new AzureStorageConfiguration("azure-storage-connection-string", "container-name")
});
Connecting to Azure storage from the profiler
To load the logs you need to connect to Azure using Options > Azure Storage Channel
Once you're connected to the Azure channel, the profiler will start showing data from the profiled application that is configured to send logs to the Azure container. You can choose whether the logs should be deleted from the container after processing or if they should stay there. If the container already contains some profiling data, you can also load it into the profiler by providing a date range.
While the easiest way to prepare the application for profiling is to simply call NHibernateProfiler.Initialize(), this approach is often undesirable, as adding or removing the profiler requires changing the application code.
Therefore, we also made it possible to configure the application without this explicit initialization. The method for doing so is:
This will generate a file with a snapshot of all the NHibernate activity in the application, which you can use for later analysis by loading it into the profiler.
You can instruct the profiler to ignore some parts of your application and not publish any events generated from those sections.
This can be achieved using one of the following ways:
using (ProfilerIntegration.IgnoreAll())
{
// Ignore all events generated from here
}
Or
ProfilerIntegration.IgnoreAll();
// Ignore all events generated from here
ProfilerIntegration.ResumeProfiling();
You'll want to use this feature when you have a massive amount of operations that you don't need to profile. A typical scenario for this is building a huge database as part of the test process, which can be time-consuming to profile.
Sometimes you want to profile your production application. It can be a website running on a remote machine or a service application. Using the production profiler feature of NHibernate Profiler, you can connect to your production application via Options > Remote Servers:
Once you're connected to the remote application, the profiler will start showing data from it.
To allow production profiling, your application should add the following line at the very beginning of your application:
The above method will make your application start listening on port 9090 for incoming connections. The second parameter we send to this method is a password that will be used to filter unauthorized access to your application. You should make it strong enough to secure your application.
The traffic over the connection between the profiler and your application will use SslStream, ensuring it is encrypted and secure.
Troubleshooting
To diagnose issues in your production profiling environment, you can set the following value:
Common errors include missing permissions in the running service. Setting this value to true will throw an error in your application, allowing you to diagnose the issue.
After this call, each new statement will be marked with a yellow star. You can stop new statements from being starred by using:
ProfilerIntegration.StarStatementsClear();
Here is a usage example:
using (var db = new Entities(conStr))
{
var post1 = db.Posts.FirstOrDefault();
using (ProfilerIntegration.StarStatements("Blue"))
{
var post2 = db.Posts.FirstOrDefault();
}
var post3 = db.Posts.FirstOrDefault();
ProfilerIntegration.StarStatements();
var post4 = db.Posts.FirstOrDefault();
ProfilerIntegration.StarStatementsClear();
var post5 = db.Posts.FirstOrDefault();
}
In the above example, only post2 and post4 will be starred.post2 would get a blue star, while post4 would get the default star, which is yellow.
Note that StarStatements can use the following colors: Yellow, Orange, Red, Green, Blue, and Gray.
While using the profiler you may be prompted by your firewall to allow the profiler to continue working.
The profiler works by listening on TCP sockets for events from Hibernate/NHibernate. To do this, it needs to listen to a socket, which can sometimes trigger an alert in firewalls.
Denying permission to start listening on a socket will disable the profiler's ability to do live profiling. It is strongly recommended that you allow it.
The profiler also checks for updates at each startup. In the event of a profiler error, it will ask if you allow it to submit the error details for resolution.
In addition, the profiler will periodically report (anonymously) which features are being used in the application, helping us focus on the most commonly used parts of the product.
When you are using SQLitewith GUIDs, NHibernate Profiler will show formatted queries that fail to find data in the database, even if the data actually exists.
The actual reason is related to the way SQLite parses GUIDs. Since it isn't actually aware of GUIDs, SQLite treats them as strings, and that obviously fails when matching against the actual GUID value.
The solution
The SQLite ADO.NET provider has a workaround for this. You can use the following connection string parameter to enable it:
You can specify this parameter when using the Show Results feature and when using a SQLite browser to inspect the actual database content. This ensures that the SQL generated by NH Prof will be able to find the actual results in the database.
If you purchased the licenses, you may use them wherever you want, as long as only you are using them.
If your company purchased the license, you would need a separate license for home use. 'Per seat' refers to the number of people in an organization using the software, not the number of machines it is installed on.
Note that there is a corollary to this: a single machine used by two developers, with both using NH Prof, requires two licenses, one for each user of the profiler.
The profiler shows the session but without any statements
If you run a program that exits very quickly, you might see the session in the profiler without any log statements. This happens because the profiler hasn't had the chance to log the statements before your application exits. To resolve this, you can force the profiler to log all the statements by adding the following line at the end of your code:
Since NHibernate Profiler 3.0, there is an option called 'Detect Connection String' . When enabled, it adds the connection string of the most recent connection, if it does not already exist in the connection list, and sets it as the default connection.
Since the connection string can contain sensitive information that we don't want to expose, we provide the option to ignore the passing of the connection string when the HibernatingRhinos.Profiler.Appender is initialised:
From time to time, while you are using NHibernate, you use additional methods to query the database. The profiler provides hooks that allow you to integrate those external querying options into it as well.
You can do that using the following code:
HibernatingRhinos.Profiler.Appender.CustomerQueryReporting.ReportQuery(
sessionId: Guid.NewGuid().ToString(), // create a new session
queryText: "select * from Users where Id = @users", // the sql
parameters: dbCmd.Parameters, // contains the @users parameter
databaseTime: 10, // time in milliseconds from executing query to first row
totalTime: 15, // time in millseconds from executing query to completing processing the query
rowCount: 10 // number of rows returned by this query
);
This will cause the select * from Users where Id = @users query to appear up as a new session in the profiler.
You might encounter an error denying access to the license file when first using the Profiler. This is typically caused by Windows locking the Profiler because it was downloaded from the Internet.
To resolve this issue, navigate to the folder where you extracted the Profiler, right-click on HibernatingRhinos.Profiler.Client.exe, and select Properties.
Next, click on the Unlock button to grant the Profiler standard application permissions and resolve the issue.
There is a known compatibility issue between the NHibernate Profiler and the UltraMon program on some computers. If you are running the UltraMon program on your computer and try to maximize the profiler window on one of your monitors, you may encounter the following exception:
An error occurred in NHibernate Profiler which is known to be caused by using UltraMon on your computer. Please follow the instructions in the following URL in order to solve this issue: https://nhprof.com/Learn/Faq/UltraMon
This is because the UltraMon program tries to inject controls into the NHibernate Profiler windows.
If you get the above message, instruct your UltraMon program not to inject buttons into the NHibernate Profiler windows. To do this, follow these steps:
Right click on the UltraMon icon in the System Tray and choose option.
On the Compatibility tab select Configure Settings.
From the Running Applications list, select the NHibernate Profiler and click Next.
Ensure that the Disable Window Buttons option is checked, then click Finish.
The license includes all updates within a point release. If you purchase a license for v1.0, you will receive all updates for any v1.x version at no additional cost.
The profiler has detected a query with a large number of joins, such as the following:
select *
from Blogs blog0_
inner join Posts posts1_
on blog0_.Id = posts1_.BlogId
inner join Comments comments2_
on posts1_.Id = comments2_.PostId
inner join Users user3_
on posts1_.UserId = user3_.Id
inner join UsersBlogs users4_
on blog0_.Id = users4_.BlogId
inner join Users user5_
on users4_.UserId = user5_.Id
Queries with too many joins can cause performance issues.
Each join requires the database to perform additional work, and the complexity and cost of the query grow rapidly with each additional join. While relational databases are optimized for handling joins, it is often more efficient to perform several separate queries rather than a single query with multiple joins.
For OLTP systems, you should consider simplifying your queries or the data model. While we do not recommend avoiding joins entirely, we strongly discourage queries with a large number of joins.
Another issue to pay attention to is the potential for Cartesian products in queries containing joins. It is easy to create such a situation without noticing it during development.
With an ORM abstraction like NHibernate, it's easy to generate queries that are not optimized. In particular, this alert is triggered when we detect that the type of the entity's field, used as a parameter in the query, does not exactly match the table's column type in the database.
A column type mismatch between the entity's property type and the table's column type can lead to several implicit conversion issues, which may cause performance problems and conversion overflow errors. Most commonly, these issues prevent the database from utilizing indexes properly.
Queries still work, and everything appears fine externally, but the query cost is significantly higher than it would be otherwise.
Common examples of type mismatches include:
Int32 vs bigint
Int64 vs int
String vs char / varchar
AnsiString vs nvarchar / nchar
To resolve this issue, ensure that the column type and entity definitions match in all respects. Refer to the common type mismatches listed above for guidance.
The profiler detected identical statements using different sizes for the same parameters. For example, consider two queries issued to find users by name.
We can do this using the following queries: (Note: I am using a syntax that shows the parameter sizes to demonstrate the issue).
exec sp_executesql
N'SELECT * FROM Users WHERE Username = @username',
N'@username nvarchar(3)',
@username=N'bob'
exec sp_executesql
N'SELECT * FROM Users WHERE Username = @username',
N'@username nvarchar(4)',
@username=N'john'
This type of code results in two query plans being stored in the database query cache due to the different parameter sizes. In fact, assuming the Username column has a length of 16, this single query could occupy 16 entries in the query cache.
Worse, if you have two parameters with varying sizes, such as username (length 16) and password (length 16), you could occupy up to 256 entries in the query cache. Clearly, if you use more parameters or if their lengths increase, the number of cache entries a single query can consume grows rapidly.
This can cause performance problems, as the database needs to keep track of more query plans, consuming more memory. It may also require evicting query plans from the cache, which leads to rebuilding the query plan, increasing server load and query time.
NHibernate provides the following configuration option to control this:
<property name='prepare_sql'>true</property>
After specifying this, NHibernate will generate a consistent query plan for all identical queries, regardless of the actual parameter size.
This alert is triggered when the profiler detects that a query was generated from the view in an MVC application. Issuing queries from the view is considered a bad practice for several reasons:
It increases the time the connection to the database needs to stay open. The recommendation is to keep the connection open only for the duration of the action, not throughout the lifetime of the request.
It makes it much harder to understand the data requirements for a particular action.
When writing views, you shouldn't have to worry about persistence or the number of queries your views are generating.
Views are often the most changeable parts of an application, and having the application issue queries from the views can lead to significant changes in the data access patterns between revisions.
Most often, queries from views result from lazy loading, Select N+1, or similar bad practices.
We strongly recommend avoiding generating queries in the view. Instead, perform all queries in the action and provide in-memory access to the view for rendering.
The database is very efficient at handling queries that search for an exact match, such as this:
select * from Users where Name = 'ayende'
But it requires a lot more work when you use a LIKE query, such as this:
select * from Users where Name like 'ayende%'
In many cases, the database can still optimize this query, and if you have an index on the field, it will use the index to speed up the search.
But things are drastically different when you have a query that checks for "contains" :
select * from Users where Name like '%ayende%'
Or using "ends with"
select * from Users where Name like '%ayende'
The problem is that the database cannot use an index for this type of query, and is forced to issue a full table scan, inspecting each value in the database for a match. This tends to be very inefficient process.
You should carefully consider whether you should use this feature. If you really need to support "ends with" and "contains" queries, you should consider using either the database's own full-text search indexing or an external full-text search solution, such as Lucene or Solr.
The excessive number of rows returned warning is generated by the profiler when a query returns a large number of rows. The simplest scenario is when we load all the rows from a large table, using something like the following code snippet:
session.CreateCriteria(typeof(Order)).List();
This is a common mistake when binding to a UI component (such as a grid) that performs its own paging. This is problematic on several levels:
We tend to want to see only part of the data.
We just loaded a lot of unnecessary data.
We are sending more data than necessary over the network.
We have a higher memory footprint than we should.
In extreme cases, we may crash due to an out-of-memory exception.
None of these are ideal, and like the discussion on unbounded result sets, this problem can be easily prevented by applying a limit at the database level to the number of rows we want to load at any given time.
But it is not just simple queries without limits that can cause this issue. Another common source of this error is the Cartesian product when using joins. Check out this query:
Assuming we have 10 orders, each with 10 order lines and 5 snapshots, we will load 500 rows from the database. Mostly, these 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, causing the same issues as before. Now, we also have the problem that Cartesian products don't tend to stop at 500, but escalate very quickly to ridiculous numbers of rows returned for a trivial amount of data that we actually want.
The solution to this issue is to change the way we query the data. Instead of issuing a single query with several joins, we can split it into multiple queries and send them all to the database in a single batch using Multi Query or Multi Criteria.
This warning is raised when the profiler detects that you are writing a lot of data to the database. Similar to the warning about too many calls to the database, the main issue here is the number of remote calls and the time they take.
We can batch several queries together using NHibernate's support for MultiQuery and MultiCriteria, but a relatively unknown feature of NHibernate is the ability to batch a set of write statements into a single database call.
This is controlled using the adonet.batch_size setting in the configuration. If you set it to a number larger than zero, you can immediately start benefiting from a reduced number of database calls. You can even set this value at runtime using session.SetBatchSize().
Using more than one session per request is generally bad practice. Here's why:
Each session has its own database connection. Using more than one session means using more than one database connection per request, resulting in additional strain on the database and slower overall performance.
Typically, we expect the session to keep track of our entities. When we have multiple sessions, each session is not aware of the entities tracked by the other session and might have to query the database again for their current state or issue an unnecessary update.
Having more than a single session also means that we can't take advantage of NHibernate's Unit of Work and have to manually manage our entities' change tracking. We might end up with multiple instances of the same entities in the same request, which using a single session for the whole request would prevent.
Having more than one session means that the ORM has more work to do. In most cases, this is unnecessary and should be avoided.
You can no longer take advantage of features scoped to the session, such as the first level cache.
It is generally recommended to use one session per request. You should explore the Session Per Request pattern.
Typically this situation results from micromanaging the session, meaning that we open the session just before the query and close it immediately after the query or operation is executed. For example, see the following code:
public T GetEntity<T>(int id)
{
using (var session = sessionFactory.OpenSession())
{
return session.Get<T>(id);
}
}
it's strongly recommended to use an ambient/contextual session, which will be the same throughout the entire request, as shown in the following code:
public T GetEntity<T>(int id)
{
var session = sessionFactory.GetCurrentSession();
return session.Get<T>(id);
}
This code makes use of the current session, so multiple calls to this method will always use the current session and not open a new one.
Writing to the database from more then one session in the same request is bad for several reasons. Here's why:
Each session uses a separate database connection. Using more than one session means using more than one database connection per request. This can hurt overall performance and put more pressure on the database.
Using different sessions means that we cannot take advantage of the NHibernate built-in transaction support and must rely on System.Transactions, which is significantly slower.
We can't rely on the database to ensure a transactionally safe view of the data since we are using several different transactions to access the database. Note that this remains true even when using System.Transactions.
When using System.Transactions it forces you to use DTC in order to keep all sessions in the same transaction. Using DTC leads to poor performance and is more brittle than avoiding it.
Without using System.Transactions, there is no ability to use a transaction across all the sessions.
For example, let us consider the following code, which uses multiple sessions to perform a single operation:
In this example, we call the database twice, each time from a different session. Because of this, we cannot take advantage of the database's native transaction support, since each operation happens in a different transaction. Our choices are to either operate essentially without transactions (in which case, money can literally disappear into the air), or participate in a distributed transaction (System.Transactions).
We are also unable to take advantage of NHibernate's features to increase system performance, such as batching multiple statements against the database.
The solution for this is simple: Use just one session per request. The strong recommendation of the NHibernate development team is that you should always use the ambient / contextual session. NHibernate also provides an API that can help you to do so.
This alert is raised whenever the profiler detects that a query was generated as a result of a data binding operation.
The simplest from of this is binding to a LINQ query against the database:
DataContext = from user in ctx.Users
where user.IsActive
select user;
The problem with this is that the Win Forms / WPF data binding code was designed with the assumption that it would work against in-memory data. Therefore, setting a data source usually triggers multiple calls to interface methods to obtain the data. As a consequence, when performing data binding against a database query directly, the query is often evaluated a couple of times, triggering multiple round-trips to the server.
The recommendation is to bind to the resultof the query:
var activeUsers = from user in ctx.Users
where user.IsActive
select user;
DataContext = activeUsers.ToList();
Another problem with data binding that the profiler will detect and warn about is lazy loading as a result of a data binding operation. For example, binding to Order.Customer.Name will lead to loading the customer entity and binding to its Name property. The problem with those sorts of queries is that they are likely to repeat for as many items as there are in the query, leading to Select N+1.
Even assuming you are binding to just a single entity, lazy loading through data binding is a bad practice. You should use eager loading to load the data upfront, rather than letting the UI load it in the worst possible manner.
We have detected a query that was made on an unindexed column. The problem here is that a query on an unindexed column forces the database to perform a table scan. Such an action requires the database to go through all the rows in the table.
In some cases, on very small tables, that is fine and the best way to find the results you want. On larger tables, however, that can be an excessively expensive operation, which usually ends up biting you only after the dataset has grown to production size, often failing at 2 AM or thereabouts.
Consider this query carefully and evaluate whether an index needs to be created, the query needs to be modified, or if this table is small enough that a table scan is the appropriate behavior.
Select N + 1 is a data access anti-pattern where the database is accessed in a suboptimal way. Take a look at this code sample, and then we'll discuss what is going on. Say you want to show the user all comments from all posts so that they can delete all of the nasty comments. The naive implementation would be something like:
// SELECT * FROM Posts
foreach (Post post in session.CreateQuery("from Post").List())
{
// lazy loading of comments list causes:
// SELECT * FROM Comments where PostId = @p0
foreach (Comment comment in post.Comments)
{
//print comment...
}
}
In this example, we can see that we are loading a list of posts (the first select) and then traversing the object graph. However, we access the collection in a lazy fashion, causing NHibernate to go to the database and bring the results back one row at a time. This is incredibly inefficient, and the NHibernate Profiler will generate a warning whenever it encounters such a case.
The solution for this example is simple: Force an eager load of the collection upfront.
Using HQL:
var posts = session
.CreateQuery("from Post p left join fetch p.Comments")
.List();
var posts = (from post in session.Query<Post>().FetchMany(x => x.Comments)
select post).ToList();
In each case, we will get a join and only a single query to the database.
Note: this is the classical appearance of the problem. It can also surface in other scenarios, such as calling the database in a loop, or more complex object graph traversals. In those cases, it is generally much harder to see what is causing the issue.
Having said that, NHibernate Profiler will detect those scenarios just as well and give you the exact line in the source code that caused this SQL to be generated.
Other options for solving this issue are MultiQuery and MultiCriteria, which are also used to solve the problem of Too Many Queries.
Select N + 1 is a data access anti-pattern where the database is accessed in a suboptimal way.This alert, in essence, is a combination of the classical N+1 with the more than one session per request alerts.
Take a look at this code sample, then we'll discuss what is going on. Say you want to show the user all comments from all posts so that they can delete all of the nasty comments. The naive implementation would be something like:
var posts = Dao.GetRecentPosts();
foreach(var post in posts)
{
post.Comments = Dao.GetCommentsForPost(post);
}
public IEnumerable<Post> GetRecentPosts()
{
using(var session = sessionFactory.OpenSession())
{
return (from post in session.Query<Post>()
orderby post.PublishedDate descending
select post)
.Take(50)
.ToList();
}
}
public IEnumerable<Comment> GetCommentsForPost(Post post)
{
using(var session = sessionFactory.OpenSession())
{
return (from comment in session.Query<Comment>()
where comment.Post.Id = post.Id
select comment)
.ToList();
}
}
In this example, we can see that we are opening a session and loading a list of posts (the first select). Then, for each of the posts that we loaded, we open up a new session, including a new connection to the database, and make an additional query.
Because we are using multiple sessions in this fashion, we can't really take advantage of NHibernate features that are meant to deal with this exact situation. The first step is to avoid using more than one session per request, and the next step is to follow the guidance on solving Select N+1 problems for NHibernate.
One of the more common mistakes made when mapping associations from the database is failing to set the proper inverse attribute, which informs NHibernate about which side of the association is the owner.
This is important because, while associations are unidirectional in the object world, they are bidirectional in the database world. As a result, NHibernate assumes by default that all associations are unidirectional, requiring an explicit step (setting inverse = 'true' on the association) to recognize bidirectional associations.
The actual problem is simple: NHibernate issues a superfluous update statement. The NHibernate Profiler can detect and warn against such a mistake.
Notice that, while this is a bidirectional association in the object model and is maintained in two different places, it is maintained in a single place in the database.
This is a very common case, and quite easy to get wrong. By default, NHibernate assumes that it must update the column on both sides. So creating a new post and adding it to the Blog's Posts collection will result in two statements being written to the database:
As you can see, we are actually setting the BlogId to the same value twice. Once in the insert statement and again in the update statement.
Now, there is a very easy fix for this issue: all you have to do is to tell NHibernate on the Blog's Posts mapping that this is a collection where the responsibility for actually updating the column value lies on the other side. This is also something I check quite often in code reviews. The fix is literally just specifying inverse = 'true' on the one-to-many collection association.
NHibernate Profiler has detected a large number of cache calls being made in a single request. Note that this alert is only relevant if you are using a distributed cache, if you are using a local cache, you can safely ignore this alert.
If you are using a distributed cache, however, this alert indicate a possible performance issue that you should be aware of. While a single call to the cache is generally much faster than a database call, making a large number of fast calls can end up being slower than making a smaller number of slower calls.
It is easy to get into situations where make hundreds of calls to the cache. With a distributed cache, this results in hundreds of remote calls, which can become very costly in terms of performance.
To avoid this issue, consider limiting the number of results returned in cache queries (since NHibernate will make a cache call for each result). This is best practice in general, even if you are not using a distributed cache or using a local cache.
Another common reason for this alert is relying on the cache as a crutch, making a large number of queries and assuming the cache will handle it fast enough after the first time. The problem with this approach is that accessing the cache takes time. Even when using a local cache, consider the case when the data is expired or being purged from the cache.
The NHibernate Profiler has detected a large number of cache calls being made in a single session. Note that this alert is only relevant if you are using a distributed cache; if you are using a local cache, you can safely ignore this alert.
If you are using a distributed cache, however, this alert indicates a possible performance issue that you should be aware of. NHibernate currently does not batch calls to the cache (this is a planned feature). While making a request to the cache is generally much faster than calling the database, making a large number of fast calls can end up being slower than making a smaller number of slower calls.
It is easy to end up in situations where you make hundreds of calls to the cache. With a distributed cache, this results in hundreds of remote calls, which can become very costly in terms of performance.
To avoid this issue, consider limiting the number of results returned in cache queries (since NHibernate will make a cache call for each result). This is best practice in general, even if you are not using a distributed cache or using a local cache.
Another common reason for this alert is relying on the cache as a crutch, making a large number of queries and assuming the cache will handle it fast enough after the first time. The problem with this approach is that accessing the cache takes time. Even when using a local cache, consider the case when data is expired or being purged from the cache.
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 request is making an excessive number of calls to the database. This is usually an indication of a potential optimization in the way the sessions are used in the request.
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, 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.
The last two issues have to be dealt with on an individual basis. Sometimes, using a different approach (such as bulk copy for batching inserts) or a specific query or stored procedure that will get all the data from the database in a single round trip.
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, see the suggestions for Select N+1. Calling the database in a loop is generally a bug and should be avoided. The code can usually be restructured to eliminate the need for such calls.
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 made to the database.
The last issue is more interesting. We need to retrieve data from several sources, so we issue multiple queries to obtain it. The problem arises from issuing multiple separate queries, which leads to the issues listed above.
NHibernate provides a nice way to avoid this by using MultiQuery and MultiCriteria, both of which allow you to aggregate multiple queries into a single call to the database. If this is your scenario, I strongly recommend taking a look at MultiQuery and MultiCriteria and see how to use them in your applications.
With the abstraction of an ORM, such as NHibernate, it's easy to generate queries that are hard to follow and have performance penalties, since you don’t see the exact SQL that is produced. This often leads to writing code that produces inefficient SQL queries.
The SQL is bad not only because of its readability but also because of the additional work the database's query optimizer must perform to process such a query.
In this case, we detected a query with complex expressions in the WHERE clause. Having too many expressions in the WHERE statement can lead to poor performance, especially as the dataset grows.
Consider the complexity of your statement and whether you can reduce the work done by refactoring your code to minimize the effort required by the database to process your queries.
With the abstraction of an ORM like NHibernate, it is easy to generate queries that are hard to follow and come with performance penalties. Since you don't see the exact SQL that is generated, you might write code that results in inefficient SQL. The issue is not only with the readability of the SQL but also with the workload required by the database's query analyzer to handle such a query.
To demonstrate such a query, consider the one below. It contains many nested SELECT statements, making the SQL hard to follow and slower for the database to execute. The query is lengthy and performs numerous operations, none of which are particularly fast.
The problem with such queries is that the code generating them may seem innocent, yet it hides their costly impact. Consider modifying the queries that triggered this warning; in some cases, splitting them into separate, independently executed queries may offer better performance.
SELECT Project4.Id AS Id,
Project4.Name AS Name,
Project4.Description AS Description,
Project4.CategoryTemplateId AS CategoryTemplateId,
Project4.MetaKeywords AS MetaKeywords,
Project4.MetaDescription AS MetaDescription,
Project4.MetaTitle AS MetaTitle,
Project4.ParentCategoryId AS ParentCategoryId,
Project4.PictureId AS PictureId,
Project4.PageSize AS PageSize,
Project4.AllowCustomersToSelectPageSize AS AllowCustomersToSelectPageSize,
Project4.PageSizeOptions AS PageSizeOptions,
Project4.PriceRanges AS PriceRanges,
Project4.ShowOnHomePage AS ShowOnHomePage,
Project4.IncludeInTopMenu AS IncludeInTopMenu,
Project4.HasDiscountsApplied AS HasDiscountsApplied,
Project4.SubjectToAcl AS SubjectToAcl,
Project4.LimitedToStores AS LimitedToStores,
Project4.Published AS Published,
Project4.Deleted AS Deleted,
Project4.DisplayOrder AS DisplayOrder,
Project4.CreatedOnUtc AS CreatedOnUtc,
Project4.UpdatedOnUtc AS UpdatedOnUtc
FROM (SELECT Limit1.Id AS Id,
Limit1.Name AS Name,
Limit1.Description AS Description,
Limit1.CategoryTemplateId AS CategoryTemplateId,
Limit1.MetaKeywords AS MetaKeywords,
Limit1.MetaDescription AS MetaDescription,
Limit1.MetaTitle AS MetaTitle,
Limit1.ParentCategoryId AS ParentCategoryId,
Limit1.PictureId AS PictureId,
Limit1.PageSize AS PageSize,
Limit1.AllowCustomersToSelectPageSize AS AllowCustomersToSelectPageSize,
Limit1.PageSizeOptions AS PageSizeOptions,
Limit1.PriceRanges AS PriceRanges,
Limit1.ShowOnHomePage AS ShowOnHomePage,
Limit1.IncludeInTopMenu AS IncludeInTopMenu,
Limit1.HasDiscountsApplied AS HasDiscountsApplied,
Limit1.SubjectToAcl AS SubjectToAcl,
Limit1.LimitedToStores AS LimitedToStores,
Limit1.Published AS Published,
Limit1.Deleted AS Deleted,
Limit1.DisplayOrder AS DisplayOrder,
Limit1.CreatedOnUtc AS CreatedOnUtc,
Limit1.UpdatedOnUtc AS UpdatedOnUtc
FROM (SELECT Distinct1.Id AS Id
FROM (SELECT Extent1.Id AS Id1,
Extent1.ParentCategoryId AS ParentCategoryId,
Extent1.LimitedToStores AS LimitedToStores
FROM dbo.Category AS Extent1
LEFT OUTER JOIN dbo.AclRecord AS Extent2
ON (Extent1.Id = Extent2.EntityId)
AND (N'Category' = Extent2.EntityName)
WHERE (Extent1.Published = 1)
AND (Extent1.Deleted <> 1)
AND ((Extent1.SubjectToAcl <> 1)
OR ((Extent2.CustomerRoleId IN (3))
AND (Extent2.CustomerRoleId IS NOT NULL)))) AS Filter1
LEFT OUTER JOIN dbo.StoreMapping AS Extent3
ON (Filter1.Id1 = Extent3.EntityId)
AND (N'Category' = Extent3.EntityName)
WHERE (Filter1.ParentCategoryId = 7 /* @p__linq__0 */)
AND ((Filter1.LimitedToStores <> 1)
OR (1 /* @p__linq__1 */ = Extent3.StoreId))) AS Distinct1) AS Project2
OUTER APPLY (SELECT TOP (1) Filter3.Id2 AS Id,
Filter3.Name AS Name,
Filter3.Description AS Description,
Filter3.CategoryTemplateId AS CategoryTemplateId,
Filter3.MetaKeywords AS MetaKeywords,
Filter3.MetaDescription AS MetaDescription,
Filter3.MetaTitle AS MetaTitle,
Filter3.ParentCategoryId AS ParentCategoryId,
Filter3.PictureId AS PictureId,
Filter3.PageSize AS PageSize,
Filter3.AllowCustomersToSelectPageSize AS AllowCustomersToSelectPageSize,
Filter3.PageSizeOptions AS PageSizeOptions,
Filter3.PriceRanges AS PriceRanges,
Filter3.ShowOnHomePage AS ShowOnHomePage,
Filter3.IncludeInTopMenu AS IncludeInTopMenu,
Filter3.HasDiscountsApplied AS HasDiscountsApplied,
Filter3.SubjectToAcl AS SubjectToAcl,
Filter3.LimitedToStores AS LimitedToStores,
Filter3.Published AS Published,
Filter3.Deleted AS Deleted,
Filter3.DisplayOrder AS DisplayOrder,
Filter3.CreatedOnUtc AS CreatedOnUtc,
Filter3.UpdatedOnUtc AS UpdatedOnUtc
FROM (SELECT Extent4.Id AS Id2,
Extent4.Name AS Name,
Extent4.Description AS Description,
Extent4.CategoryTemplateId AS CategoryTemplateId,
Extent4.MetaKeywords AS MetaKeywords,
Extent4.MetaDescription AS MetaDescription,
Extent4.MetaTitle AS MetaTitle,
Extent4.ParentCategoryId AS ParentCategoryId,
Extent4.PictureId AS PictureId,
Extent4.PageSize AS PageSize,
Extent4.AllowCustomersToSelectPageSize AS AllowCustomersToSelectPageSize,
Extent4.PageSizeOptions AS PageSizeOptions,
Extent4.PriceRanges AS PriceRanges,
Extent4.ShowOnHomePage AS ShowOnHomePage,
Extent4.IncludeInTopMenu AS IncludeInTopMenu,
Extent4.HasDiscountsApplied AS HasDiscountsApplied,
Extent4.SubjectToAcl AS SubjectToAcl,
Extent4.LimitedToStores AS LimitedToStores,
Extent4.Published AS Published,
Extent4.Deleted AS Deleted,
Extent4.DisplayOrder AS DisplayOrder,
Extent4.CreatedOnUtc AS CreatedOnUtc,
Extent4.UpdatedOnUtc AS UpdatedOnUtc
FROM dbo.Category AS Extent4
LEFT OUTER JOIN dbo.AclRecord AS Extent5
ON (Extent4.Id = Extent5.EntityId)
AND (N'Category' = Extent5.EntityName)
WHERE (Extent4.Published = 1)
AND (Extent4.Deleted <> 1)
AND ((Extent4.SubjectToAcl <> 1)
OR ((Extent5.CustomerRoleId IN (3))
AND (Extent5.CustomerRoleId IS NOT )))NULL) AS Filter3
LEFT OUTER JOIN dbo.StoreMapping AS Extent6
ON (Filter3.Id2 = Extent6.EntityId)
AND (N'Category' = Extent6.EntityName)
WHERE (Filter3.ParentCategoryId = 7 /* @p__linq__0 */)
AND ((Filter3.LimitedToStores <> 1)
OR (1 /* @p__linq__1 */ = Extent6.StoreId))
AND (Project2.Id = Filter3.Id2)) AS Limit1) AS Project4
ORDER BY Project4.DisplayOrder ASC
With an ORM abstraction like NHibernate, it's easy to generate queries that are not optimized.
In this case, we detected that your query spans a large number of tables, which may lead to poor performance and become too slow, especially as your dataset grows. The more tables involved in the query, the more work the database has to perform, whether it's through subselects, joins, or nested queries.
Consider optimizing the query to use fewer tables, or even splitting it into multiple separate queries if that proves to be more performant.
Example of a query with multiple tables:
select blog0_.Id as Id15_0_,
posts1_.Id as Id20_1_,
comments2_.Id as Id19_2_,
blog0_.Subtitle as Subtitle15_0_,
blog0_.AllowsComments as AllowsCo4_15_0_,
blog0_.CreatedAt as CreatedAt15_0_,
posts1_.Title as Title20_1_,
posts1_.BlogId as BlogId20_1_,
comments2_.PostId as PostId1__,
comments2_.Id as Id1__,
user3_.Password as Password22_3_,
user3_.Username as Username22_3_,
user3_.Bio as Bio22_3_,
user5_.Password as Password22_4_,
user5_.Username as Username22_4_,
user5_.Bio as Bio22_4_,
users4_.BlogId as BlogId2__,
users4_.UserId as UserId2__
from Blogs blog0_
inner join Posts posts1_
on blog0_.Id = posts1_.BlogId
inner join Comments comments2_
on posts1_.Id = comments2_.PostId
inner join Users user3_
on posts1_.UserId = user3_.Id
inner join UsersBlogs users4_
on blog0_.Id = users4_.BlogId
inner join Users user5_
on users4_.UserId = user5_.Id
With the abstraction of an ORM like NHibernate, it's easy to create code that is hard to follow and has performance penalties, since you don't see the exact SQL produced in the end. This often leads to writing code that results in inefficient SQL. The SQL is not only hard to read but also requires more work from the database's query analyzer to execute.
In this case, we detected that you have a query with a lot of Where statements. Having too much Where statements can lead to poor performance, especially when you grow and have a big data set. In this case, we detected that your query contains many WHERE statements. Having too many WHERE statements can lead to poor performance, especially as your dataset grows.
We suggest keeping the WHERE clauses to a minimum.
NHibernate Profiler has detected a transaction that was disposed without either calling Commit() or Rollback().
This is a common bug, forgetting to call transaction.Commit() before disposing of the session, which results in an implicit rollback. For example:
using(var transaction = session.BeginTransaction())
{
var post = session.Get<Post>(5);
post.AddComment(comment);
} // bug, implicit rollback goes here
Instead of the code above, we should have written the following:
using(var transaction = session.BeginTransaction())
{
var post = session.Get<Post>(5);
post.AddComment(comment);
transaction.Commit()
}
This piece of code will work and save the new comment. If you want to roll back the transaction, it is recommended to use an explicit Rollback(), as this would make the code easier to understand later.
An unbounded result set occurs when a query is performed without explicitly limiting the number of returned results usingSetMaxResults() in NHibernate, or the TOP or LIMIT clauses in SQL. Usually, this implies that the application assumes the query will always return only a few records. While this may work well in development and testing, it can become a ticking time bomb in production.
The query may suddenly start returning thousands, or even millions, of rows. This leads to increased load on the database server, the application server, and the network. In many cases, it can cause the entire system to grind to a halt, often resulting in application server crashes due to 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, all of them will be loaded, which is likely not what was intended. This can be easily fixed by using pagination:
Now we can be sure 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. Another common occurrence of the unbounded result set problem arises from directly traversing the object graph, as shown in the following example:
var order = session.Get(orderId);
DoSomethingWithOrderLines(order.OrderLines);
Here, again, we are loading the entire set (which is identical to the query we issued before) without considering how large the result set may be. NHibernate provides a robust solution for 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 easily page through a collection and saves us from dealing with unbounded result sets and their consequences.
A common mistake when using a database is to use transactions only for orchestrating multiple write statements. In reality, every operation performed by the database, including queries and writes (update, insert, delete), is executed within a transaction.
Note: In SQL Server, Implicit Transactions are typically referred to as Autocommit Transactions.
When we don't define our own transactions, it falls back to implicit transaction mode, where each statement to the database runs in its own transaction. This results in a significant performance cost (database time to build and tear down transactions) and reduced consistency.
Even if we are only reading data, we should use a transaction, as it ensures consistent results from the database. NHibernate assumes that all database access occurs under a transaction and strongly discourages using the session without one.
Example of valid code:
using(var session = sessionFactory.OpenSession())
using(var tx = session.BeginTransaction())
{
// execute code that uses the session
tx.Commit();
}
Leaving aside the safety concerns of working with transactions, the assumption that transactions are costly and need optimization is false. As mentioned earlier, databases always run in a transaction and are heavily optimized to handle them.
The real question is: Is the transaction per-statement or per-batch? Creating and disposing of a transaction involves a non-trivial amount of work; doing it per-statement is more costly than doing it per-batch.
It is possible to control the number and type of locks a transaction takes by adjusting the transaction isolation level. In fact, a common optimization is to reduce the isolation level.
NHibernate treats the call to Commit() as the point at which all changes in the unit of work are flushed to the database. Without an explicit call to Commit(), NHibernate has no way of knowing when to perform this flush. While calling Flush() is possible, it is generally discouraged, as it often indicates improper transaction usage.
We strongly suggest using code similar to the example above (or another approach to transactions, such as TransactionScope, or Castle's Automatic Transaction Management) to handle transactions correctly.
Transaction and the second-level cache
Another implication of not using explicit transactions with NHibernate is related to the use of the second-level cache.
NHibernate goes to great lengths to ensure that the second-level cache maintains a consistent view of the database. This is accomplished by deferring all second-level cache updates until the transaction is committed. In this way, we can assert that the data in the second-level cache is the one committed to the database.
Forgetting to use explicit transactions nullifies the second-level cache. Here is an example that would make this clear:
using(var session = sessionFactory.OpenSession())
{
var post = session.Get<Post>(1);
// do something with post
}
Even if the second-level cache is enabled for Post, it will still not be cached in the second-level cache. The reason is that NHibernate will not update the cache with the values for the loaded entities until the transaction is committed.
This code, however, does make use of the second-level cache:
using(var session = sessionFactory.OpenSession())
using(var tx = session.BeginTransaction())
{
var post = session.Get<Post>(1);
// do something with post
tx.Commit();
}
NHibernate Profiler has detected that a session is being used in a different thread than the one it was opened on.
NHibernate sessions are not thread-safe, and using them across multiple threads requires careful synchronization. It is generally best to consider a session as only useful within the thread that created it.
There are valid scenarios for cross-thread session usage (e.g., background loading with careful synchronization, or multi-request spanning sessions), but typically, this alert indicates a problem with serializing access to the session.
The following code will trigger this alert:
using(var session = factory.OpenSession())
{
var wait = new ManualResetEvent(false);
//simulating work on the session using a different thread...
ThreadPool.QueueUserWorkItem(state =>
{
using(var tx = session.BeginTransaction())
{
session.Get<Blog>(1);
tx.Commit();
}
wait.Set();
});
wait.WaitOne();
}
If you are intentionally using the session across multiple threads and are certain that you are correctly serializing access to it, you can safely ignore this alert.
You can also completely disable this alert in the options dialog.
There are two main reasons why this might show up.
NHibernate Warning Surfacing
The NHibernate Profiler surfaces logged warnings from NHibernate, allowing you to identify the cause of the warning and fix it.
NHibernate is quite good at detecting problematic situations and warning you about them, so please pay attention to any warnings that are surfaced.
NHibernate Error Detection
Whenever NHibernate encounters an error (e.g., transaction deadlock or optimistic concurrency exception), the NHibernate Profiler detects it and displays the full error, including the exception message and stack trace.
Most of the time, this error occurs when using DateTime.MinValue or DateTime.MaxValue. Ensure that your DateTime value falls within the supported range of SQL Server.
