0

EDIT: Instead of assuming and randomly saying its a duplicate please check and verify yourself. This doesn't help out users when you immediately downvote and say its a duplicate. Those responses are not doing what I am doing. I have tried the already.

Instead of getting OOM at 700k records it does it at 800k records. So that response did not help.

I am trying to retrieve a ton of rows from a database via a SP and I keep getting Out of Memory Exception on reader.Read() when my class Object reaches a certain limit.

  SqlCommand cmd = new SqlCommand("usp_GetTonofData", connection);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                SqlCommandBuilder.DeriveParameters(cmd);                 
                cmd.Parameters[1].Value = "Blah";
                cmd.Parameters[2].Value = "foo";
                cmd.CommandTimeout = 1000;              
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);               

                while (reader.Read())
                {
                    object[] values = new object[reader.FieldCount];
                    reader.GetValues(values);
  OOM Exception occurs here around the 700k records mark ---> var category = new DataClass()
                    {
                        Prop1 = values[0].ToString(),
                        Prop2 = Convert.ToDateTime(values[1]).ToString("MM/dd/yyyy"),
                        Prop3 = Convert.ToDateTime(values[2]).ToString("MM/dd/yyyy"),
                        Prop4 = Convert.ToDateTime(values[3]).ToString("MM/dd/yyyy"),
                        Prop5 = values[4].ToString(),
                        Prop6 = values[5].ToString(),
                        Prop7 = values[28].ToString(),
                        Prop8 = values[29].ToString(),
                        Prop9 = values[30].ToString(),
                        Prop10 = values[31].ToString(),
                        Prop11 = values[32].ToString(),
                        Prop12 = values[33].ToString(),
                    };

                    storedProcedureList.Add(category);
                }

                connection.Close();
                connection.Dispose();    
            }

      return storedProcedureList;
CodeMan03
  • 570
  • 3
  • 18
  • 43
  • try datatable and read all records in it – Pranay Rana Apr 03 '18 at 17:48
  • 1
    The real question is what are you going to do with that many records in memory? Is a user going to read all that data on the screen (you mentioned at least 700K of records)? I doubt it. If you want to do some analytics on it then do that on the database side (aggergation and such). If you want to export it then write it to a file/network stream and do not keep the data in memory. – Igor Apr 03 '18 at 17:57
  • The user wants all of this data in an excel file – CodeMan03 Apr 03 '18 at 18:03
  • Then create the Excel file as a `FileStream` and write each record to the file one at a time. Do not keep the record data in memory. – Igor Apr 03 '18 at 18:16
  • Users also want to see it in a grid that our app is using with angular. So Excel is the final product but the grid on display is the intermedium. – CodeMan03 Apr 03 '18 at 22:24
  • 1
    I am sure the users cannot view 800k rows at the same time. There is a reason why paging and infinite scroll were invented. This is a duplicate - there is no way to "solve" an OOM exception. You have to use common sense to keep your application from using all of the virtual memory on the system. – NightOwl888 Apr 04 '18 at 01:21
  • Even if your server side code did not crash with an OOM your browser would crash or would become so unresponsive that users would be unable to use the application. The solution has been stated: 1) Write to a FileStream (or networkstream etc) for exporting and 2) Use paging controls with anything UI related to only return a managable chunk of data for the users (like batches of 100 or 200 for example). These are both common solutions used when dealing with large number of records. – Igor Apr 04 '18 at 14:56

0 Answers0