Entity Framework: performance issue regarding Loading Master and Detail tables

Question

We have use Entity framework 1.0.

We have a Master and Detail table. Detail take is many to one to the Master table.

If during query Master table and include the Detail table as following:

Logs = EntityContext.Log.Where("it.logID > 0").Include("LogDetails").OrderBy("it.logID DESC");



foreach (Log r in Logs)

   _Logs.Add(r);

in the UI we can use Logs.logDetails (collection) to get log detail information.

If we loading 5,000 logs, it takes 190,284 ms. It more than 3 minutes. We need to support 10,000 logs, so more than 3 minutes just loading log tables is not acceptable!

However, if we do not include detail table in the query such as following code, loading 10,000 only takes 735 ms.

Logs = EntityContext.Log.Where("it.logID < 10001").OrderBy("it.logID DESC");

foreach (Log r in Logs)

   _Logs.Add(r);

The displayed view includes Master Log List (List View ) and log detail (grid).

However, we only need each log detail information when user select one of Master log item.

We prefer to use the second query to get master run logs.

How to query the Detail table so we can improve the loading performance?

thanks, Jane

Answer 1

If we seperated loading the detail table, it takes 1,372 ms to loading 31,301 detail records. So at least we should separated queries to load the Master and the Detail tables.

var LogDetails = EntityContext.logDetail.Where("it.logDetailID > 0").OrderBy("it.logDetailID DESC");
    if (LogDetails == null)
     throw new Exception("Can not get log details from the Database");

    _LogDetails = new List<logDetail>();
    foreach (logDetail r in LogDetails)
     _LogDetails.Add(r);

 

Answer 2

Hi Jane,

 

Glad to see you again!

 

For such a performance issue on the .Include method, I remember I once suggested you to use EntityReference.Load() to load the related master entity in this thread.   Now, I think we can also use EntityCollection.Load() to load the detail entities when the user select certain master entity object.  

 

Besides, I think I should mention the new lazy loading feature in EF4.   With lazy loading, the related entities are loaded only when we try to access them via navigation properties.   So you just need to query the master entities and bind the query result to the data control.   Additional reference about lazy loading in EF4: http://geekswithblogs.net/iupdateable/archive/2009/11/26/getting-started-with-entity-framework-4-ndash-lazy-loading.aspx.  

 

If you have any questions, please feel free to let me know.

 

Have a great day!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 3

Hi Lingzhi,

Thanks for your info.

One thing we concern about separetely querying/loading Master and Detail tables is we load all detail table data into memory.

It is OK for application, but it may not be the best solution. We are glad that you mention different way to do that in EF4.

Does the EntityCollection.Load() is the EF4 feature?

We plan to move to EF4 in near future.

So far we still use EF1. for EF1, it seems that it is not good idea include loading Detail table when query master table entity data.

It is too slow.

thanks,Jane

Answer 4

Hi Jane,

 

Sorry for the delay.   It is not new feature in EF4 and we can use it in EFv1 as well.   As my last post suggested, the new feature in EF4 is the lazy loading feature.   The related entities will be automatically loaded whenever we try to access them.  

 

Hope you have a nice day!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.