the following returns the error in the title, i know a solution is to extend the maximum execution time, but i'm asking how can i make it less than 60seconds, you might notice i removed all queries from the loop so they don't run more than once, do the ->where() queries also take long? and if so is there another way to do this to optimise time
Route::get('/test', function () {
$locations = DB::table('tbl_testlocations')->get();
$dateCurr = date("Y-m-d");
$dateBack = '1970-10-04';
$BUs = DB::table("tbl_businessunit")->get();
$data = DB::table("tbldata")
->leftJoin('tbl_testlocation_links', 'tbldata.Test_Request_Number', '=', 'tbl_testlocation_links.Test_Request_number')
->whereBetween('Date_Test_Completed', [$dateBack, $dateCurr]);
$data1 = DB::table("tbldata")
->leftJoin('tbl_testlocation_links', 'tbldata.Test_Request_Number', '=', 'tbl_testlocation_links.Test_Request_number')->whereBetween('Date_Test_Completed', [$dateBack, $dateCurr]);
$data4 = DB::table("timecard_day_hour")
->leftJoin('tbldata', 'tbldata.Test_Request_Number', '=', 'timecard_day_hour.tracker_description')
->leftJoin('tbl_testlocation_links', 'tbldata.Test_Request_Number', '=', 'tbl_testlocation_links.Test_Request_number')
->whereBetween('Date_Test_Completed', [$dateBack, $dateCurr]);
$data2 = DB::table("timecard_day_hour")
->leftJoin('tbldata', 'tbldata.Test_Request_Number', '=', 'timecard_day_hour.tracker_description')
->leftJoin('tbl_testlocation_links', 'tbldata.Test_Request_Number', '=', 'tbl_testlocation_links.Test_Request_number')
->whereBetween('Date_Test_Completed', [$dateBack, $dateCurr]);
foreach ($locations as $key => $location) {
$SumPHS = $data->where($location->TestLocation, 1)
->where('type', 1 || 2)
->where('cancelled', 0)
->get()->sum('Personnel_Hours_Spent');
$Sums = $data2->where($location->TestLocation, 1)
->where('type', 1 || 2)
->where('cancelled', 0)
->get();
$SumRH = $Sums->sum('rh');
$SumOH = $Sums->sum('oh');
$SumDH = $Sums->sum('dh');
$TWSUM = $SumPHS + $SumRH + $SumOH + $SumDH;
$location->totalHours = $SumPHS + $SumRH + $SumOH + $SumDH;
$ReqCount = $data->count();
$location->reqCount = $data->count();
foreach ($BUs as $key => $BU) {
$data3 = $data1
->where($location->TestLocation, 1)
->where('BU_Index', $BU->BU_index)
->where('type', 1 || 2)
->where('cancelled', 0)
->get();
$ReqCount_ = $data3->count();
$BU->reqCount = $data3->count();
$SumPHS_ = $data3->sum('Personnel_Hours_Spent');
$data5 = $data4->where($location->TestLocation, 1)
->where('BU_Index', $BU->BU_index)
->where('type', 1 || 2)
->where('cancelled', 0)
->get();
$SumRH_ = $data5->sum('rh');
$SumOH_ = $data5->sum('oh');
$SumDH_ = $data5->sum('dh');
$cnthours = $SumPHS_ + $SumRH_ + $SumOH_ + $SumDH_;
$BU->cntHours = $SumPHS_ + $SumRH_ + $SumOH_ + $SumDH_;
}
$location->BUs = $BUs;
}
return $locations;
});
Any help appreciated, thank you!