NHibernate Perfomance: Analisys

In this post I will show the results of the previous post from another point of view hoping it would be useful to Gergely Orosz to understand how work with NHibernate to have better performance for his test (in that post you can download the original code).

The environment

Pentium D840, 3GB ram, winXP, MsSQL2008Express, .NET3.5, NHibernate2.1.0GA.

The domain is the same:

where each Company has 24 Employees and each employee has 24 Reports (save a company mean save 601 entities).

Storing data

The StoreData test is basically a bulk insertion, of the domain, importing data from XML. To have better performance, in bulk inserts, there are basically two “tricks” as described in NHibernate reference (see Chapter 12): the adonet.batch_size and the StatelessSession. If you know how work the batcher, studying the code or watching the SQL log or reading this post, you can even change your code to improve performances having less round trips.

I haven’t used an “extreme” configuration (batch_size=500) but the result is:

To save 24 Companies, that mean 14424 entities, my code toke 39 roundtrips (in 5 transactions).

This result mean ~3050 entities per second, with normal behavior, and ~5300 entities per second using compiled queries (note the domain is something with parent-child relationship and a big NVARCHAR and not the same used by ORMBattle.NET).

If I play a little bit more, with the code, I may have even better performance.

Read All

Here I can show the code:

IList<NHCompany> result;
using (var session = Sf.OpenSession())
{
 result = session.GetNamedQuery("NHCompany.All").List<NHCompany>();
}
foreach (NHCompany company in result)
{
 writer.WriteLine(company);
 foreach (NHEmployee employee in company.Employees)
 {
     writer.WriteLine(employee);
     foreach (NHReport report in employee.Reports)
     {
         writer.WriteLine(report);
     }
 }
}

As you can see “read all” mean literally read-all and then show results in a log file. The difference here is that I’m loading 24 companies with all his employees and all reports (again 14424 entities) in only one roundtrip and only one SQL.

Read by ID

Again the code:

foreach (Company company in companies)
{
 NHCompany foundCompany;
 using (ISession session = Sf.OpenSession())
 {
     foundCompany = session.GetNamedQuery("NHCompany.One")
         .SetInt32("pId", company.Id).UniqueResult<NHCompany>();
 }

 writer.WriteLine(foundCompany);
 foreach (var foundEmployee in foundCompany.Employees)
 {
     writer.WriteLine(foundEmployee);
     foreach (var foundReport in foundEmployee.Reports)
     {
         writer.WriteLine(foundReport);
     }
 }
}

Which sense has this test I don’t know, by the way it is loading again each company (24) with all his employees and all reports (again 14424 entities) but, in my implementation, in only 24 roundtrips.

Update

foreach (NHCompany company in companies)
{
 company.Name += valueToAdd;
 foreach (NHEmployee employee in company.Employees)
 {
     employee.Name += valueToAdd;
     foreach (NHReport report in employee.Reports)
     {
         report.Text = valueToAdd + report.Text;
     }
 }
 session.Update(company);
}

This code is updating each entity (again 14424) and I’m doing it in 5 transactions. Thanks to this test I found a missed feature in NHibernate (the code is there but the configuration does not allow its usage) because, so far, this code is working using 49 roundtrips per each company when we can obtain the same final result with 39 roundtrips in total (for all 14424). As you can see I’m using a normal ISession (you can see it because there is only a session.Update and StateLessSession does not work with cascade).

Even if NH2.1.0GA is performing this test in 49 roundtrips per each company, the final result is ~1550 updates per second, with normal behavior, and ~2450 updates per second using compiled queries.

Delete

public override void DeleteData()
{
 using (var session = Sf.OpenStatelessSession())
 using (var tx = session.BeginTransaction())
 {
     session.CreateQuery("delete from NHCompany").ExecuteUpdate();
     tx.Commit();
 }
}

Well… is there something to say here ?

It is an entirely database clean in only one SQL and only one roundtrip in only one transaction.

Conclusions

I’m sorry with Gergely because his fault was “only” to publish the wrong post in the wrong moment; as I said before, in other moment my reaction would be only close the browser.

Anyway my opinion about MDTD continuing being the same (MDTD = Monkey Driven Test Development).