Linq query over mysql table takes too long

Question

The following Linq Query joins over 6 tables and creates a List of 'AppointmentData' . Inside the joins , 'Appointment' table and 'Patient' table has the largest data . ( approx 15k for appointments and 5k for patients )

It's taking 50 seconds to execute this code.

IQueryable<Appointment> Appointments;

if (condition1)
{
    Appointments = _context.Appointment.Where(somecondition);
}
else
{
    Appointments = _context.Appointment.Where(othercondition);
}

AppointmentsData = (
    from 
        app in Appointments
    join 
        pat in _context.Patient
    on 
        app.IdPatient equals pat.Id
    join 
        doc in _context.Doctor
    on 
        app.IdDoctor equals doc.Id
    ...
    ...
    //* Around 5 more joins of the same type * // 
    ...

    select new Models.AppointmentData()
    {
        Id = app.Id,
        Patient = pat.FullName,
        Doctor = doc.FullName,
        ...
        ...
        ...
        /* around 15 more fields from different tables 
        that were joined */
        .....


    }
).ToList();

I've tried using a smaller version of the database , with 2k appointments and 1k patients and it takes less than 3 seconds.

I have omitted a few conditions because they were confusing and I'm sure they're not related to the problem.

Have you checked that there are appropriate indexes in the database? Have you looked at the SQL that EF is generating? Do you really need to join all that stuff at that point? — Steve Todd, May 21 '19 at 16:58
just primary/ foreign keys . I don't know how to check the generated sql but i'll google it, and yes I need to join all that stuff . — Christian Bejarano, May 21 '19 at 17:03
Take a look here: https://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework which shows you how to view the underlying SQL. I'm not familiar with the toolset for MySQL, but given a raw SQL string does it have anything to help you optimise the query? — Steve Todd, May 21 '19 at 17:08
The answer below gives you the basics of it, but the important thing to remember is that any field that takes part in your WHERE clause should have an index. — Steve Todd, May 21 '19 at 17:13
You can always try parallel linq if it makes sense too, if it genuinely takes this much time to process. — iSpain17, May 21 '19 at 19:32

Anirudha Gupta · Answer 1 · 2019-06-10T13:22:25.410

0

If you run the generated SQL in MySQL workbench, you can see the time it take to execute. You can add some index after analyzing the query in workbench.

Just find the column which can be good option for indexed column. You can sort out this issue by adding some index for your query.

edited Jun 10 '19 at 13:22

answered May 21 '19 at 17:09

Anirudha Gupta

9,073
9
54
79

score 0 · Answer 2 · answered May 21 '19 at 17:33

Based on your information I looked up some solutions for your challenge.

You can try to change the way your are joining the tables (INNER JOIN, LEFT JOIN, ...). Look here.
Do you really need to make a .ToList()? Meaning do you really need a evaluated set of results? Try .AsEnumerable() or .AsQueryable() described in the link above.
If you are using EF, you can try to turn off Object Tracking. This checks for changing in the result set. And this takes time.
You may also try to use sub-querys (don't join the whole table with another whole table. Just make two selects, name them as X1 and X2 and join them via their ID.

Please forgive me, that I'm not thaaat familiar with EF since I used it the last time 4 years ago.

score 0 · Answer 3 · answered May 21 '19 at 17:35

First of all, as the other dear members said, you should check if you have indexes on the columns and then, try the following code:

IQueryable<Appointment> Appointments;

        // Does not affect your slowness issue.
        Appointments = condition1 ? _context.Appointment.Where(somecondition) : _context.Appointment.Where(othercondition);

        AppointmentsData = Appointments
                           .Join(_context.Patient,
                                 appPatientKey => appPatientKey.Id, // The primary key of Appointments.
                                 patientKey => patientKey.Id,         // The primary key of Patient.
                                 (appPatientKey, patientKey) => new {
                                     Appointments = appPatientKey,
                                     Patient = patientKey
                                 })
                            .Join(_context.Doctor,
                                  appPatientKey => appPatientKey.IdDoctor, // Assuming that the IdDoctor is coming from Appointments
                                  doctorKey => doctorKey.Id,
                                  (appPatientKey, doctorKey) => new {
                                      appPatientKey.Appointments,
                                      appPatientKey.Patient,
                                      Doctor = doctorKey
                                  })
                            ... // other Joins
                            .GroupBy(result => { AppointmentId = result.Appointments.id, PatientFullName = result.Patient.Fullname, DoctorFullName = result.Doctor.FullName...})
                            .Select(theEntity => new Models.AppointmentData()
                            {
                                Id = AppointmentId,
                                Patient = PatientFullName,
                                Doctor = DoctorFullName

                            }).ToList();

Linq query over mysql table takes too long

3 Answers3