Ayende @ Rahien

Hi!
My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:

ayende@ayende.com

+972 52-548-6969

, @ Q c

Posts: 18 | Comments: 84

filter by tags archive

NH Prof AlertsUse statement batching

time to read 1 min | 145 words

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:

  1. (31 Dec 2008) Use statement batching
  2. (30 Dec 2008) Too many database calls per session
  3. (29 Dec 2008) Excessive number of rows returned
  4. (29 Dec 2008) Unbounded result set
  5. (28 Dec 2008) Use of implicit transactions is discouraged
  6. (28 Dec 2008) Select N + 1

Comments

Krzysztof Kozmic

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

Ayende Rahien

It works in Sql Server 2000 + and in Oracle.

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

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

Thomas,

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

Thanks for noticing this.

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

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

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

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

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

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

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

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

Much thanks on both items.

Comment preview

Comments have been closed on this topic.

FUTURE POSTS

  1. Buffer allocation strategies: A possible solution - 3 days from now
  2. Buffer allocation strategies: Explaining the solution - 4 days from now
  3. Buffer allocation strategies: Bad usage patterns - 5 days from now
  4. The useless text book algorithms - 6 days from now
  5. Find the bug: The concurrent memory buster - 7 days from now

There are posts all the way to Sep 11, 2015

RECENT SERIES

  1. Find the bug (5):
    20 Apr 2011 - Why do I get a Null Reference Exception?
  2. Production postmortem (10):
    03 Sep 2015 - The industry at large
  3. What is new in RavenDB 3.5 (7):
    12 Aug 2015 - Monitoring support
  4. Career planning (6):
    24 Jul 2015 - The immortal choices aren't
View all series

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats