Detailed explanation on using Lazy Collections to improve the performance of Laravel Excel reading (easily supports millions of data)

A new type of collection has been added in Laravel 6: Lazy Collections. They are great if you need to process very large data sets (thousands or millions of rows) without running into memory constraints.

Recommended: laravel tutorial

My most recent task was to refactor Excel export in a project at work. The problem is that the export can no longer be created because the dataset is too large for Laravel to handle. The database query returned approximately 300,000 results! The application times out or consistently runs out of memory.

A naive approach is to increase the timeout or memory limit and hope that the next time something goes wrong, someone else will handle it. But that's not how I work. I don't like Band-Aids. I like concrete, long-term solutions.

Laravel Excel extension pack is already quite flexible. It does a great job of reducing database load by using "chunks" when using FromQuery-concerns. However, our export still struggles with large data sets.

My colleagues and I discussed whether we should completely rewrite this feature: push the export to a queue and send a notification to the user when the export is complete. However, this feature is just a minor thing in this app. It doesn't make sense to us to add so much overhead just for a simple export.

Later that day, I had a little "Eureka" moment because I remembered that Laravel had LazyCollections.

I rewrote the export: it now uses FromCollection-concern instead of FromQuery. The only change I had to make to the collection() method was to replace the get() method at the end of the query builder chain with a cursor().

Below is a simplified version of our export functionality. The Request object is passed through the constructor so we can adapt the query based on what the user selects in the UI.

<?php 
namespace App\Exports;
use App\User;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromCollection;
use Illuminate\Http\Request;
class UsersExport implements FromCollection
{
    use Exportable;
    protected Request $request;
    public function __construct(Request $request)
    {
        $this->request = $request;
    }
    public function collection()
    {
        return User::query()
            ->when($this->request->get(&#39;include_subscribed&#39;), function ($q) {
                return $q->where(&#39;is_subscribed&#39;, true);
            })
            ->cursor(); // ← 重要的一点
    }
}

I believe you are experiencing memory issues in your project. You increased the memory limit and hopefully that solved the problem (I've done this countless times myself).

If it were in a Laravel project, I hope, I could have you revisit that code and rewrite it using LazyCollections.

Fixing this issue was very interesting, so I did a little benchmark: our export can now easily export millions of rows without hitting memory limits. so cool!

The above is the detailed content of Detailed explanation on using Lazy Collections to improve the performance of Laravel Excel reading (easily supports millions of data). For more information, please follow other related articles on the PHP Chinese website!