This content originally appeared on DEV Community and was authored by Aduramimo Oludare
Writing backend laravel logic means having to wrestle with database manipulation problems from time to time, and if you have ever been in a situation where you have very large records to insert or update (say 15,000), especially when writing cron jobs that crawl applications for very large information and tries to send back reports for business intelligence purposes, it might shock you to note that these operations may take anywhere between 10 minutes to 1 hour if care is not taken to optimize such code, depending on the various timeout settings on your server.
Writing a database insert for each record in an array with the following code might seem okay if done on a local environment or even on a server with few records to worry about:
foreach ($rows as $record) {
DB::table('my_table')->insert([
'clientid' => $record['id'],
'full_name' =>$record['full_name']),
'status' => $record['status']),
'created' => now(),
]);
}
This is not efficient however, and might result in unpleasant scenarios such as memory leaks, abrupt SQL locks and so on, when such logic is employed for large datasets.
Some try to overcome this challenge by writing special queued jobs to do this in the background, however there is a better way to get around this issue by simple array manipulations as outlined below:
- Assuming you have an array of 25000 records stored in a variable
$arrayData. - Define another empty array
$insertion_data = array(); - Loop through the
$arrayDatawhile assigning your database values against each key in$arrayDataand push each item into the$insertion_dataarray :
$insertion_data = array();
foreach ($arrayData as $arr){
$new_data = [
'data_requested_at' => date("Y-m-d H:i:s",
strtotime($arr['data_requested_at'])),
'response_code' => $arr['response_code'],
'status' => $arr['status'],
'created_at' => date('Y-m-d H:i:s'),
'updated_at' => date('Y-m-d H:i:s')
];
$insertion_data[] = $new_data;
}
4.Grab your final insertion data to a Laravel collection
$insertion_data = collect($insertion_data);
5.Leverage the Eloquent chunk method to have a pool of records you want to insert/update per time. You may start with 500 and then increase it according to requirements. Now loop through this new Collection and perform your database insertion. Don't forget to implement the try-catch technique to investigate database errors.
$data_to_insert = $insertion_data->chunk(500);
foreach ($data_to_insert as $key => $data)
{
try{
DB::table('my_target_table')->insert($data
->toArray());
}
catch(\Illuminate\Database\QueryException $e){
$error = $e->getMessage();
echo $error;
}
}
This content originally appeared on DEV Community and was authored by Aduramimo Oludare
Aduramimo Oludare | Sciencx (2023-05-19T17:55:22+00:00) How to perform very large Insert/Update operations in Laravel efficiently. Retrieved from https://www.scien.cx/2023/05/19/how-to-perform-very-large-insert-update-operations-in-laravel-efficiently/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.