System.OutOfMemory Error over a large dataset 1.5 millions

Question

Ok everyone I have been struggling with the System.OutOfMemory error when selecting from a large dataset, its over 1.5 million records in the database. The query I am performing is below. How to over come this issue. I really need help.

var ResultSet = from ResultsConcatString in ResultsTable.AsEnumerable()
                                                           join ResultsByMaxDate in concatTable.AsEnumerable()
                                                            on
                                                                new
                                                                {
                                                                    KEY1 = ResultsConcatString.Field<string>("CONCATSTR"),
                                                                    KEY2 = ResultsConcatString.Field<string>("MAXTIME"),
                                                                }
                                                            equals
                                                                new
                                                                {
                                                                    KEY1 = ResultsByMaxDate.Field<string>("CONCATSTR"),
                                                                    KEY2 =   ResultsByMaxDate.Field<string>("WRITE_TIME"),
                                                                }
                                                           select new
                                                           {
                                                               STANDARD_VERSION = ResultsByMaxDate.Field<string>("STANDARD_VERSION"),
                                                               WRITE_TIME = ResultsByMaxDate.Field<string>("WRITE_TIME"),
                                                               IDENTIFICATION = ResultsByMaxDate.Field<string>(IDENTIFICATION"),
                                                               REVISION = ResultsByMaxDate.Field<string>("REVISION"),
                                                               SERIAL_NUMBER = ResultsByMaxDate.Field<string>("SERIAL_NUMBER"),

                                                           };

David Smith · Answer

Yes that is linq below, I do have a try catch around the code, I have the System.OutOfMemory Exception catch. Also Do you guys think I should do the OdbcAdapter for the amount of records or you the SqlAdapder, I am not for sure how to over come the records below.

Sam Hobbs · Answer

That does not look like SQL. I think you are confusing Senthilkumar by calling it SQL. It is LINQ, correct? Are you sure you are getting the error from that statement? LINQ often delays execution until later. Try using try/catch. There might be some other error that your program does not respond to.

Senthilkumar · Answer

Hi Darnell, Here problem is not relative to the hard disk. Because the dataset is memory representation and related to the asp.net worker process where it consumes the memory of RAM.

David Smith · Answer

I am operation on a 64-bit machine. I am still getting the error. I dont think error is point to the physical memory of the computer. I have a 16gb harddrive, so i know running out of memory is not the problem, it only happens, when i have over a million records in the database, are telling me to also serialize and deserialize class?

Senthilkumar · Answer

Hi Darenell, I am not sure about exactly what is the problem. But i think problem with the size of the data stored in the dataset and when it does for serialize and deserialize then problem comes. If you run in the 32-bit then you may face this problem. I do not think you will have similar kind of problem with 64-bit. Please see the similar kind of problems and solutions. http://blogs.msdn.com/b/tess/archive/2008/09/02/outofmemoryexceptions-while-remoting-very-large-datasets.aspx http://our.componentone.com/groups/winforms/chart-for-winforms/forum/topic/system-out-of-memory-exception-with-large-data-set/