EF 5 large data load strategy

Question

Good day,

I have the following tables

PARENT 1=>N CHILDREN 1=>N GRANDCHILDREN.

Both tables have over 30 columns.

I need to select over 50,000 records form PARENT, plus I will need certain fields from CHILDREN and GRANDCHILDREN. Data is needed to manipulate in memory (complex algorithms on what's been selected).

I am using Entity Framework 5.

I tried various combinations of Eager loading (Include, projection etc), but I am still not able to make it perform better then it perorms with LINQ-to-SQL in the following scenario:

" SELECT from PROJECTS

on binding of each row: SELECT from CHILDREN SELECT from GRANDCHILDREN

"

it generates at least 50,001 calls to the DB, but it's still performing better then any of my EF approaches, which take over x5 longer than the current LINQ-to-SQL design.

The best solution would be to have an WHERE IN query on children, but it's not available in EF 5 in native implementation (contains doesn't cut it - too slow for badly done...).

Any ideas will be greatly appreciated.

Thanks,

Agreed, no one is going to sift through 50,000 rows given to them. You need to look into paging and/or filtering before showing on the UI. — gunr2171, Oct 02 '13 at 16:44
Can you add a stored procedure or view to the database and add that to your model? — Brian P, Oct 02 '13 at 16:47
Forget about the grid... I need all that data in memory to do some complex data manipulations. — agfc, Oct 02 '13 at 16:48
@AlexeiFimine, then you are better off doing as much of the calculations as you can in sql before you pull the data back. Use a stored procedure if you can, and you can pass in inputs if needed. — gunr2171, Oct 02 '13 at 16:50
I don't think so he is asking about showing those records. he is asking about efficient way of loading 50K+ records in memory and then doing some algorithm on the resultant object via LINQ or some other logic.You can check http://stackoverflow.com/questions/8107439/entity-framework-4-1-most-efficient-way-to-get-multiple-entities-by-primary-key/8108643#8108643 — Kamran Shahid, Oct 02 '13 at 16:59

score 1 · Accepted Answer · answered Oct 02 '13 at 16:48

I assume you are implementing paging in your grid view and are not puting thousands of rows into a grid view at once. If so, you can only select 10 or however many rows you are displaying in the grid view at a time. This will be a lot easier to work with.

I found this example on MSDN that implements paging server side to reduce the number of rows returned in a single query.

You can also consider writing or having a dba write an efficient stored procedure that you can link to your entity framework to control the SQL Code.

Thanks for posting the link! – Mark Kram Oct 08 '13 at 15:27 — Mark Kram, Oct 08 '13 at 15:27

score 0 · Answer 2 · answered Oct 02 '13 at 17:05

I had similar issue some days ago. EF is very slow. After some experiments I received more or less normal performans with direct queries:

Create ViewModel with needed fields:

public class MyViewModel
{
  public string one {get; set;}
  public string two {get; set;}
}

Then in controller action:

MyViewModel result = db.Database.SqlQuery<MyViewModel>
                    ("SELECT a.one, b.two" +
                    " FROM Table1 a, Table2 b" +
                    " WHERE a.id == somthing"
                    ).FirstOrDefault();

score 0 · Answer 3 · answered Oct 08 '13 at 14:54

Paging wouldn't work for I need data to be sorted based on a calculated field. The field can be only calculated in the web-server memory for the calculation needs client info (yes, yes, there is a way of passing this info to the db server, but this wasn't an option).

Solution: using(var onecontext = new myCTx()) { SELECT all from PROJECTS and implement Context.EntityName.SQLQuery() on all grand children, using the good old WHERE IN construct (I put it all into my entities' partial classes as extensions).

}

this way I get all my data in N db trips, where N is the number of generations, which is fine. The EF context then connects everything together. And then I perform all my r

EF 6 should have WHERE IN built in, so I guess this approach will become more obvious then. Mind you: using Contains() is not an option for large data for it produces multiple OR's instead of the straight IN. Yes, ADO.NET then translates OR's into IN, but before that there is some really heavy lifting being done, which is killing your app server.

EF 5 large data load strategy

3 Answers3