+972 52-548-6969
NH Prof AlertsUse 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().
More posts in "NH Prof Alerts" series:
- (31 Dec 2008) Use statement batching
- (30 Dec 2008) Too many database calls per session
- (29 Dec 2008) Excessive number of rows returned
- (29 Dec 2008) Unbounded result set
- (28 Dec 2008) Use of implicit transactions is discouraged
- (28 Dec 2008) Select N + 1
Comments
isn't it something that works only with MS SQL Server 2005+ ?
It works in Sql Server 2000 + and in Oracle.
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
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...
Thomas,
You are correct, and I'll change the name of this alert.
Thanks for noticing this.
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?
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.
Sorry for the typo. I meant
"when i try to insert a graph with one to many relationship, updates are followed by inserts. "
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.
I can confirm that batching works on Oracle.
Note that this is only using's Oracle's driver, not the system.data.oracle driver.
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!
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).
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.
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
It appears that Niraj Bhatt has got batch size working with Oracle (see nirajrules.wordpress.com/.../nhibernate-lessons...) so I'll find out from him what, if anything, he had misconfigured initially. Will then post according in NH Prof Google group.
How's that for support: while posting a follow-up post you were already posting the answer.
Much thanks on both items.
Comment preview