Improve performance on updating big table, Laravel

Question

I have a function that should run once every week (cron job), and now I try to do a stress test.

In my request I get:

Maximum execution time of 60 seconds exceeded

protected function updateAnswerHistory(){
    $answer_statistics = AnswerStatistic::select('question_id','paragraph_id','lkp_answer_id')->get(); //about 500row

    $history = AnswerHistory::select('id', 'question_id','paragraph_id','lkp_answer_id','correct_answer_score')->get(); //about 40k rows

    foreach ($history as $row) {

        if($row->question_id){
            $lkp_answer_id = $answer_statistics->where('question_id', $row->question_id)->pluck('lkp_answer_id')->first();
            if($row->lkp_answer_id === $lkp_answer_id){
                $row->update(['correct_answer_score' => 7]);
            }else{
                $row->update(['correct_answer_score' => 4]);
            }
        }

        if($row->paragraph_id){
            $lkp_answer_id = $answer_statistics->where('paragraph_id', $row->paragraph_id)->pluck('lkp_answer_id')->first();
            if($row->lkp_answer_id === $lkp_answer_id){
                $row->update(['correct_answer_score' => 7]);
            }else{
                $row->update(['correct_answer_score' => 4]);
            }
        }
    }
}

One bad thing is that query from foreach which takes time, but I am not sure how can I improve this.

Answer 1

i' m not sure i understand you db tables structure correctly,

but getting data from db and update them has it's expensive cost

you should make the updating process in db by any way ...

this code idea is to join the two table based on question_id column then made the 'wheres' then update, i didn't got the chance to test it ...

AnswerHistory::join('answer_statistics','answer_statistics.question_id','answer_histories.question_id')-> where('answer_histories.question_id','!=',null)->
    where('answer_histories.lkp_answer_id','=',DB::raw('answer_statistics.lkp_answer_id'))
        ->update(['correct_answer_score' => 3]);

AnswerHistory::join('answer_statistics','answer_statistics.question_id','answer_histories.question_id')-> where('answer_histories.question_id','!=',null)->
    where('answer_histories.lkp_answer_id','!=',DB::raw('answer_statistics.lkp_answer_id'))
        ->update(['correct_answer_score' => 0]);

please let me know if it helps