CODEWITHSUNDEEP
phplaraveleloquentsql-updatelaravel-7
Beusebiu
Beusebiu

Reputation: 1523

Improve performance on updating big table, Laravel

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.

Upvotes: 2

Views: 533

Answers (2)

zeref
zeref

Reputation: 166

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

Upvotes: 0

OMR
OMR

Reputation: 12218

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

Upvotes: 2

Related Questions