Getting 28,000 timeSlots From Save Table To Main Table

PHP
1

Hi
All, I have a table with a weeks worth of time slots saved which include day, time and availability. This table is used
as a template for the main calendar system so you can load a weeks saved time slots into your calendar for any particular week of the year.

The table holds approx 28,000 records for a given week depending on the number of hours and days they work.

Codes taking ages to load, tried putting data into an array and pulling out but still slow.
Any ideas on how to speed this up ?

if($loadSlots==“YES”)
{
$loadStartTimeStamp = strtotime( “$day-$month-$year”);
$loadFinishTime = $loadStartTimeStamp + 6048000;

$query = "SELECT staffID, timeSlotID, timeInterval FROM tblServicesTimeSlotDetails WHERE clientID='".$_SESSION['clientID']."'";
			
		$result = mysql_query($query,$db);
		$row = mysql_fetch_array($result);
					
		$interval = $row[timeInterval];
		$staffID = $row[staffID];
		$timeSlotID = $row[timeSlotID];
						
					
			
					for($i=$loadStartTimeStamp; $i<=$loadFinishTime; $i+=60)
								{
								$day = gmdate("D", $i);
								$time = gmdate("G:i", $i);
						
$query2 = "SELECT status FROM tblServicesSavedTimeSlots WHERE clientID='".$_SESSION['clientID']."' AND staffID='$staffID' AND
day='$day' AND time='$time'";

$result2 = mysql_query($query2,$db);
$row2 = mysql_fetch_array($result2);
$status = $row2[status];
								
$query3 = "INSERT INTO tblServicesTimeSlots SET timeSlotID='$timeSlotID', clientID='".$_SESSION['clientID']."', staffID='$staffID',
slotTime='$i', slotStatus='$status', LIVE='NO'";
								
								mysql_query($query3,$db);
								}
					
					
						}
2

So you are doing aproximately 100000 select and insert queries?

To speed it up, you should find a way to get rid of the queries in the loop. Can’t you rewrite those queries to something like


"INSERT INTO  tblServicesTimeSlots
 SELECT
     tblServicesTimeSlotDetails.timeSlotID
   , tblServicesTimeSlotDetails.clientID
   , tblServicesTimeSlotDetails.staffID
   , [B][COLOR="#FF0000"]>> do something with tblServicesSavedTimeSlots.day and tblServicesSavedTimeSlots.time here to get the slotTime in the format you want <<[/COLOR][/B]
   , tblServicesSavedTimeSlots.status
   , 'NO'
 FROM  tblServicesTimeSlotDetails
 INNER JOIN tblServicesSavedTimeSlots
 ON  tblServicesTimeSlotDetails.clientID = tblServicesSavedTimeSlots.clientID
 AND tblServicesTimeSlotDetails.staffID = tblServicesSavedTimeSlots.staffID
 WHERE tblServicesTimeSlotDetails.clientID='".$_SESSION['clientID']."'
";
3

Hi
Thanks for idea, don’t really see how I could use it, unless I’m not following you.

How could I get around the loop as its needed to make the time stamp values for seven days.
The time stamp gets the day and time for that time stamp and checks it against the saved template to see if the time is available by splitting out the day and time only.
It then dumps the time stamp and status into the main table.