Out Of Memory in 50 million record c# nhibernate

Question

how can I handle Out Of Memory in 50million record nhibernate, I used stateless sessions in NHibernate and solve around ~1.7 million records but its not good for my report. and another thing is I need to calculate on them so I can't take apart the result. Could you let me know how to optimize it?

        public IEnumerable<ATMDispenseReport> GetReport(string WhereClause)
    {

        var CurrentUnitOfWork = this.UnitOfWork as MainUnitOfWork;

        var session = CurrentUnitOfWork.StatelessSession;

        string SqlQuery = "MyQuery";

        var lstATMDispenseReport =session.CreateSQLQuery(SqlQuery)       .SetResultTransformer(Transformers.AliasToBean<ATMDispenseReport>())
            .List<ATMDispenseReport>();

        return lstATMDispenseReport;
    }

Edit: Final Solution was using DBMS to handle the query calculation.

Answer 1

I am presuming you are running on x86 (32-bit). An array (or a List<T>) in .NET has to be stored in a contiguous block of memory, so a list of 50 milion items will need a single block of more than 200MB just to store the addresses of each instance of ATMDispenseReport (and this will occur after several instantiations, because lists grow dynamically).

Although a 32-bit app has a 2GB memory limit, if you already have a bunch of stuff and the memory is fragmented, it's possible that you don't have contiguous area that is large enough. And then you also have 50 million of ATMDispenseReport objects, which don't need to be allocated contiguously, but they are probably at least 10 times larger than the 4 byte pointer.

1. Dirty hack? Switch to x64 and check if you have enough RAM in this machine. You might need <gcAllowVeryLargeObjects> too. Note that, for an object of average size of ~40B you will need more than 2GB of RAM. That's seems a bit weird for a single query, since I doubt your user really needs all these rows at once.

2. Simple solution? Replace List<T>() with Enumerable<T>() and enumerate results, if you really need to enumerate all results. This will stream the data lazily, so you only need to keep a single element in memory at any time. Remember, however, that all this data still needs to be delivered through the socket your app is using to connect to the DBMS server. We are probably talking about several GBs of data.

3. Better solution? Rewrite your SQL to aggregate all data at the SQL side, before it gets back to your app. Let the DBMS server do all the job.