NHibernate, the Parallel Framework, and SQL Server

Question

hey guys, we have a loop that:

1.Loops over several thousand xml files. Altogether we're parsing millions of "user" nodes.

2.In each iteration we parse a "user" xml, do custom deserialization

3.finally, in each iteration, we send our object to nhibernate for saving. We use:

.SaveOrUpdateAndFlush(user);

This is a lengthy process, and we thought it would be a perfect candidate for testing out the .NET 4.0 Parallel libraries. So we wrapped the loop in a:

Parallel.ForEach();

After doing this, we start getting "random" Timeout Exceptions from SQL Server, and finally, after leaving it running all night, OutOfMemory unhandled exceptions.

I haven't done deep debugging on this yet, but what do you guys think. Is this simply a limitation of SQL Server, or could it be our NHibernate setup, or what?

cheers

andy

Answer 1

Parallel.ForEach is not magic. You still have to manage the units of work, connection pools, etc, with the added complexity of multiple threads. Also, remember that the NHibernate Session is not thread-safe (each thread should have its own session)

Your timeout exceptions could be caused by deadlocks or exhaustion of the connection pool (you didn't post the stack trace or the inner exceptions, so it's a guess)

OutOfMemory exceptions are probably due to memory leaks; you are probably leaving your sessions and entities hanging around. You should use a profiler like ANTS or dotTrace to find the cause (both have free trials)