CODEWITHSUNDEEP
c#linqentity-frameworkoptimizationlinq-to-entities
Paul Keister
Paul Keister

Reputation: 13077

Is there any way to optimize this LINQ to Entities query?

I was asked to produce a report that is driven by a fairly complex SQL query against a SQL Server database. Since the site of the report was already using Entity Framework 4.1, I thought I would attempt to write the query using EF and LINQ:

var q = from r in ctx.Responses
                    .Where(x => ctx.Responses.Where(u => u.UserId == x.UserId).Count() >= VALID_RESPONSES)
                    .GroupBy(x => new { x.User.AwardCity, x.Category.Label, x.ResponseText })
         orderby r.FirstOrDefault().User.AwardCity, r.FirstOrDefault().Category.Label, r.Count() descending
         select new
         {
             City = r.FirstOrDefault().User.AwardCity,
             Category = r.FirstOrDefault().Category.Label,
             Response = r.FirstOrDefault().ResponseText,
             Votes = r.Count()
         };

This query tallies votes, but only from users who have submitted a certain number of required minimum votes.

This approach was a complete disaster from a performance perspective, so we switched to ADO.NET and the query ran very quickly. I did look at the LINQ generated SQL using the SQL Profiler, and although it looked atrocious as usual I didn't see any clues as to how to optimize the LINQ statement to make it more efficient.

Here's the straight TSQL version:

WITH ValidUsers(UserId)
AS
(
    SELECT UserId
    FROM Responses
    GROUP BY UserId
    HAVING COUNT(*) >= 103
)
SELECT d.AwardCity
    , c.Label
    , r.ResponseText
    , COUNT(*) AS Votes
FROM ValidUsers u
JOIN Responses r ON r.UserId = u.UserId
JOIN Categories c ON r.CategoryId = c.CategoryId
JOIN Demographics d ON r.UserId = d.Id
GROUP BY d.AwardCity, c.Label, r.ResponseText
ORDER BY d.AwardCity, s.SectionName, COUNT(*) DESC

What I'm wondering is: is this query just too complex for EF and LINQ to handle efficiently or have I missed a trick?

Upvotes: 5

Views: 1604

Answers (2)

Esteban Elverdin
Esteban Elverdin

Reputation: 3582

Maybe this change improve the performance, removing the resulting nested sql select in the where clause

First get the votes of each user and put them in a Dictionary

var userVotes = ctx.Responses.GroupBy(x => x.UserId )
                             .ToDictionary(a => a.Key.UserId,  b => b.Count());

var cityQuery = ctx.Responses.ToList().Where(x => userVotes[x.UserId] >= VALID_RESPONSES)
               .GroupBy(x => new { x.User.AwardCity, x.Category.Label, x.ResponseText })
               .Select(r => new
                       {
                           City = r.First().User.AwardCity,
                           Category = r.First().Category.Label,
                           Response = r.First().ResponseText,
                           Votes = r.Count()
                       })
               .OrderByDescending(r => r.City, r.Category, r.Votes());

Upvotes: 1

jessehouwing
jessehouwing

Reputation: 114671

Using a let to reduce the number of r.First()'s will probably improve performance. It's probably not enough yet.

 var q = from r in ctx.Responses
                .Where()
                .GroupBy()
     let response = r.First()
     orderby response.User.AwardCity, response.Category.Label, r.Count() descending
     select new
     {
         City = response.User.AwardCity,
         Category = response.Category.Label,
         Response = response.ResponseText,
         Votes = r.Count()
     };

Upvotes: 4

Related Questions