LINQ to Entities query with lots of grouping

Question

I have been presented with a simple but slow performing LINQ to Entities query which I've pasted below.

Could anybody give me some pointers on how to improve performance? The query below has been significantly improved since I inherited it but I don't know what else I can do without moving to the DB (which isn't an option at this stage)

var created = DateTime.Now.AddMonths(-6);

            return (from t1 in db.Opportunities
                    from s1 in db.OpportunityStatus.Where(x => x.OpportunityStatus_ID == t1.StatusReason_ID)
                    from t2 in db.Leads.Where(x => x.Lead_ID == t1.Lead_ID)
                    from t3 in db.Tasks.Where(x => (x.Type_ID == 4) && (x.Item_ID == t1.Opportunity_ID)).DefaultIfEmpty()
                    from t4 in db.TaskAppointments.Where(x => x.Parent_Task_ID == t3.Task_ID).DefaultIfEmpty()
                    from t5 in db.Addresses.Where(x => x.Address_ID == t4.Address_ID).DefaultIfEmpty()
                    from n1 in db.Notes.Where(x => x.Type_ID == 4 && (x.Item_ID == t1.Opportunity_ID)).DefaultIfEmpty()
                    from n2 in db.Notes.Where(x => x.Type_ID == 5 && (x.Item_ID == t1.Lead_ID)).DefaultIfEmpty()
                    from t6 in db.Quotations.Where(x => x.OpportunityId == t1.Opportunity_ID).DefaultIfEmpty()
                    from u1 in db.UserNames.Where(x => x.User_Username == t1.Owner).DefaultIfEmpty()
                    from u2 in db.UserNames.Where(x => x.User_Username == t2.Owner).DefaultIfEmpty()

                    where (t1.Company_ID == company) && (t1.Created >= created)

                    orderby (t1.Created) descending

                    group new { t1, t3, t4, s1, t6, u1, u2, n1, n2, t5 } by new
                    {
                        t1.Opportunity_Title,
                        t2.Company_Name,
                        s1.OpportunityStatus_Name,
                        ts = u1.Name,
                        fs = u2.Name
                    } into g

                    let latestAppointment = g.Max(uh => uh.t4.AppointmentStart)
                    let latestAppointmentStatus = g.FirstOrDefault(x => x.t4.AppointmentStart == latestAppointment).t4.AppointmentStatu.Status_Name
                    let latestAppointmentCity = g.FirstOrDefault(x => x.t4.AppointmentStart == latestAppointment).t5.City

                    let latestNoteDate = g.Max(uh => uh.n2.Date)
                    let latestNote = g.FirstOrDefault(x => x.n2.Date == latestNoteDate).n2.Note_Text

                    let latestOpportunityNoteDate = g.Max(uh => uh.n1.Date)
                    let latestQuotationDate = g.Max(uh => uh.t6.Created)

                    select new PipelineViewModel
                    {
                        Id = 0,
                        CompanyName = g.Key.Company_Name,
                        OpportunityTitle = g.Key.Opportunity_Title,
                        CompanyCity = latestAppointmentCity,
                        OpportunityStatusName = g.Key.OpportunityStatus_Name,
                        LastAppointmentDate = latestAppointment,
                        LastAppointmentStatus = latestAppointmentStatus,
                        LastOpportunityNoteDate = latestOpportunityNoteDate,
                        LastNoteDate = latestNoteDate,
                        LastNote = latestNote,
                        LastQuoteDate = latestQuotationDate,
                        TelesalesUserName = g.Key.ts,
                        FieldSalesUserName = g.Key.fs
                    }).Take(howMany);

Answer 1

This can't be called "simple". And it's not possible to tell where bottleneck is because of so many joins and subqueries.

Subqueries are certainly suspect, you can optimize them by using Dictionary<> (as you would add indexes in real DB).

I would also try to split this huge query into small ones, put ToArray() at the end of each one, and run a profiler to get some hints on what is slow.

Answer 2

Here is my approach which has helped one of my friends to boost the performance of a similar complex query by 450%:

1. While opening your project in Visual Studio, set a breakpoint at the beginning of your query.
2. Start with debugging F5.
3. Wait till the execution hit your breakpoint (hold on).
4. Run SQL profiler.
5. Execute your query and wait till the execution is finished.
6. Grab the SQL query that the SQL profiler has caught.
7. Open a new query window in SQL server.
8. Paste your generated SQL query.
9. Right click, then select "Analyze Query in Database Engine Tuning Advisor".
10. When the "Database Engine Tuning Advisor"" hit "Start Analysis".
11. It may take some time to look for suggestions to optimize your query, but when finished, it will tell you the recommended performance gain when applying the recommended modifications.
12. If the wizard suggested some sort of recommendations, I highly recommend you to apply them, open up "Tools=> Apply Recommendations"

Answer 3

You have gone through the hard way when you should follow the easy way!! Why do you need to load so much data all at once??

My suggestion is to break that (stupendous) query into smaller queries and call each one when is needed...(even if you need all at the same time).

But before anything, take a picture of that piece of art! :D