query performance within a foreach loop

Posted on

Question :

In the app I build users will be able to book appointmnets…on (a specific) page load I must display all appts-past and future-a query does that…each row holds the details of an appointment.
Each appt though has also some services and/or staff associated with it…1 or 2 queries will bring this info.

So I must loop for all the appts and run 1/2 queries each time-for every app.
So for 20 apts that is 20/40 SELECT queries

From which number of apts onwards you think I will face a perfomace issue?
I do not think 20 or 30 appts will create a problem.
I know that to give an answer on this info must be given but I do not know which exact info is needed…i can provide though…

Possible workourounds(for which i want your feedback)

1.Display only a specific number of appts on page load(for example 10)…when the user scrolls to the 10th an ajax request is made to bring another 10…maybe the ajax request could be triggered on some ‘more’ button click or jusn triggered by the scroll event per ce…loop through the appts will be done again but only for these 10 appts
I think this is a virtual scroll scenario..but i do not know this is proper solution for this case.

What do you think?Any other possible workarounds.
I am just trying to figure out the alogorithm for it.
Probably it is sth trivial for today’s web standards…for me though sth new.

thx…

P.S we must also consider the UX factor also for a case as the above

here is the fiddle with the info you requested…
fiddle

at line 131 you will find the query for getting the appointmnets from the DB

at line 145 is the query to get the services associated w the appt.
I did not write the query for the staff cause it is identical to the one with the services.

So..what do you think?

Answer :

Try not to explicitly loop. Instead, try to write a JOIN that fetches the appt(s) in the same query. That is likely to be much faster.

SELECT p.*, a.*
    FROM Patients AS p
    JOIN Appts AS a  ON a.patient_id = p.id
    WHERE patient_id = ...
    ORDER BY a.appt_date DESC;

That will give you one row of data for each appointment for the one patient.

Index

To speed up the query at line 131 add this to appointments:

INDEX(apps_origin, delete_front, bookfromID, bookedfor)

I am going to combine the staff query and services query in one query and add indexes..than I am going to bring only 20-30 appt at a time..I think this must be OK.. at the end of of the 20th or 30th appt I will display a “more” text..upon clicked by the user an ajax request will bring another 20-30 and so on…

Leave a Reply

Your email address will not be published. Required fields are marked *