Processing large text (JSON) file

Question

I have a requirement to allow my Intranet .NET web portal users to send free-text SQL query to the backend (Read-only DB on SQL Server 2014) and get the results in Excel, while this works fine for most cases but the code fails when the results are too big (around 350mb, 250k records) to be processed.

My first attempt was to get the results directly as JSON serialized into a data table on the frontend.

That failed since iterating through the results set would throw System.OutOfMemoryException:

private JavaScriptSerializer _serializer;
return _serializer.Serialize(results));

So decided its not a good thing anyway to display this amount of results on the interface directly since IE will struggle. So went to the option of prompting users to download an Excel copy of the output by saving the results into a JSON file then read the file and convert it to Excel:

using (StreamReader sr = new StreamReader(filePath))
String json;
// Read and display lines from the file until the end of 
// the file is reached.
while ((json = sr.ReadLine()) != null)
{
    Console.WriteLine(json);}
}

However the ReadLine() method is throwing the same exception, it is good to note that the ReadLine is failing due to the fact the file consists of only one line otherwise I would try and iterate over the lines line by line.

Lastly I tried access the IEnumerable directly to write it into Excel

var results = new ReportProcess().ExecuteSql(sqlQuery, out string eventMessage);
List<object> queryObjects = new List<object>();


            foreach (var result in results)
            {
                queryObjects.Add(result);
            }

            var row = queryObjects.FirstOrDefault();
            if (row != null)
            {
                var recordType = row.GetType();

                using (var excelFile = new ExcelPackage())
                {
                    MemberInfo[] membersToInclude = recordType
                        .GetProperties(BindingFlags.Instance | BindingFlags.Public)
                        .ToArray();

                    var worksheet = excelFile.Workbook.Worksheets.Add("Sheet1");

                    worksheet.Cells.LoadFromCollection(queryObjects, true,
                        OfficeOpenXml.Table.TableStyles.None,
                        BindingFlags.Instance | BindingFlags.Public,
                        membersToInclude);


                    fileName = Guid.NewGuid() + ".xlsx";
                    excelFile.SaveAs(new FileInfo(HttpContext.Current.Server.MapPath("~/temp/") + fileName));

                }
            }

And again the code fails around

foreach (var result in results)
{
   queryObjects.Add(result);
}

With the same exception

So now I'm stuck around the fact that no matter what I try to do to iterate through the IEnumerable resuls, I would always get the OutOfMemory Exception.

I have also tried to increase the memory allocated to objects by setting the gcAllowVeryLargeObjects to true in the web.config but to no avail:

 <runtime>
    <gcAllowVeryLargeObjects enabled="true"/>
 </runtime>

Other attempt:

Googling around did not bring anything to resolve the issue, any suggestions/ideas?

Might want to check that nothing here helps you with the out of memory exception https://stackoverflow.com/questions/8563933/c-sharp-out-of-memory-exception — Fuzzybear, Jun 27 '18 at 11:33
Id say you are double handling this file somehow allocating it more than once, and you are compiling it in 32bit — TheGeneral, Jun 27 '18 at 11:35
@Maya it doesn't look as so big. Have you tried other json serializes such as Jil? — Alex Riabov, Jun 27 '18 at 11:43
If there would be a sort criteria for each table which can be requested, the SQL could get the results chunk by chunk: `select * from table order by field offset 0 rows fetch next 1000 rows only;`, `select * from table order by field offset 1000 rows fetch next 1000 rows only;`... — Axel Richter, Jun 27 '18 at 11:48
@AxelRichter This is a free-text queries, I can not control or post-handle user queries — Maya, Jun 27 '18 at 12:06
An unrelated question since you are allowing your users to send SQL DCL how are you preventing malicious SQL injection and SQL scripts attacks? @maya — Mark Kram, Jun 27 '18 at 12:07
@MarkKram Again, this is an internal portal against a read-only db — Maya, Jun 27 '18 at 12:08
you can directly pass results collection to the LoadFromCollection function, why you are creating List — Nitin Sawant, Jun 27 '18 at 12:12
@NitinSawant LoadFromCollection takes a List of objects, it doesnt take IEnumerable directly — Maya, Jun 27 '18 at 12:35

score 0 · Answer 1 · answered Jun 28 '18 at 12:40

Eventually I had to rewrite my code to implement external libraries to serialize CSV using CsvHelper library

using (StreamReader sr = new StreamReader(filePath))
                {
                    var csvReader = new CsvReader(sr);
                    var records = csvReader.GetRecords<object>();
                    var result = string.Empty;
                    try
                    {
                        return JsonConvert.SerializeObject(new ServerData(records, _eventMessage));
                    }
                    catch (Exception ex)
                    {
                        _eventMessage.Level = EventMessage.EventLevel.Error;
                    }


                    return _serializer.Serialize(new ServerData(result, _eventMessage));
                }

That seems to work fine with large data set, OutOfMemory Exception is no longer appearing

Processing large text (JSON) file

1 Answers1