Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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);

Thanks in advance.

share|improve this question
6  
I hope there is a strong reason why db.Oportunities has a StatusReason_ID and not a StatusReason, a Lead_ID and not a Lead, etc. Because all these joins? "POOF GONE!" –  ANeves Dec 4 '12 at 18:06
1  
What @ANeves said. Also, ordering then grouping probably isn't going to be doing what you want, especially since you're then transforming it later. –  Bobson Dec 4 '12 at 19:00
1  
Also, let latestAppointmentStatus = g.FirstOrDefault(x => x.t4.AppointmentStart == latestAppointment).t4.AppointmentStatus.Status_Name will throw a NullReferenceException under some circumstances. –  Bobson Dec 4 '12 at 19:11
1  
Does the backing model have any navigation properties defined? Or it's a model with just tables thrown in and no relationships? –  Jeroen Dec 6 '12 at 3:54
add comment

3 Answers

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.

share|improve this answer
add comment

Here is my approach which has helped one of my friends to boost the performance of a similar complex query by 450%, yes I've typed it correctly 450%

1- while opening your project in VisualStudio, set a break point at the beginning of your query. 2- start with debugging (F5).

3- wait till the excution hit your breakpoint (hold on).

3- Run SQL profiler.

4- execute your query and wait till the execution is finished..

5- grab the SQL query that the SQL profiler has caught.

6- open a new query window in SQL server.

7- paste your generated SQL query.

8- Right click, then select "Analyze Query in Database Engine Tuning Advisor".

9- when the "Database Engine Tuning Advisor"" hit "Start Analysis".

10- 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

11- if the wizard suggested some sort of recommendations, I highly recommend you to apply them, open up "Tools=> Apply Recommendations"

finally, let me know if this has helped you, thanks.

share|improve this answer
    
Great comments, but the OP said he wasn't ready to start changing the database. –  Jeff Vanzella Dec 6 '12 at 4:44
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.