Sunday, December 2, 2012

Some notes on performance tuning with NHibernate

A few weeks back, I spent an intensive day performance tuning parts of a, to me, relatively unfamiliar part of our codebase. Like it often is, the biggest optimizations were to be found in how we work with the database. Now, I don't consider myself to be an NHibernate expert; I read this book and have used it on two projects, but in the end I just do my best to avoid doing stupid things with it. The topics discussed below are mostly common knowledge for long time NHibernate users, but I thought it might be convenient for others to just summarize them, and add references to other, more in-detail, posts.

Under the covers

When you're looking into optimizing, you probably want to have a look at what's really going on. You could do this by turning on NHibernate's log4net debug logging. This might be good enough for some scenarios, but it's not really convenient for when there is lots and lots of stuff happening. Instead, you might want to look into NHibernate Profiler. It's trivial to get started with, yet the feedback it provides is very powerful: next to session statistics and executed queries, you also get alerts which suggest techniques to improve your code. I need to use this tool more often just to get a better grip of the NHibernate internals.

The fastest query is the one that isn't

Going to the database is probably one of the slowest things your application is going to do. If you can avoid it, do so.

If you're using a stateful session, NHibernate will track your entities, and store them in its first level cache. When you get these items by id later on, you avoid going to the database. So instead of querying the database for the same entity multiple times in the same session, do it once, and get the entity by its id on subsequent calls.

When you're having a hard time keeping those ids around, consider introducing a light-weight datastructure, such as a dictionary, which can help you build a small look-up cache. This could also be a sign that you might want to reconsider your identity strategy though.

Working with batches

An ORM really isn't the best tool to do bulk inserts or updates; look at SqlBulkCopy instead.

If your batches are still relatively small, and you opt to stay with NHibernate anyways, there are two things you can do which will improve performance tremendously: use a stateless session and configure batching.

Switching to a stateless session is simple enough. Do take into account that some features won't work anymore: lazy loading, caching, cascading and implicit updates. Setting up batching is also just a matter of configuration. The most important thing to remember is to use an appropriate identity generator; batching will only work when the application is responsible for generating the ids. I'm using a HiLo generator, but GUIDs or assigned ids will work too. 

2 comments:

  1. Not bad.
    One should also consider configuring a second-level cache (discussed in the link about first-level caching)
    Mapped entities that are high read / low write can be set with a cache level of non-strict read/write (chance of dirty reads goes up so choose read/write if writing can be an issue).
    Mapped entities that are read-only (say, a mapped country lookup object) could be marked as cache readonly
    Last, database tuning still matters. NHibernate Profiler helps quite a bit. Database-specific tools do as well though. For example, on SQL Server, I like to use Plan Explorer by SQL Sentry (the show plan stuff in Management Studio is fine too) and look to see if we missed anything basic like indexes on foreign-key joined tables.

    ReplyDelete
  2. Good scaling & performance tips are given in the youtube vidoe "how MSSQL scales stackoverflow". Check it out, you will see a lot of cool ideas.

    ReplyDelete