Refactoring #3: Optimizing Eloquent queries & console commands

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. 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.

Optimizing Eloquent queries & console commands

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.

Refactor into smaller methods

Use cursor() instead of get()

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 instead.

Use batches instead of individual inserts

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.

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

The code used for illustration is taken from the OpenLitterMap 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