Optimizing Laravel 8 for efficient handling of large datasets

Question

I'm currently working with a table containing millions of rows. I need to efficiently process around one million of these rows to retrieve specific data, such as total counts and counts grouped by certain criteria. I'm currently using Laravel's chunk() method to achieve this. However, the endpoint's response time is currently quite slow, and it is taking a lot of time.

$incidents = Incident::where('canceled_by', null)
    ->with(['detail.classification'])
    ->whereIn('period_id', $requestAll['typeValue'])
    ->where($views[$requestAll['view']]['where'], $views[$requestAll['view']]['whereValue']);

$incidents->chunk(100000, function ($chunkIncidents) use (&$generalInformation, $views, $requestAll) {
    // Manipulate data
});

Answer 1

Using the Eloquent way with lots of items can be slow, because every row is mapped to its model. You are also doing this for 2 extra models. It would be quicker to just retrieve the columns needed using the DB facade.

DB::table('incidents')
    ->select('incidents.id', 'details.description')
    ->whereNull('canceled_by')
    ->leftJoin('details', 'incidents.id', '=', 'details.incident_id')

This could also be slow, because the Builder in Laravel uses Collections for the results. Collections are also slow when using lots of data. A direct statement would be the fastest way. You can generate the sql using the Eloquent way and retrieve the sql by calling the toSql() function, but that will be without the binding in Laravel 8.

Incedent::query()->where('column', 'value')->toSql()

Above will result in

select * from `incidents` where `column` = ?