CODEWITHSUNDEEP
c#.netnhibernatehqlbulkinsert
NYCChris
NYCChris

Reputation: 649

Obscure NHibernate 3.2 Bulk Insert/HQL issue

I have a requirement to do a 'bulk' insert of Event records into a database, based on a set of dates that the user passes in.

My event entity has a StartTime property and a StopTime property. The Event entity is the core entity in my application, and as such has a number of collections and entity references associated with it. It's a heavy entity.

So, my original code had me looping through the dates and doing something like this:

 Session.Save(new Event {StartTime=startdate, StopTime=endDate});

However this didn't scale well, and we felt the performance when inserting a few hundred dates.

So I had the thought of inserting all the dates into a temp table, but I could not figure out how to accomplish that with HQL.

So my next thought was to include all the dates as a subquery which got me the closest. Here is that approach: 

var hql = "INSERT INTO Event (Code, Comment, Description, Status, StartTime, StopTime)
           SELECT ev.Code, ev.Comment, ev.Description, ev.Status, Dates.SDate, Dates.EDate
           FROM Event ev, 
                (
                    SELECT '1/2/2012 3:00:00 PM' as SDate, '1/2/2012 6:00:00 PM' as EDate
                    UNION ALL SELECT '1/3/2012 3:00:00 PM', '1/3/2012 6:00:00 PM' 
                    UNION ALL SELECT '1/4/2012 3:00:00 PM', '1/4/2012 6:00:00 PM'
                ) as Dates
           WHERE ev.Id = :sourceEventId";

Session.CreateQuery(hql).SetInt32("sourceEventId", @event.Id).ExecuteUpdate();

But the hql fails with very obscure error codes. My research online tells me that hql does not yet support UNION clauses.

Right now, I rewrote this approach as SQL and execute it through Session.CreateSQLQuery() and it works great. But I don't want to run it as SQL. That defeats the purpose of going with NHibernate anyway.

Is there another way I can approach this using HQL, (or any other NHibernate technology)?

Any help here is greatly appreciated!

UPDATE

  1. I forgot to mention that this is a legacy SQL Server database (I run unit tests on sqlite), and that all entities are generating their id's using SQL's Identity column. So any attempts to batch fail.

  2. As I explained in the comment below, I also tried the stateless session approach. NHibernate would fail telling me that I needed to save all my lazy loaded entities first. I temporarily got around that issue by removing those mappings, and it did work, and it was faster than regular session. But I still ended up having to insert every entity one-at-a-time.

Upvotes: 1

Views: 1782

Answers (2)

Sleiman Jneidi
Sleiman Jneidi

Reputation: 23329

try using stateless session instead of session

using (var session = sessionFactory.OpenStatelessSession())
using (var tx = session.BeginTransaction())
{
for (int i = 0; i < count; i++)
{
    session.Insert(yourObjects[i]);
}
tx.Commit();
}

this link is also helpful

Moreover you can use ADO.net SqlBulkCopy instead of stateless session ,check this.

Upvotes: 1

Ivo
Ivo

Reputation: 8352

What about this?

var sdates = new []{ "1/2/2012 3:00:00 PM", "1/3/2012 3:00:00 PM", "1/4/2012 3:00:00 PM" };
var edates = new []{ "1/2/2012 6:00:00 PM", "1/3/2012 6:00:00 PM", "1/4/2012 6:00:00 PM" };

var hql = "INSERT INTO Event (Code, Comment, Description, Status, StartTime, StopTime)
       SELECT ev.Code, ev.Comment, ev.Description, ev.Status, :sdate, :edate
       FROM Event ev
       WHERE ev.Id = :sourceEventId";

for(var i=0;i<sdates.Length;i++)
{
     Session.CreateQuery(hql).SetInt32("sourceEventId", @event.Id)
                             .SetString("sdate", sdates[i])
                             .SetString("edate", edates[i])
                             .ExecuteUpdate();
}

Probably you'll need to use DateTime instead of string and do some tweaks to the code, but it's an idea. Basically, you will be executing three commands instead of one but also instead of n+1 (and that was your scalation problem, right?)

Hope it helps.

Upvotes: 0

Related Questions