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?Answer1:
<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>
adonet.batch_size works, as long as:
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.