0

I am trying to find the most efficient way of running multiple (> 1000) insert statements with NHibernate.

The actual insert statement is very simple as it uses an FK ID from a newly created object together with values from a subquery. Here's what I would write in SQL:

insert into dbo.NotificationView
select 
    1 AS IDOfNewItem, 
    U.Id AS UserID, 
    0 AS HasEdited
from
    [User] U
    INNER JOIN UserSite US ON U.Id = US.UserId
where
    US.SiteId = 1

I have seen that there is the parameter called adonet.batch_size (NHibernate: insert multiple items at once) that can be set in the "hibernate-configuration" XML file, but it appears that this will simply create the same number of insert statements as there are objects.

Is there a way to run the insert in one go, without iterating through each item?

If so, does this negatively affect the cache in any way?

Community
  • 1
  • 1
jose
  • 172
  • 3
  • 17

1 Answers1

1

adonet.batch_size works, as long as:

  • You are using a supported DB (SQL Server, not sure about Oracle)
  • You are using a supported generator (for example, identity doesn't work)
  • You don't do anything "strange" with your entities.

Anyway, it looks like you are trying to do a bulk-insert, which is totally unrelated.

You can accomplish that with DML-style operations in HQL.

Diego Mijelshon
  • 52,548
  • 16
  • 116
  • 154
  • adonet.batch_size works with Oracle. I believe support as added in the 2.X timeframe. (I used it on my last project.) One word of warning... The Oracle provider in NH 2.X does not produce correct update batch logging messages. If you look in the logs (or are using NHProf), it looks like batching isn't working and the last update is executed twice. In actuality it is working and the log messages are wrong. This has been fixed in NH3. – James Kovacs Nov 12 '10 at 18:17