CODEWITHSUNDEEP
mysqlsqlperformancelaravel
Moshe Harush
Moshe Harush

Reputation: 701

Optimize Multiple database update

I am using laravel in my app. So now I need to update many rows on DB once a day with Cron job.

If I will use the update option built-in laravel I need to do something like this:

// for example, the update info in array
$arrayToUpadte = [
    [
        'numberId' => '0500000000',
        'usagePer' => '4.5'
    ],
    [
        'numberId' => '0500000001',
        'usagePer' => '5.6'
    ],
    [
        'numberId' => '0500000002',
        'usagePer' => '8.1'
    ],
    [
        'numberId' => '0500000003',
        'usagePer' => '0.3'
    ]   
];


// update will do one query to db every update!!
foreach( $arrayToUpadte as $updates ){

    \App\Phonelines::where( 'numberId', '=', $updates['numberId'] )->update([
        'usagePer' => $updates['usagePer']
    ]);

}

So my question is how I can Improve code to have as few queries as possible?

If I have this in a regular PHP project I would do it like this:

$newValues = [];
foreach( $arrayToUpadte as $updates ){
    $newValues[] = $updates['numberId'] . ',' . $updates['usagePer'];
}

$newValues = implode( '),(', $newValues );

$mysqli -> query( "
    INSERT INTO `phonelines` ( `numberId`, `usagePer` ) VALUES (" . $newValues . ")
    ON DUPLICATE KEY
    UPDATE `usagePer` = VALUES( `usagePer` );
" );

Is there a way to do this in Laravel's Framework?

Upvotes: 2

Views: 110

Answers (1)

Mortada Jafar
Mortada Jafar

Reputation: 3679

you can use this :

  DB::raw("INSERT INTO `phonelines` ( `numberId`, `usagePer` ) 
              VALUES (" . $newValues . ") ON DUPLICATE KEY
             UPDATE `usagePer` = VALUES( `usagePer` )") ;

Upvotes: 1

Related Questions