Is NHibernate generated SQL slower than handwritten statements in SP nowadays?

Question

I wonder about the general performance with insertions/updates/deletes in a sql server 2012 db, with data amounts sometimes reaching 20 m. rows in a table.

Can I trust NHibernate in generating optimal queries?

Answer 1

The simple answer is: No. NH cannot generate "optimal queries" the way you do with hand written queries.

It usually gets data from the database to create objects in memory. So for instance, by default it always selects all columns in a table. It tries to optimize SQL compilation time as well by trying to always generate the same queries with the same arguments.

You still can write your hand-written queries in HQL, Linq or SQL. You usually loose the ORM benefits, but it works perfectly if only a few cases in your application are performance critical and not much object oriented.

NHibernate can do other performance optimizations you can hardly achieve with hand written SQL. And this features are transparent to the business logic and do not need much special development effort.

These features are especially in the area of lazy loading and batch size (pre-fetching). With hand-written SQL, you have to do most performance optimizations in your business logic. If your business logic is complex, there is just not much space for optimizations and maintainability gets a huge issue. This is the situation where both performance and maintainability of the hand written DAL drops and it gets a real pain. This is the situation where NHiberate offers both better performance and much better maintainability.

As you may guess: if you business logic is not very complex, the hand-written DAL can provide the best performance possible with not too much maintainability trade off.

So always choose the right tool for the problem.

Example Case

The application has a function to process orders. It gets a primary key as an argument. It loads the order with the order items from the database, it needs information about users and products, locations, taxes, postal rates, special offers and stuff like this. So it is a very complex thing. Query optimization is not much of an issue, because most queries are trivial from it's nature.

Four years later, your boss wants a "batch process" where you can process hundreds of orders. So you have to call your function in a loop. First problem: you get the orders to process by an optimized query. But your function takes a primary key as argument and loads the orders again. Next problem: all the related data about products, locations etc need to be loaded for each order again, even though they are the same in most of the cases (but not always). So it starts to perform very bad. You try to implement some caches for things you already loaded from the database. It gets hard to manage changes on this data, because you have a version in the database and a version in the cache. So you do your best until it performs good enough while you lost control over your code which had been so nice before the "batch process" came in. And whatever feature comes next, it will kill you.

With NHibernate it looks much different. You do not have to pass primary keys ever. You have objects (which may be proxies and only contain the primary key) which are loaded exactly once from the database. When navigating (in contrast to use additional queries) from the order to the products, locations and everything, objects are loaded only once on the first use. Because of configurable batch-size, NH prefetches the products, locations etc. for some orders that will be processed later, so it avoids additional queries. Your code basically only contains the calculations you need to do and is free of complicated stuff which is required to improve performance. (Ok, this is how it would be in a perfect world, but it is pretty good actually).

Answer 2

Using NHibernate or any ORM is a choice, mostly chosen to have a more maintainable code compared to a one with SQLs and SPs.

It might not generate a query fast enough compared to a SP in some cases, and still many of these cases may not have noticeable performance issues. However if that is not the case you can always use a SP and use NHibernate to call SP.

Answer 3

You should not blindly trust it. There are many ways to optimize queries in NHibernate such as multiple APIs for querying including an ability to generate HQL or SQL queries.

There are various profiling tools including NHibernate Profiler that can be used to monitor queries and the general performance.

There is also the IStatelessSession designed for bulk operations.

Answer 4

This depends on what you would call optimal queries and how your data model suits your database model (tables).

You can log all the statements, so it is transparent what NHibernate is doing and what SQL statements it is using to query or update the database.

NHibernate gives you many options to tune your queries. Some possibilities are stateful vs stateless sessions, transactions, HQL, futures.

If this is still doesn't work how you intented it to you can call SQL statements directly from NHibernate. The disadvantage of this is that you lose the ability to port your code pretty easy to another database, if this is important for you.