CODEWITHSUNDEEP
phpmysqllaravel-5
Micael Sousa
Micael Sousa

Reputation: 1169

Database overload in long task using Laravel

I'm currently struggling with an issue that is overloading my database which makes all page requests being delayed significantly.

Current scenario

- A certain Artisan Command is scheduled to be ran every 8 minutes
- This command has to update a whole table with more than 30000 rows
- Every row will have a new value, which means 30000 queries will have to be executed
- For about 14 seconds the server doesn't answer due to database overload (I guess)

Here's the handle method of the command handle()

public function handle()
{
    $thingies = /* Insert big query here */

    foreach ($thingies as $thing)
    {
        $resource = Resource::find($thing->id);

        if(!$resource)
        {
           continue;
        }

        $resource->update(['column' => $thing->value]);
    }
}

Is there any other approach to do this without making my page requests being delayed?

Upvotes: 0

Views: 651

Answers (2)

Micael Sousa
Micael Sousa

Reputation: 1169

Thanks to @cyclone comment I was able to update all the values in one single query.

It's not a perfect solution, but the query execution time now takes roughly 8 seconds and only 1 connection is required, which means the page requests are still being handled when the query is being executed.

I'm not marking this question as definitive since there might be improvements to make.

$ids = [];
$caseQuery = '';

foreach ($thingies as $thing)
{
    if(strlen($caseQuery) == 0)
    {
        $caseQuery = '(CASE WHEN id = '. $thing->id . ' THEN \''. $thing->rank .'\' ';
    }
    else
    {
       $caseQuery .= ' WHEN id = '. $thing->id . ' THEN \''. $thing->rank .'\' ';        
    }

    array_push($ids, $thing->id);
}
$caseQuery .= ' END)';

// Execute query
DB::update('UPDATE <table> SET <value> = '. $caseQuery . ' WHERE id IN ('. implode( ',' , $ids) .')');

Upvotes: 0

BeetleJuice
BeetleJuice

Reputation: 40886

Your process is really inefficient and I'm not surprised it takes a long time to complete. To process 30,000 rows, you're making 60,000 queries (half to find out if the id exists, and the other half to update the row). You could be making just 1.

I have no experience with Laravel, so I'll leave it up to you to find out what functions in Laravel can be used to apply my recommendation. I just want to get you to understand the concepts.

MySQL allows you to submit a multi query; One command that executes many queries. It is drastically faster than executing individual queries in a loop. Here is an example that uses MySQLi directly (no 3rd party framework such as Laravel)

//the 30,000 new values and the record IDs they belong to. These values
// MUST be escaped or known to be safe
$values = [
    ['id'=>145, 'fieldName'=>'a'], ['id'=>2, 'fieldName'=>'b']...
];

// %s and %d will be replaced with column value and id to look for
$qry_template = "UPDATE myTable SET fieldName = '%s' WHERE id = %d";

$queries = [];//array of all queries to be run

foreach ($values as $row){ //build and add queries
    $q = sprintf($qry_template,$row['fieldName'],$row['id']);
    array_push($queries,$q);
}

//combine all into one query
$combined = implode("; ",$queries);

//execute all queries at once
$mysqli->multi_query($combined);

I would look into how Laravel does multi queries and start there. The last time I implemented something like this, it took about 7 milliseconds to insert 3,000 rows. So updating 30,000 will definitely not take 14 seconds.

As an added bonus, there is no need to first run a query to figure out whether the ID exists. If it doesn't, nothing will be updated.

Upvotes: 1

Related Questions