Improve performance on updating big table, Laravel

user_11543786 0
Viewed 232

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.

laravel-7 laravel
2 Answers

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

user_105735600

When retrieving big data set, using DB facade is faster than using Eloquent Model since it will reduce the time of converting db record to eloquent model

And also since bulk update is faster than single row update, You can do it like

$questionList = DB::table('answer_history')
 ->join('answer_statistic')
 ->on('answer_history.question_id', 'answer_statistic.question_id')
 ->on('answer_history.lkp_answer_id', 'answer_statistic.lkp_answer_id')
 ->whereNull('answer_statistic.deleted_at')
 ->pluck('answer_history.id')
 ->toArray();


$paraList = DB::table('answer_history')
 ->join('answer_statistic')
 ->on('answer_history.paragraph_id', 'answer_statistic.paragraph_id')
 ->on('answer_history.lkp_answer_id', 'answer_statistic.lkp_answer_id')
 ->whereNull('answer_statistic.deleted_at')
 ->pluck('answer_history.id')
 ->toArray();

$ids7 = array_merge($questionList,$paraList); 

AnswerHistory::wherein('id', $ids7) >update(['correct_answer_score' => 7]);
AnswerHistory::whereNotin('id',$ids7 )->update(['correct_answer_score' => 4]);

Edited : innerjoin -> join

user_56936010
Related