What is the best way to iterate through a table of 60,000 records, where each record has a map to a few hundred other records, in order to process them one at a time?

In total, the 60,000 row table and the 42 million row table must be processed. Having NHibernate pull in all these records into memory at once overruns my 2GB RAM capacity (not surprisingly). I suspect lazy-loading the map to the larger table is only delaying the inevitable, because as I process each record the other table will get slowly loaded until it's fully in memory -- or I run out.

I wish there was a ISession.CreateQuery construct of some type that would allow me to pass in a delegate that would process each record as it came in, rather than NHibernate having to get ALL the records and return them ALL to me. Functionality like that of foreach in C#, where as soon as I am done with the current record, it is evicted.

The only idea I can think of to resort to is to not use NHibernate at all for this one data operation. To use the SqlDataReader myself and pull the various tables I need in as necessary. But I sure wish NHibernate could help me here.

If there is a way and I'm ignorant, please tell me how to do it.

Thanks.

At any rate this processing is overkill. Even if you use just ADO.NET you still have to transfer a large amount of data from the database.

I think this huge processing is a very good candidate for a stored procedure, and do all the processing in the db server

Cheers,
Radu

I absolutely agree.

I did a data processing app almost two years ago, using NHibernate 0.2. We had to have processing code run from a Windows Service, controlled by Remoting, so that it could run in the background. Once in a while there would be too much data and the service would die with an OutOfMemoryException (4 GB RAM). No, the problem was not a bug in NH which has since been fixed, it is a fundamentally bad choice in technology for the job at hand.

I would never do that again. ORM is not the solution for data processing. Use a stored procedure. This is truly what they are for.

Maybe "processing" wasn't the right word. I'm basically exporting my database into a data file format for researchers. So yes, I have to pull the data from the database. Stored procedures don't apply here.

Your database product should have an export tool. Some databases also allow stored procedures to write to a file as well. This needs to be kept as close to the DB as possible, and ORM is definitely not the way to go. If you must do it in app code, I would use an IDbDataReader and just loop through it and write to a file stream.

It's a binary file format that requires a Win32 dll, along with some computation on the data to create some metadata at the same time. I certainly could use the IDbDataReader, and I think that would solve the memory and performance problems, but it'll be a shame to not be able to leverage the nice object model that NHibernate usually exposes the database with.

It would be very nice to have an Enumerator property in ISession... just like iterator() in Hibernate. Try to check if you can create your own Session implementation with this property... in the get you would use the DataReader... Let me know if you tried and if it works...

Well, I worked around the problem (for now) using this code:


This code gets a list of all primary keys, then gets each individual row, and then evicts it. This effectively cuts down on the amount of RAM consumed. However, RAM still creeps up, even if I use ISession.Clear(). This got the job done, but it still took ~1GB of RAM for the query.

Oh, and this is using C# 2.0's yield return construct.

There is such a method, it's called Enumerable (the Java method is called iterate).

I see it's not implemented yet... :oops: :? :(

Where do you see that?