EF4 - Eager Loading slower than Lazy Loading?

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("s开发者_JAVA百科ervice_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.

---

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.

---

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.