NHibernate has pretty good support for batching, something that can significantly increase performance when inserting or updating large number of objects.
Example:
In the above example you can see that the order lines are created in one statement. In a recent mail conversation with Patrik Löwendahl he asked for assistance in getting batching to work. The first thing to check is what id generator you are using, you cannot use native (sql identity) id generator and expect batching to work for inserts. The reason for this that for identity inserts NHibernate issues a "select SCOPE_IDENTITY()" statement after each insert statement to fetch the generated ID. If you want to use batching for inserts you need to use the guid or hilo id generator.
Another issue i came across was that batching does not work as you would hope for associations. For example if you want to save a thousands orders and each order has five order lines, this would result in six thousands calls if batching was disabled and two thousands calls with batching enabled. As you see in the screenshot above, batching is only done on the order lines and not for everything.
You can optimize further by using the stateless session. However inserting entities using nhibernate’s stateless session ignores associations. But by looping through all orders and calling session.Insert(order), and then doing a nested loop to do the same for all order lines you can insert all orders and order lines in just two calls to the database.
The problem Patrik had was very weird and confusing. To verify that batching was actually happening he used SQL Profiler, while I used NHProfiler. The weird thing is that they show a very different picture. NHProfiler shows order lines as being issued in one command while SQL Profiler shows them as separate RPC calls.
+
This result left me very confused. The NHProfiler result clearly indicates that batching is being used but SQL Profiler shows the same results as when batching is off. However when batching is enabled the performance is significantly better, what is going on here?? After some Googling on SQL Profiler and batching I found this comment on stackoverflow:
On MS SQL Server, SQL Profiler shows each insert statement seems to be on it's own. After reviewing your comment, I viewed a TCP Dump of the conversation and do see that it is batching multiple commands together. SQL Profiler shows each insert as a "RPC Completed" event which was confusing me. Thanks for your help.
I appears that batching IS being done but not like I thought it would be (for example a Batch Starting command in SQL Profiler). The difference, when batching is turned on, is that all the statements are sent to the database in one go without waiting to listen for a response. That explains the SQL Profiler result, however I still find the NHProfiler result puzzling as it indicates that the order lines are created using a single call to sp_executesql.
Ayende care to explain? :)
Some links on NHibernate batching:
