Friday, December 9, 2011

Fetching whole table with NHibernate

I've done a quick benchmark for different ways of retrieving whole tables from the database. I've generated 10,000 random rows in my test table with countries first and then run simple SELECT * FROM Country queries for 7 different ways using more or less of NHibernate.

Here are the methods and its results:

2263 ms for LINQ query:

session.Query<Country>().ToList();

1468 ms for ICriteria:

session.CreateCriteria<Country>().List<Country>();

1289 ms for QueryOver:

session.QueryOver<Country>().List();

1431 ms for HQL:

session.CreateQuery("From Country").List<Country>();

1428 ms for SQL query with AddEntity:

session.CreateSQLQuery("select Id, Name, Continent from Country")
.AddEntity(typeof(Country))
.List<Country>();

593 ms for SQL query with AliasToBeanTransformer:

session.CreateSQLQuery("select Id, Name, Continent from Country")
.AddScalar("Id", NHibernateUtil.Int32)
.AddScalar("Name", NHibernateUtil.String)
.AddScalar("Continent", NHibernateUtil.String)
.SetResultTransformer(Transformers.AliasToBean<Country>())
.List<Country>();

489 ms for SQL query with manual objects creation:

session.CreateSQLQuery("select Id, Name, Continent from Country")
.AddScalar("Id", NHibernateUtil.Int32)
.AddScalar("Name", NHibernateUtil.String)
.AddScalar("Continent", NHibernateUtil.String)
.List<object[]>()
.Select(row => new Country()
{
Id = (int) row[0],
Name = (string) row[1],
Continent = (string) row[2]
}).ToList();

The results are pretty interesting.

Contrary to LINQ provider, there's no significant difference between ICriteria, QueryOver and HQL. Fetching data with native SQL query gives no performance gain when used with AddEntity - NHibernate still needs to initialize its infrastructure for managing entities - result is comparable to previous methods.

But there is a big difference when using plain SQL queries and transforming rows to objects on our own - it is about 3 times faster than letting NHibernate to do it. Why is that? When using AliasToBeanTransformer or when creating objects manually, we in fact don't work with persistent entities. Country objects created in two last queries are just POCOs, not associated with NHibernate session at all - its changes are not tracked and persisted automatically. This is not the usual way we are supposed to work with NHibernate, but this can be useful in some scenarios.

2 comments:

  1. nice post...I preferred the AliasToBeanTransformer...but the manual objects creation is still faster by some ms :)

    ReplyDelete