I have a process that retrieves data from a web service and saves it in my database. This data consists of over 20,000 items. In order to save them into the database, I need to gather some information first before storing them. Therefore, I have a foreach loop that runs over 20,000 times, executing a read and write operation for each item.
However, as time passes, this method starts to slow down significantly. It now takes more than an hour to complete!
I attempted to improve performance by disabling the query log (DB::disableQueryLog()), but there was no noticeable enhancement.
This is part of the code I am using:
$data = API::getItems();
foreach ($data as $item) {
$otherItem = OtherItem::where('something', $item['something'])->first();
if (!is_null($otherItem)) {
Item::create([
...
]);
}
}
To address this issue, I decided to preload all OtherItem into a collection, which resolved the slowdown problem:
$data = API::getItems();
$otherItems = OtherItem::all();
foreach ($data as $item) {
$otherItem = otherItems->where('something', $item['something'])->first();
if (!is_null($otherItem)) {
Item::create([
...
]);
}
}
Nevertheless, I am seeking to comprehend why the initial approach deteriorated in performance over time and what is the optimal way to handle such tasks going forward.
EDIT:
In order to clarify: I am aware that performing 20,000 queries is not efficient and, under normal circumstances, performance may not be crucial (unless the task takes hours instead of minutes). Currently, I will only run this routine occasionally while in development. Ultimately, I combined both solutions provided (adding batching and buffering the items). Here is the revised code for reference:
$data = collect(API::getPrices());
$chunks = $data->chunk(500);
$otherItems = OtherItem::all();
foreach ($chunks as $items) {
$buffer = [];
foreach ($items as $item) {
$otherItem = otherItems->where('something', $item['something'])->first();
if (!is_null($otherItem)) {
$buffer[] = [
...
];
}
}
Item::insert($buffer);
}
Thus, I am puzzled regarding the substantial slowness of the process (even with all the queries optimized). To delve deeper into this matter, I have initiated benchmarking. When utilizing the two-query approach, the following results were obtained:
For 6000 iterations:
- Max read: 11.5232 s
- Min read: 0.0044 s
Mean read: 0.3196 s
Max write: 0.9133 s
- Min write: 0.0007 s
- Mean write: 0.0085 s
Every 10-20 iterations, the read time unexpectedly spikes to over a second for 2-3 iterations, without clear reason as to why.
As an additional curiosity, I also performed benchmarking to compare the difference between chunking and buffering the items before inserting them into the database:
- without buffering: 1,115.4 s (18 min 35 s)
- chunking and buffering: 1064.7 s (17 min 45 s)