How can we optimize this linq to entity query to decrease the response time?

Question

IQueryable<WebEvent> mySearch = 
    eventDC.GetBooks()
        .Where(p => p.Price.Any(d => d.EventDatetime.Month == fromDate.Month 
                                     && d.EventDatetime.Year == fromDate.Year))
        .WithGroup(groupId)
        .OrderBy(p => p.Price.Where(r => r.Datetime >= fromDate)
                             .OrderBy(q => q.Datetime)
                             .FirstOrDefault().Datetime);
List<Book>ventsList = mySearch.ToList<Book>();

We have such a long query, and it consume much time to get the books and sorting, after performance test , we found response time for the page which contains this query exceed 10 seconds, and we need to seek to solve this and reduce the response time.

Do anyone have any suggestions ?

Answer 1

Typically examine the SQL to see what it's producing, which you can do inline. There is a tool that can help you do that, it's called LinqPad, and you can create a LINQ query and play around with tweaking the LINQ query. Also, looking for places to add indexes; this can speed up performance too (too many indexes can hurt performance so be careful too).