Refactoring #3: Optimizing Eloquent queries & console commands
Tags: laravel, refactoring, webdev, eloquent
Published: 2021-12-31 11:57:05

We have a console command at Openlittermap that takes all the litter locations in the world and groups them into clusters for viewing on the [global map](https://openlittermap.com/global). It's a heavy command in terms of memory consumption, database strain, and execution time. That's why it's used sparingly, from time to time. Let's see how we can make it run daily, without sacrificing time and memory.

It's going to be a bit long, so bear with me here. For brevity, we're only showing the `handle` method. Being a lengthy method, it's hard to find a place to start refactoring, so let's go easy and extract some smaller methods out of it. This will help us understand the problem better, and improve the command's readability.
{% gist
https://gist.github.com/GeniJaho/ac70866e06809d585470f0a5f30ddd8c %}

##Refactor into smaller methods
There are two main actions that this command does. Firstly, it generates a JSON file with all the latitude & longitude values of litter, hence, the `generateFeatures` method. Secondly, using this JSON file, a node command (yes, a node command) is executed using the `exec` PHP function, which generates the clusters into a `clusters.json` file. Lastly, we populate our `clusters` database table using the generated JSON. I know, lengthy explanation.
{% gist
https://gist.github.com/GeniJaho/f6beca490e7c239d0d75ebe24bb4db5b
%}
##Use cursor() instead of get()
The memory issue with this line `Photo::select('lat', 'lon')->get();` is that it will load all the photo objects into memory, and that is a big problem with the 250k+ photos currently in the database. To fix that, Laravel provides a nice query helper called [cursor](https://laravel.com/docs/8.x/eloquent#cursors). By using `$photos = Photo::select('lat', 'lon')->cursor();`, only one Eloquent model is kept in memory at any given time while iterating over the cursor. For that reason, we don't even have to call the `unset($photos);` method at all.

From the docs, although the cursor method uses far less memory than a regular query, it will still eventually run out of memory. If you're dealing with a very large number of Eloquent records, consider using [the lazy method](https://laravel.com/docs/8.x/eloquent#streaming-results-lazily) instead.
##Use batches instead of individual inserts
Looking at the loop that iterates over all clusters, it's killing the database with thousands of MySql `insert` queries for storing the clusters. It would be much better if we utilized the `Cluster::insert()` method instead of `Cluster::create()`. This way, we'll only execute one query to insert all the records.
{% gist
https://gist.github.com/GeniJaho/0c095d9d599dd80516a042c9531ab6e0
%}

Now this will of course fail because inserting lots of megabytes at once is a big no-no for the database. Let's see how we can insert the data in chunks.

{% gist
https://gist.github.com/GeniJaho/a60b96531c51b6ee41bb22ccca3ddeec
%}

We're doing a couple of things here. First, we're wrapping the clusters object into a Laravel `Collection` instance, so that the code becomes more readable and we're provided some nice helpers. Using the `filter()` method on the collection, we're extracting the check with `isset()` outside of the mapping (or looping). The call to `map()` on the collection allows us to modify the `$cluster` objects into simple arrays, preparing them for insertion.

Now here's the fun part. The call to `->chunk(1000)` will separate the collection items into multiple chunks of size 1000. The call to `each()` after that iterates all the chunks and inserts them separately. This allows us to easily execute a much smaller number of queries, without sacrificing performance.
##Bonus small improvements
There are three small improvements I'd like to mention. Just to feel... complete.
{% gist
https://gist.github.com/GeniJaho/6d3b6b4e9591924f0db4264929c7ffb1
%}

> The code used for illustration is taken from the [OpenLitterMap](https://github.com/OpenLitterMap/openlittermap-web) project. They're doing a great job creating the world's most advanced open database on litter, brands & plastic pollution. The project is open-sourced and would love your contributions, both as users and developers.

---

Originally published at [https://genijaho.dev](https://genijaho.dev/blog/refactoring-3-optimizing-eloquent-queries-and-console-commands).