Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Linq System.OutofMemoryException

I have a long running process in C# that hits a Sql table anywhere from 10 to 200 times. When the process exceeds about 50 hits and queries greater than about 100,000 rows from the same table each time, it will throw a System Out of Memory Exception at this line, specifically at the bottom where it converts the IQuery object to a List:

var cht = from p in _db.TickerData
          where p.Time >= Convert.ToDateTime(start) &&
          p.Time <= Convert.ToDateTime(end)
          orderby p.Time
          select p;

_prices = cht.ToList();    < this is where the System.OutofMemoryException occurs >

What can I do to prevent this error??

like image 923
CraigJSte Avatar asked Dec 09 '22 01:12

CraigJSte


1 Answers

First off:

specifically at the bottom where it converts the IQuery object to a List

Yes, that's where you'd expect the Out Of Memory condition to occur.

The assignment of cht above doesn't actually hit the database; all it does is declare the shape of the query. This is called deferred execution and LINQ uses it all over the place. It means "we don't actually process anything until your code needs it."

Calling ToList, though, essentially says "the code needs it, all of it, right now." So that's where it sends the query to the database, pulls back all the results all at once, uses LINQ magic to turn them into CLR objects, and stuff them all in a List<T> for you.

Having said that, this is just a hunch, but it's possible that your LINQ provider doesn't know what Convert.ToDateTime is. If it doesn't know how to handle that, it won't put it into the WHERE clause in the query it executes, and instead it will load the entire table and filter it client-side, which might be why you crash when the table gets too big, rather than when the result set gets too big.

To verify this, use a profiler for your database to intercept the query, and see if the WHERE clause looks like you'd expect. If it's not translating right, try this instead:

var startTime = Convert.ToDateTime(start);
var endTime = Convert.ToDateTime(end);
var cht = from p in _db.TickerData
          where p.Time >= startTime && p.Time <= endTime
          orderby p.Time
          select p;
_prices = cht.ToList();

If that doesn't help, you're probably just pulling back too much data, and you'll have to work on that the same ways you'd work with processing too much data in any other context.

like image 84
Mason Wheeler Avatar answered Dec 10 '22 14:12

Mason Wheeler