Ayende @ Rahien

It's a girl

NH Prof Alerts: Use statement batching

This is a bit from the docs for NH Prof, which I am sharing in order to get some peer review.

This warning is raised when the profiler detects that you are writing a lot of data to the database. Similar to the warning about too many calls to the database, the main issue here is the number of remote calls and the time they take.

We can batch together several queries using NHibernate's support for Multi Query and Multi Criteria, but a relatively unknown feature for NHibernate is the ability to batch a set of write statements into a single database call.

This is controlled using the adonet.batch_size setting in the configuration. If you set it to a number larger than zero, you can immediately start benefiting from reduced number of database calls. You can even set this value at runtime, using session.SetBatchSize().

Comments

Krzysztof Kozmic
12/31/2008 07:31 AM by
Krzysztof Kozmic

isn't it something that works only with MS SQL Server 2005+ ?

Ayende Rahien
12/31/2008 10:47 AM by
Ayende Rahien

It works in Sql Server 2000 + and in Oracle.

ZeusTheTrueGod
12/31/2008 11:34 AM by
ZeusTheTrueGod

Ayende, Happy New Year!

I read your blog every day, and at least 30% of your posts are usefull for me, please write more and better next year

Is there any chance to see a demo (5-10 minutes) of using NH Prof in sample hibernate application and removing select N+1 and other performance issues? If NHProf can find at least half issues - it is already usefull tool and comunity should have it in NHibernate package

Thomas Krause
12/31/2008 02:02 PM by
Thomas Krause

This is a nice feature, but I would reconsider the name of this warning. If I remember correctly your other alerts state the problem in the name ("Too many database calls per session"), but this time you named the alert after the proposed solution ("Use statement batching"). This is bad in my eyes for a number of reasons:

  • It is inconsistent with the other warnings

  • A large number of writes may indicate a more fundamental problem, like deleting all child items in a collection and recreating them each time you add one item.

  • There are other solutions to reduce the number of writes to the database other than batching. For example for large bulk imports, you may want to use other options like SqlBulkCopy.

Just my 2cts...

Ayende Rahien
12/31/2008 02:52 PM by
Ayende Rahien

Thomas,

You are correct, and I'll change the name of this alert.

Thanks for noticing this.

Roger
01/02/2009 12:25 AM by
Roger

I haven't really used nhib + ado.net batching in real apps due to the mismatch between this and versioning/opt locking (which I normally use for every aggregate).

Are you aware of any good work around for this?

Niraj Bhatt
01/13/2009 11:30 AM by
Niraj Bhatt

It's not working with Oracle. I am using 10G & in the config file I have adonet.batch_size property to 16. I also tried setting batch-size at the class level but in vain. It still shows me all inserts seperate. One more aspect which confused me is when i try to insert a graph with one to many relationship, inserts are followed by updates.

Niraj Bhatt
01/13/2009 11:33 AM by
Niraj Bhatt

Sorry for the typo. I meant

"when i try to insert a graph with one to many relationship, updates are followed by inserts. "

Niraj Bhatt
01/15/2009 05:49 AM by
Niraj Bhatt

Oops!!! I had missed inverse="true". Ayende this could be a good thing to be detected by Profiler. Also can you please confirm that statment batching works with Oracle? I am looking at a single call to DB with multiple queries which does Insert, delete, update. Somehow I am not able to achieve it.

Ayende Rahien
01/15/2009 10:13 AM by
Ayende Rahien

I can confirm that batching works on Oracle.

Note that this is only using's Oracle's driver, not the system.data.oracle driver.

Ted Jardine
02/10/2009 01:10 AM by
Ted Jardine

First of all, within 10 minutes of profiling an app with NH Prof, I was able to discover an N + 1 select that none of my tests covered and in so doing uncovered a small typo that hadn't been caught at compile time or in any tests. Thank you for a great app!

  1. I too am having difficulty getting the adonet.batch_size to work with Oracle. And yes, I'm using Oracle's driver (11g (11.1.0.6.20) with a 10g database).

  2. I would love a keyboard shortcut to OPTIONS -> Clear

I realize that support questions should likely go to the Google group, so will post there.

Ayende Rahien
02/10/2009 01:25 AM by
Ayende Rahien

Ted,

1/ the problem is with the way oracle's batches work. It wouldn't be reported as a batch in the profiler.

2/ I'll add one

Ted Jardine
02/10/2009 01:39 AM by
Ted Jardine

How's that for support: while posting a follow-up post you were already posting the answer.

Much thanks on both items.

Comments have been closed on this topic.