Linq to Entities performance problem with many columns

Question

I am having an issue with getting linq to entities to perform well. The query I have (not mine, maintaining someone's code :-)), has several includes that I've determined are all necessary for the WPF screen that consumes the results of this query.

Now, the SQL generated executes very fast and only returns one row of data. But it is returning 570 columns, and i think the performance hit is in the overhead of creating all the objects and all of those fields.

I've tried using lazy loading, but that doesn't seem to have any effect on performance.

I've tried removing any of the "include" statements that aren't necessary, but it appears that they all are needed.

here's the linq query:

var myQuery = 
                from appt in ctx.Appointments
                               .Include("ScheduleColumnProfile")
                               .Include("EncounterReason")
                               .Include("Visit")
                               .Include("Visit.Patient")
                               .Include("Visit.Patient.PatientInsurances")
                               .Include("Visit.Patient.PatientInsurances.InsuranceType")
                               .Include("Visit.Patient.PatientInsurances.InsuranceCarrier")
                               .Include("MasterLookup")
                               .Include("User1")
                               .Include("User2")
                               .Include("Site")
                               .Include("Visit.Patient_CoPay")
                               .Include("Visit.Patient_CoPay.User")
                               .Include("Visit.VisitInstructions.InstructionSheet")
                    where appt.VisitId == visitId 
                        && appt.MasterLookup.LookupDescription.ToUpper() != Rescheduled 
                        && appt.Site.PracticeID == practiceId
                        && appt.MasterLookup.LookupDescription.ToUpper() != Cancelled
                    orderby appt.AppointmentId descending
                    select appt;

The SQL generate is 4000 lines long with 570 columns in the select statment and 3 or 4 Union ALLs, so I'm not going to paste it here unless someone REALLY wants to see it. Basically, i'm looking for a way to get rid of the unions if possible, and trim down the columns to only what's needed.

Help!

:-)

Read here: http://stackoverflow.com/questions/5521749/how-many-include-i-can-use-on-objectset-in-entityframework-to-retain-performance/5522195#5522195 — Ladislav Mrnka, Aug 31 '11 at 17:10
Thanks, that helped explain the impact of using .Include. So, would the solution then be to use objectContext.LoadProperty()? — Scot, Aug 31 '11 at 17:56
The solution is to play with multiple techniques and find which provides best performance in your scenario. — Ladislav Mrnka, Aug 31 '11 at 18:15

score 2 · Accepted Answer · answered Sep 02 '11 at 12:59

if anyone is keeping track, this is the solution that ended up working for me. Thanks to everyone who commented and made suggestions... it eventually lead me to what i have below.

            ctx.ContextOptions.LazyLoadingEnabled = true;

            var myQuery =
                from appt in ctx.Appointments
                where appt.VisitId == visitId
                    && appt.MasterLookup.LookupDescription.ToUpper() != Rescheduled
                    && appt.Site.PracticeID == practiceId
                    && appt.MasterLookup.LookupDescription.ToUpper() != Cancelled
                orderby appt.AppointmentId descending
                select appt;


            var myAppt = myQuery.FirstOrDefault();

            ctx.LoadProperty(myAppt, a => a.EncounterReason);
            ctx.LoadProperty(myAppt, a => a.ScheduleColumnProfile);
            ctx.LoadProperty(myAppt, a => a.Visit);
            ctx.LoadProperty(myAppt, a => a.MasterLookup);
            ctx.LoadProperty(myAppt, a => a.User1);
            ctx.LoadProperty(myAppt, a => a.User2);
            ctx.LoadProperty(myAppt, a => a.PatientReferredProvider);

            var myVisit = myAppt.Visit;

            ctx.LoadProperty(myVisit, v => v.Patient);
            ctx.LoadProperty(myVisit, v => v.Patient_CoPay);
            ctx.LoadProperty(myVisit, v => v.VisitInstructions);
            ctx.LoadProperty(myVisit, v => v.EligibilityChecks);

            var pat = myVisit.Patient;

            ctx.LoadProperty(pat, p => p.PatientInsurances);


            //load child insurances
            foreach (PatientInsurance patIns in myAppt.Visit.Patient.PatientInsurances)
            {
                ctx.LoadProperty(patIns, p => p.InsuranceType);
                ctx.LoadProperty(patIns, p => p.InsuranceCarrier);
            }

            //load child instruction sheets
            foreach (VisitInstruction vi in myAppt.Visit.VisitInstructions)
            {
                ctx.LoadProperty(vi, i => i.InstructionSheet);
            }

            //load child copays
            foreach (Patient_CoPay coPay in myAppt.Visit.Patient_CoPay)
            {
                ctx.LoadProperty(coPay, c => c.User);
            }

            //load child eligibility checks
            foreach (EligibilityCheck ec in myAppt.Visit.EligibilityChecks)
            {
                ctx.LoadProperty(ec, e => ec.MasterLookup);
                ctx.LoadProperty(ec, e => ec.EligibilityResponse);
            }

score 0 · Answer 2 · answered Aug 31 '11 at 16:15

0

I would recommend creating a new Class that contains only the properties that you need to display. When you project to a new type you don't need to have Include statements, but you can still access the navigation properties of the entity.

var myQuery = from appt in ctx.Appointments
              where appt.VisitId == visitId 
                    && appt.MasterLookup.LookupDescription.ToUpper() != Rescheduled 
                    && appt.Site.PracticeID == practiceId
                    && appt.MasterLookup.LookupDescription.ToUpper() != Cancelled
              orderby appt.AppointmentId descending
              select new DisplayClass
              {
                 Property1 = appt.Prop1,
                 Proeprty2 = appt.Visit.Prop1,
                 .
                 .
                 .
              };

answered Aug 31 '11 at 16:15

Aducci

26,101
8
63
67

are there any other options? The page that consumes this is expecting the appointment and all the associated navigation properties. – Scot Aug 31 '11 at 16:27
@Scot - Is it actually checking to see if it is an Appointment entity? If it is just binding to certain property names you can make your DisplayClass entity with the same names. – Aducci Aug 31 '11 at 16:52
Yeah, it needs the appointment entity and its associated child objects. That is the problem i am running into, with the "include" it takes a long time to execute, without the include i can't access any of the those child objects – Scot Aug 31 '11 at 17:13
@Scot - I dont think you understand what I am saying. You can create your DisplayClass with all the same properties as your Appointment entity including even the child objects and you would just exclude the ones that are not being displayed – Aducci Aug 31 '11 at 17:18
I understand what you are saying. I'm not sure its a viable option at this point because I'd have to change anything that consumes this function to use the new DisplayClass type. Also would I not lose the ability to update the underlying data? This seems like a lot of loops to jump through just to help performance. – Scot Aug 31 '11 at 17:44

Linq to Entities performance problem with many columns

2 Answers2