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.