Question:

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:

<pre class="lang-sql prettyprint-override">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 (<a href="https://stackoverflow.com/questions/2833406/nhibernate-insert-multiple-items-at-once" rel="nofollow">NHibernate: insert multiple items at once</a>) 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?

adonet.batch_size works, as long as:

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

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

You can accomplish that with <a href="http://nhforge.org/doc/nh/en/index.html#batch-direct" rel="nofollow">DML-style operations</a> in HQL.