System Out of memory exception: Selecting 4.5 million records into a datatable

Question

_{Friday, May 11, 2012 4:29 AM}

Sorry for the confusion, how to write the logic export a large dataset into pieces. At the moment if I try to export 4.5 million files to one text file, I get a system out of memory exception. So I have to write logic to break up into chunks. so what would be the logic. I want to write no more then  750,000 records to a text file.

Let me know if this make sense.

Export the data to excel is the easy part. Now export 4.5 five million records to one textfile is not the easy part, because i get the system out of memory exception. How to avoid that?

I have 4.5 million records in a temporary datatable, my goal is to export data to a text file in chunks of records until all records are written to textfile or processing

Can someone help me write the logic to export 750,000 records at a time until all records are gone. For every 750,000 records create its own text file. Below is the logic I using below in code, can you guys help me modify logic below

Have simple logic below to grab the data, now I after the data get into to the temporary table, how to write the logic break up 4.5 millions records into pieces?

DataTable  dataTable = new DataTable ();

dataTable.Columns.Add(new DataColumn("UNIT", typeof(string)));
                        dataTable.Columns.Add(new DataColumn("SERIAL", typeof(string)));
                        dataTable.Columns.Add(new DataColumn("PART", typeof(string)));

SqlDataReader dr = cm.ExecuteReader();

// The error happens while trying to read from the data reader, I get the system out of memory error, I am trying to read all 4.5 million into a datatable. I never make it to the "return datatable" below                          
                        while(dr.Read()) 
                        {

                                nextRow = dataTable.NewRow();

                                nextRow["UNIT"] = row.UNIT;
                                nextRow["SERIAL"] = row.SERIAL;
                                nextRow["PART"] = row.PART;

                                dataTable.Rows.Add(nextRow);

                        }

return dataTable;

All replies (11)

_{Thursday, May 17, 2012 8:43 PM ✅Answered}

If you use the data reader directly and you still get an out of memory exception then it means that somehow you store the records in memory. Can you show the complete code that you have used with data reader?

_{Thursday, May 17, 2012 11:50 PM ✅Answered}

by looking at the code you put on your original post, certainly you will get out of memory error, cause you keep pumping rows into a data table. you should write the record to file as you are reading.

nextRow = dataTable.NewRow();

***                                nextRow["UNIT"] = row.UNIT;***
***                                nextRow["SERIAL"] = row.SERIAL;***
***                                nextRow["PART"] = row.PART;***

                                dataTable.Rows.Add(nextRow);

Johnny http://goo.gl/TaQNY

_{Friday, May 18, 2012 7:08 AM ✅Answered}

Probably it is much easier to create an SP and run that on the server side, spares you direct a bunch of memory. Try it in this forum where the experts live who handle that. 

http://social.msdn.microsoft.com/Forums/en-US/transactsql/threads

Success
Cor

_{Monday, June 11, 2012 7:01 PM ✅Answered}

Thanks for point this out. That was the fixed. I was modifying exisiting code.

_{Friday, May 11, 2012 4:35 AM}

Below link describes how to do data table pagination

http://msdn.microsoft.com/en-us/library/tx1c9c2f(v=vs.80).aspx

Regards,
Ahmed Ibrahim
SQL Server Setup Team
This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
This can be beneficial to other community members reading the thread.

_{Friday, May 11, 2012 5:12 AM}

hi there,
here's a link that describes the way to paginate results and to retrieve only a specific amount of records each time a certain page is chosen...
http://www.codeproject.com/Articles/12338/Using-ROW_NUMBER-to-paginate-your-data-with-SQL-Se

Peter Koueik

_{Friday, May 11, 2012 6:01 AM}

The most easiest way to save a datatable to disk is wrap it into a dataset and to write it then using Write XML 

DataSet ds = new DataSet();
ds.Tables.Add(dataTable);
ds.WriteXML("theFilePath");

typed in his message so watch typos.

Success
Cor

_{Monday, May 14, 2012 6:19 AM}

Before I can load the dataset, I have to read the 4.5 records from the database first. I getting an System out of memory exception while reading records.

_{Monday, May 14, 2012 6:23 AM}

So how to get pass the system out of memory exception while reading the records from the database, are saying write a query using the TOP command until all records are process. What are suggesting?

_{Monday, May 14, 2012 6:36 AM}

Do you have specific reason to use a DataTable? Things would be simpler and faster if you simply read data from the data reader and then write it to directly to the file.

_{Thursday, May 17, 2012 8:26 PM}

The actually what I tried to do in the first place, I never make it out of the while loop below. I get the system out of memory exception

SqlDataReader dr = cmd.ExecuteReader();

while(dr.Read())

{

counter++;

//I get system out of memory exception when counter == 1.5 million records, so i never get to do anything from this point. Keep in

//mind there is 4.5 million records in the database, this is what i am trying to get passed.

}