Batching of Inserts/Updates/Deletes

jason.hill · **Joined:** Sat Sep 03, 2005 12:54 am **Posts:** 139

Hi,

I had a quick look through the NH source and it looks like insert/update/delete batching is not yet supported. Are there any plans to implement this in a future release?

Cheers,

Jason

anandbn · **Posted:** Fri Aug 04, 2006 5:47 am

You can look at :

http://www.hibernate.org/hib_docs/v3/re ... batch.html

For current support on Batching.

jason.hill · **Joined:** Sat Sep 03, 2005 12:54 am **Posts:** 139

Hi,

I did see that doc, but I thought it was for Hibernate V3...I am using NHibernate 1.0.2.

Cheers,

Jason

sergey · **Posted:** Fri Aug 04, 2006 8:09 am

It can't really be implemented in NHibernate because ADO.NET doesn't support it. ADO.NET 2 supports batching but not when using plain IDbCommand/IDbConnection (one has to use DataAdapters). So currently there are no plans to do it.

jason.hill · **Joined:** Sat Sep 03, 2005 12:54 am **Posts:** 139

Hi Sergey,

Is it not possible to send a batch of SQL statements via an IDbCommand like this:

Code:

insert into Customer values(...);
insert into Customer values(...);
insert into Customer values(...);
insert into Customer values(...);

Cheers,

Jason

sergey · **Posted:** Sat Aug 05, 2006 11:39 am

NHibernate needs to check return values (i.e. affected row counts) for safety and this concatenated command won't return the values for individual statements.

jason.hill · **Joined:** Sat Sep 03, 2005 12:54 am **Posts:** 139

It would be quite easy to keep track of the row count in a contatenated batch (I can only speak for SQL Server) although I guess you have other issues like identity values from newly inserted rows too. This would be possibly via TSQL but could be pretty tricky to implement generically for multiple database engines.

Batching of the deletes would certainly seem to be easier to implement as it would only be necessary to keep track of the total rows affected.

It would be great if some time could be invested in this area because performance does suffer with the multiple hits to the DB that occurs currently, especially if the DB resides on a separate box.

paulh · **Posted:** Wed Aug 09, 2006 10:55 am

It's slightly tricker than it first appears. NHibernate uses parameters for queries to avoid SQL injection and create more efficient SQL, so we'd have to change all of the code that creates parameters to generate explicit SQL which would be rather time consuming and bug-prone.

Unless there's a way that I don't know about to get the SQL that would be emitted by an IDBCommand, I don't see this happening.

Regards

paulh · **Posted:** Wed Aug 09, 2006 11:03 am

It's slightly tricker than it first appears. NHibernate uses parameters for queries to avoid SQL injection and create more efficient SQL, so we'd have to change all of the code that creates parameters to generate explicit SQL which would be rather time consuming and bug-prone.

Unless there's a way that I don't know about to get the SQL that would be emitted by an IDBCommand, I don't see this happening.

Regards