Maximum execution time of 60 seconds exceeded (need help reducing run time)

168 views Asked by At

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!

0

There are 0 answers