加快创建未来记录

Question

I have a Codeigniter system where users can enter an appointment and have it automatically repeat on a weekly basis. The system automatically creates appointments a month ahead of the current date if the original appointment was set to repeat.

Here is the module function. It works, but boy is it slow. Would really appreciate suggestions; is there a way to do this in a couple of passes using SQL?

The problem is the nested loops, when the system has hundreds of repeat appointments to check for it takes about 20 seconds to run.

The 'repeats' field is set to 1 for an appointment that repeats, or 2 for a 'child' appointment created from it.

    function update_repeat_appointments($pupil_ID=NULL) // adds appointments 1 month ahead for all repeat appointments
{
    if ($pupil_ID != NULL) {$this->db->where('timetable.pupil_ID', $pupil_ID);}

    $this->db->where('repeats', 1);
    $this->db->where('date >=', date('Y-m-d', strtotime("-1 month", strtotime("now"))));
    $this->db->order_by("date", "asc"); 
    $query = $this->db->get('timetable');
    $repeatapps = $query->result();

    $enddate = strtotime("+1 month", strtotime("now")); // Change this line to have repeat appointments go further into the future

    //Loop over current repeat appointments
    foreach ($repeatapps as $row) {
        $startdate = strtotime($row->date);
        $runningdate = $startdate;

        $this->db->where('pupil_id', $row->pupil_id);
        $this->db->where('repeats <>', 1);
        $this->db->where('date >=', date('Y-m-d', strtotime("-1 month", strtotime("now"))));
        $this->db->order_by("date", "asc"); 
        $query = $this->db->get('timetable');
        $subapps = $query->result();

        while ($runningdate <= $enddate) {
            // Check if there is an appointment in a future week for this pupil
            $runningdate = strtotime("+1 week", $runningdate);
            $found=false;
            foreach ($subapps as $subrow) {
               if (strtotime($subrow->date) == $runningdate) { //Matched appointment found, exit loop
                  $found=true;
                  break; 
               }
            }
            if ($found=false) {
                //Add an appointment with relevant data, including future date
                $data = array ( "staff_id" => $row->staff_id,
                                "pupil_id" => $row->pupil_id,
                                "venue_id" => $row->venue_id,
                                "group_id" => $row->group_id,
                                "notes" => $row->notes,
                                "date" => date('Y-m-d h:i:s', $runningdate),
                                "repeats" => 2, // is a sub_repeat
                                "root_ID" => $row->ID // Record ID of root appointment, allows bulk changes to it's repeats
                                );
                $this->add_appointment($data);                      
            }
        }       
    }  

Answer 1

This seems less of a "this particular script is slow" problem than it is a "this particular script doesn't scale because it's doing too much at once" problem.

I would assume from looking at this that it's a cron script. It has to recalculate the information, whether or not anything changed. The more repeating events you have, the longer this script takes.

I would ask some questions like this:

1. Can I populate a month in advance RIGHT WHEN the appointment is marked as recurring? (Load is distributed to the individual users requesting the action)
2. Can I append a new event every time an old one passes, thereby keeping a running list of a month's worth, but not having to run expensive calculations every time? (Load is probably centralized in a cron script here, unless you have an "event" triggered by the related user--i.e. marked as attended/completed/etc.)

In this scenario, here's the cron logic:

1. Check for recurring appointments that have passed since the last time the cron script ran (some sort of flag for "processed")
2. For every appointment record that passed, add one to the end of the queue over the recurrence interval, and flag the "expired" record as "processed"

Having said all that, a 20-second cron script isn't nearly as terrible as a 20-second page request. When you distribute the load, always lean in favor of user experience.