EF4 - Eager Loading slower than Lazy Loading?

Question

I have a data model made up of several entities, all of them interlinked, all in all about 80 tables.

I'm starting to get the code in production shape, and in the process I am switching from lazy loading everything (it made my development life easier) to eager loading in cases where I don't need all the related data, or it simply represents too much data to be sent to the client, it's just a waste of bandwidth.

However, I just noticed that, for at least some entities, eager loading just what I need is dramatically slower than lazy loading all of the data.

Here is an example, I can load a "request" object either lazily:

db.ContextOptions.LazyLoadingEnabled = true;
request = db.requests.Where(rq => rq.idrequest == rID).FirstOrDefault();

or eagerly:

db.ContextOptions.LazyLoadingEnabled = false;
request = db.request_objects.Include("service_objects.task_objects.task_parameters.parameter_values.address1").Include("service_objects.task_objects.task_parameters.parameter_values.attachment").
                            Include("service_objects.task_objects.task_parameters.parameter_values.phone_nbrs").Include("service_objects.task_objects.task_parameters.parameter_values.stored_texts").
                            Include("service_objects.task_objects.parent_tasks").Include("service_objects.task_objects.contact_objects").
                            Include("service_objects.service_parameters.parameter_values.address1").Include("service_objects.service_parameters.parameter_values.attachment").
                            Include("service_objects.service_parameters.parameter_values.phone_nbrs").Include("service_objects.service_parameters.parameter_values.stored_texts").
                            Include("service_objects.stored_texts").
                            Include("request_attachments.attachment").Include("request_notes.note").
                            Include("request_contacts.contact_objects").Include("contact_objects").
                            Include("contact_objects1").Include("contact_objects2").
                            Include("request_objects_links.request_objects1").Include("stored_texts").
                            Include("company_objects").
                            Where(ro => ro.idrequest_objects == rID).FirstOrDefault();

In most cases, eager loading the request is hundreds or thousands of times (!!!) slower than lazily loading it, although more often than not lazy loading will load A LOT of extra data. I mean, using eager loading it takes between 2 and 3 seconds, whereas it takes less than 40 ms in most cases (often less than 10 ms) with lazy loading (I got those times using a System.diagnostics.Stopwatch).

I am no SQL expert, and don't know anything about SQL optimization, but I can't quite wrap my head around why loading less data is more expensive.

Any insight, or obvious mistake on my part? Thanks!

EDIT

Judging from Brokenglass's answer, I wasn't clear enough :o). This code is just part of a WCF service function, pretty straightforward:

[OperationContract]
public request LoadRequestByID(int rID)
{
  request res = null;
  try
  {
    DBEntities db = new DBEntities();
    res = db.request_objects.Where(ro => ro.idrequest_objects == rID).FirstOrDefault();
  }
  catch (Exception e)
  {
    //Error log
  }
  return res;
}

I decided to time it when I noticed that it took considerably longer to display a request's details on the client end (after calling the above function) when eager-loading as opposed to lazy-loading.

score 3 · Accepted Answer · edited May 23 '17 at 12:12

3

Well this is interesting. It is actually not true that eager loading loads less data. It loads much more because all your includes are joined together into single enormous data set. The problem of lazy loading is that each lazy loaded property results in additional database query = roundtrip to database. That can be ultra fast on your local machine but it can be damn slow once your database server will be elsewhere on your network.

So the main advices are:

Measure the performance in the real production like environment.
Make sure that whole your graph is really loaded with lazy loading. You can forget somewhere marking single navigation property as virtual and half of your data will not be loaded at all. (This should not happen if you have autogenerated entities from T4 template).
Make sure that you transfer data you will really need on the client. Transferring all data just to make everything easier is not always win-win solution.

edited May 23 '17 at 12:12

Community

1
1

answered Aug 02 '11 at 21:02

Ladislav Mrnka

360,892
59
660
670

Thanks for the response Ladislav. So if I understand correctly, when I lazy load the request, it initially only loads the base details coming from the "requests" table, then fires up 30+ queries to get the related properties and finally returns the assembled "request" entity to the client. I hadn't realized that. Your other post was interesting, I wasn't aware of the LoadProperty method, I'll see if it is faster using that. I'm content testing for an environment where the DB is hosted on the webserver at the moment, my first clients will be setup that way, but I'll need to keep that in mind. – Bourgui Aug 02 '11 at 22:09
Well, my first foray into explicit loading is so far a success. Thanks for your answer and your link, it really pointed me in the right direction – Bourgui Aug 03 '11 at 17:49

score 1 · Answer 2 · answered Aug 02 '11 at 17:51

1

You are comparing apples to oranges currently:

db.ContextOptions.LazyLoadingEnabled = true;
request = db.requests.Where(rq => rq.idrequest == rID).FirstOrDefault();

This just enables lazy loading, but the loading itself has not been done yet - all related entities have not been loaded until those properties are explicitly accessed.

The second case you illustrate would result in joins on the corresponding DB tables, which naturally is a lot slower than just taking one row from one table - but it would retrieve all the data that you need up front.

answered Aug 02 '11 at 17:51

BrokenGlass

158,293
28
286
335

Thanks for the response. This is actually just part of the service function that returns the request Entity to the client app. The function itself is pretty straightforward: `[OperationContract] public request_objects LoadRequestByID(int rID) { request res = null; try { DBEntities db = new DBEntities(); res = db.request_objects.Where(ro => ro.idrequest_objects == rID).FirstOrDefault(); } catch (Exception e) { //Error log } return res; }` ... – Bourgui Aug 02 '11 at 18:03
I would assume that it loads everything before sending it to the client, seeing as the lazy loading only happens when the data is in context. At least that's the way I understand it, but maybe I'm wrong? Either way, on the client end, it still takes a noticeably longer time to display the details of the request when eager-loading as opposed to lazy-loading (even though I only eager-load the data I use). Thanks! – Bourgui Aug 02 '11 at 18:09

EF4 - Eager Loading slower than Lazy Loading?

2 Answers2