CODEWITHSUNDEEP
phpmysql
Sandokan
Sandokan

Reputation: 849

Too many SQL calls on page load?

I'm constructing a website for a small collection of parents at a private daycare centre. One of the desired functions of the site is to have a calendar where you can pick what days you can be responsible for the cleaning of the locales. Now, I have made a working calendar. I found a simple script online that I modified abit to fit our purpose. Technically, it works well, but I'm starting to wonder if I really should alter the way it extracts information from the databse.

The calendar is presented monthly, and drawn as a table using a for-loop. That means that said for-loop is run 28-31 times each time the page is loaded depending on the month. To present who is responsible for cleaning each day, I have added a call to a MySQL database where each member's cleaning day is stored. The pseudo code looks like this, simplified:

Draw table month
    for day=start_of_month to day=end_ofmonth
        type day
        select member from cleaning_schedule where picked_day=day
        type member

This means that each reload of the page does at least 28 SELECT calls to the database and to me it seems both inefficient and that one might be susceptible to a DDOS-attack. Is there a more efficient way of getting the same result? There are much more complex booking calendars out there, how do they handle it?

Upvotes: 3

Views: 307

Answers (5)

Tim Hurrell
Tim Hurrell

Reputation: 51

Not that 28 calls is a big deal but I would use a join and call in the entire month's data in one hit. You can then iterate through the MySQL Query result as if it was an array.

Upvotes: 2

Luc
Luc

Reputation: 985

28 queries isnt a massive issue and pretty common for most commercial websites but is recommend just grabbing your monthly data by each month on one hit. Then just loop through the records day by day.

Upvotes: 1

Hendrik Brummermann
Hendrik Brummermann

Reputation: 8312

You can use greater and smaller in SQL. So instead of doing one select per day, you can write one select for the entire month:

SELECT day, member FROM cleaning_schedule 
WHERE day >= :first_day_of_month AND day >= :last_day_of_month
ORDER BY day;

Then you need to pay attention in your program to handle multiple members per day. Although the program logic will be a bit more complex, the program will be faster: The interprocess or even network based communication is a lot slower than the additional logic.

Depending on the data structure, the following statement might be possible and more convenient:

SELECT day, group_concat(member) FROM cleaning_schedule 
WHERE day >= :first_day_of_month AND day >= :last_day_of_month
GROUP BY day
ORDER BY day;

Upvotes: 1

Eugen Rieck
Eugen Rieck

Reputation: 65284

The MySQL query cache will save your bacon.

Short version: If you repeat the same SQL query often, it will end up being served without table access as long as the underlying tables have not changed. So: The first call for a month will be ca. 35 SQL Queries, which is a lot but not too much. The second load of the same page will give back the results blazing fast from the cache.

My experience says, that this tends to be much faster than creating fancy join queries, even if that would be possible.

Upvotes: 2

Colin M
Colin M

Reputation: 13348

SELECT picked_day, member FROM cleaning_schedule WHERE picked_day BETWEEN '2012-05-01' AND '2012-05-31' ORDER BY picked_day ASC

You can loop through the results of that query, each row will have a date and a person from the range you picked, in order of ascending dates.

Upvotes: 5

Related Questions