0

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!

salah1337
  • 67
  • 1
  • 7
  • 1
    Install (only locally, NEVER in production) [Laravel Debugbar](https://github.com/barryvdh/laravel-debugbar) and see which queries take much longer than others. – KazikM Dec 04 '20 at 16:33
  • Do you have indexes on your DB tables? And if yes - are they matching your queries? Try to add or modify indexes and check execution time. – KazikM Dec 04 '20 at 16:41
  • 2
    You have many queries joining more or less the same tables. You should try to reduce them and simplify things by using [eloquent relationships](https://laravel.com/docs/8.x/eloquent-relationships). For example you're left joining `tbldata` twice with the same where condition. It's probably more efficient to get the data of `tbldata` once and then get the data of `tbl_testlocation_links` using a `where timecard_day_hour.tracker_description IN(array of ids from first table)` to replace the join. – apokryfos Dec 04 '20 at 17:23
  • How much data are you trying to get through? – Jason Dec 04 '20 at 23:29
  • Thanks for your time everyone, @jason, i'm not sure, quite a lot for sure – salah1337 Dec 04 '20 at 23:34
  • @KazikM does it serve the same use as laravel telescope? – salah1337 Dec 04 '20 at 23:35
  • 1
    Something similar, but Debugbar is more mature (7 years of development vs 2 years of Telescope). You can read a comparison [here](https://barryvdh.nl/laravel/debugbar/telescope/2020/06/14/laravel-debugbar-vs-telescope-toolbar/). – KazikM Dec 05 '20 at 15:05

0 Answers0