Skip to main content

NHibernate performance issues #3: slow inserts (stateless session)

The whole series of NHibernate performance issues isn't about simple use-cases. If you develop small app, such as simple website, you don't need to care about performance. But if you design and develop huge application and once you have decided to use NHibernate you'll solve various sort of issue. For today the use-case is obvious: how to insert many entities into the database as fast as possible?

Why I'm taking about previous stuff? The are a lot of articles how the original NHibernate's purpose isn't to support batch operations, like inserts. Once you have decided to NHibernate, you have to solve this issue.

Slow insertion
The basic way how to insert mapped entity into database is:
SessionFactory.GetCurrentSession().Save(object);
But what happen when I try to insert many entities? Lets say, I want to persist
  • 1000 libraries
  • each library has 100 books = 100k of books
  • each book has 5 rentals - there are 500k of rentals 
It's really slow! The insertion took exactly 276 seconds! What's the problem?
Each SQL insert is sent to server within own server request.

Batch processing with using adonet.batch_size
You can set property adonet.batch_size within your hibernate configuration to tell NHibernate that he can sent more queries to the SQL server within one statement. I'm going to set this value to 100. What's the improvement? Insertion took 171 seconds right now. Better than 276! But isn't it a lot of time? Yes it is!
The major problem is that NHibernate standard insertion via Session.Save is not intended to use for batch processing. NHibernate generated events, go through mapping, doesn't group insert statements together in proper way by default. Obviously, it must take some time. Now, it's the time to introduce ...

Stateless session
NHibernate's developers are smart guys so this significant functionality can't stay in "not-intended for batch processing" state. Stateless session is tool intended for batch processing.

Stateless session is lightweight version of Session.Save method, it doesn't throw so much events, it's fast, it just generates one insert for given object according to mapping. It's fast, so it apparently has any drawbacks.

Stateless session's drawbacks
  • stateless session isn't compatible with standard NHibernate session! There is another interface because it has completely different purpose. Spring.net's support is missing, you can't use transaction template. You must handl all the stuff by yourself.
  • because of intended fast behavior, stateless session doesn't handle any cascade operation on children. You must manually push all objects to session, all children, their children, etc.
The last point seems to be very unpleasant drawback but if you look at previous picture showing NHibernate profiler you can see the major benefit of this approach.

Despite I've set adonet.batch_size to 100,  only 5 inserts are sent to SQL server within one statement. NHibernate groups inserts only for same type of entity. You aren't able to achieve optimized query count with using standard way.

As I've said, you must call Insert method for each entity, so you can group all inserts of each specific entity by your code. Here are results of insertion:
  • 149 seconds - no advanced grouping when inserts are sent to sql server - insertion of first library followed by  insertion of it's books, insertion of all book's rentals, insertion of another library - we aren't still use fully utilized power of adonet.batch_size because only 5 inserts are sent in one statement
foreach (Library library in libraries) {
  session.Insert(library);
  foreach (Book book in library.Books) {
    session.Insert(book);
    foreach (Rental rental in book.Rentals) {
      session.Insert(rental);
    }
  }
}
  • 86 seconds - first of all libraries are processed by session's insert following by all books and all rentals - this approach efficiently uses batch size, because for 100k of books it sents only 1000 statements to SQL server, each having 100 of insert followed by set of 5k of inserts statements for rentals
foreach (Library library in libraries) {
  session.Insert(library);    
}

foreach (Library library in libraries) {
  foreach (Book book in library.Books) {
      session.Insert(book);
  }
}

foreach (Library library in libraries) {
  foreach (Book book in library.Books) {
    foreach (Rental rental in book.Rentals) {
        session.Insert(rental);
    }
  }
}
  • 80 seconds - adonet.batch_size = 1000

Stateless session is efficient!
The best result is the small summary of measured times because the main benefit of stateless session will exactly appear. The example persists (1k + 100k + 500k) 601k of entities.

session type adonet.batch_size additional groupping time [s]
standard no no 276
standard 100 no 171
stateless session 100 no 149
stateless session 100 yes 86
stateless session 1000 yes 80

If you need to improve your application's insertion time, just use stateless session.

You can also see Series of .NET NHibernate performance issues to read all series articles.

Comments

Asava Samuel said…
This is WAY easier to use than NHibernate:
https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx
Hi Admin,
Excellent blog and its totally loaded with valid posts on Java and .Net technology. Consider including RSS feed in your blog, so aspirants like me can follow your blog easily. Dot Net Training

Popular posts from this blog

Git on Windows: MSysGit

I have started to use Git today. I read a lot of discussions that there is no good tool for Windows platform. After forethought I have decided to used TortoiseGit. I also feared of difficult work related with Git as a lot of articles mentioned many instructions. As I already said, I have decided to use TortoiseGit, because I'm used to work with TortoiseSvn, but for start, MSysGit is enought. So this article is about MSysGit, next will be about TortoiseGit.

How to start with MSysgit on local machine?
Download and install Git for WindowsCreate source code directory for your git appRight click the directory at your favorite file browser. Menu should contain item "Git init here". It initializes chosen directory to be git-abled :-)It was your first usage of Git.

Commit data to local Git repository

Now, you can add any file, your first source code, to created directory. If you are prepared to commit any changes to your local git repository, follow next instructions.
Right-click th…

Tomcat 7 remote deployment

I decided to provide automatic deployment of war packaged application using Jenkins and Deployment plugin. The target platform is Amazon with Tomcat 7, see nice set of articles to find out how to setup such environment for free.

Well, there is couple of tutorials but they missing some points so it pushed me to lost one hour of my work.

What I gotFresh installation of Tomcat 7 on remote machine with opened 8080 port on firewallPersonal war file supposed to be deployed
How to push it to tomcat?
1. First of all, there is simple configuration of tomcat users in file tomcat-users.xml - it was my pain in the ass :-) As original comprehensive documentation says, it's necessary to define user, but which one(s)?

Here is working example of tomcat-users.xml:

<tomcat-users>
<user username="manager-gui" password="changeit" roles="manager-gui"/>
<user username="manager-script" password="changeit" roles="manager-script"/>