Why is the SQL produced by LINQ-to-Entities so inefficient?

Question

The following (cut down) code excerpt is a Linq-To-Entities query that results in SQL (via ToTraceString) that is much slower than a hand crafted query. Am I doing anything stupid, or is Linq-to-Entities just bad at optimizing queries?

I have a ToList() at the end of the query as I need to execute it before using it to build an XML data structure (which was a whole other pain).

var result = (from mainEntity in entities.Main
              where (mainEntity.Date >= today) && (mainEntity.Date <= tomorrow) && (!mainEntity.IsEnabled)
              select new
              {
                  Id = mainEntity.Id,
                  Sub =
                      from subEntity in mainEntity.Sub
                      select
                      {
                          Id = subEntity.Id,
                          FirstResults =
                              from firstResultEntity in subEntity.FirstResult
                              select new
                              {
                                  Value = firstResultEntity.Value,
                              },
                          SecondResults =
                              from secondResultEntity in subEntity.SecondResult
                              select
                              {
                                  Value = secondResultEntity.Value,
                              },
                          SubSub =
                              from subSubEntity in entities.SubSub
                              where (subEntity.Id == subSubEntity.MainId) && (subEntity.Id == subSubEntity.SubId)
                              select
                                  new
                                  {
                                      Name = (from name in entities.Name
                                              where subSubEntity.NameId == name.Id
                                              select name.Name).FirstOrDefault()
                                  }
                          }
              }).ToList();

While working on this, I've also has some real problems with Dates. When I just tried to include returned dates in my data structure, I got internal error "1005".

Answer 1

Just as a general observation and not based on any practical experience with Linq-To-Entities (yet): having four nested subqueries inside a single query doesn't look like it's awfully efficient and speedy to begin with.

I think your very broad statement about the (lack of) quality of the SQL generated by Linq-to-Entities is not warranted - and you don't really back it up by much evidence, either.

Several well respected folks including Rico Mariani (MS Performance guru) and Julie Lerman (author of "Programming EF") have been showing in various tests that in general and overall, the Linq-to-SQL and Linq-to-Entities "engines" aren't really all that bad - they achieve overall at least 80-95% of the possible peak performance. Not every .NET app dev can achieve this :-)

Is there any way for you to rewrite that query or change the way you retrieve the bits and pieces that make up its contents?

Marc

Answer 2

Have you tried not materializing the result immediately by calling .ToList()? I'm not sure it will make a difference, but you might see improved performance if you iterate over the result instead of calling .ToList() ...

foreach( var r in result )
{
  // build your XML
}

Also, you could try breaking up the one huge query into separate queries and then iterating over the results. Sending everything in one big gulp might be the issue.