Improving Magento 2 and Adobe Commerce Performance using Laravel Eloquent on Large Datasets

Laragento is a powerful object-relational mapper (ORM) based on Elouqent that makes it easy to interact with Magento 2 database without legacy low perfomant core magento ORM built on top of Zend Framework 1(Zend_DB). However, when working with large datasets, Magento 2, even with Eloquent, can sometimes be slow.

Magento Eloquent (Laragento) is here :

GitHub - Genaker/laragento: Collection of the Laravel/Eloquent Model classes that allows you to get…

There are a few things you can do to improve Eloquent performance on large datasets:

Use eager loading

Eager loading is a technique that allows you to load all of the related data for a model in one query. This can significantly improve performance, especially when you are working with complex relationships. To use eager loading, you can use the with() method. For example, the following code shows how to eager load the product relationship for a category:

$user = Category::with('products')->get();

This code will load the ‘products’ relationship for the user, along with the attributes etc. This means that you will only need to make one query to the database, even though you are retrieving two different magento tables.

Use caching

Caching is another great way to improve Magento 2 Eloquent performance. Caching allows you to store the results of expensive queries in memory so that you don’t have to re-run them every time they are needed. To use caching in Eloquent, you can use the cache() method. For example, the following code shows how to cache the results of a query to get all users:

$users = Products::cache(60)->get();

This code will cache the results of the query for 60 seconds. This means that if the query is run again within 60 seconds, the results will be returned from the cache, without having to re-run the query.

Use indexes

Indexes can help to improve the performance of Eloquent queries by allowing the database to quickly find the data that it needs. If you are running queries on large datasets, you should make sure that the columns that you are filtering and ordering by are indexed.

You can use indexes in Laravel by adding them to your migrations. Let's say that we expect our store users to search the products by name a lot, we could add an index query to the title of the products:

Schema::table('products', function (Blueprint $table) {
    $table->index('title');
});

By creating this migration, we can speed up our table. Keep in mind, however, this index will only be applied if we search the product using the whole title or the start of the title. In other words, the index will be applied if we run queries like this:

Product::where('title', '=', $search);
Product::where('title', 'like', $search . '%');

But it won't be applied if we try to match by using a like query like this:

Product::where('title', 'like', '%' . $search . '%');

This can be solved using a full-text index, there are other types of indexes which are outside the scope of this article.

Optimize MAgento 2 Eloquent queries

There are a few things you can do to optimize your MAgento 2 Eloquent queries:

• Avoid using select('*'). Instead, select only the columns that you need.
• Avoid using where() clauses with multiple conditions. Instead, use whereHas() or whereDoesntHave() clauses to filter related data.
• Avoid using orderBy() clauses with multiple columns. Instead, use orderByDesc() or orderByAsc() clauses to order by a single column.

Select only the columns you need

One way we can optimize a query is by reducing the amount of data we fetch from the database. When you make a query, the data that the database returns is sent over the network. The more data there is the more time it will take, but that’s not all, all that data has to be stored in memory for the lifetime of the request and that can cause your server to slow down and run out of memory under heavy loads.

Fortunately, in Laragento, we can specify exactly the data we need. Let’s say for example that we have an e-commerce website, and we want to display a list of products. Our controller would look something like this:

Products::query()->paginate()

You can reduce the amount of data by selecting only the columns you need:

Products::query()->select(['id', 'sku'])->paginate()

2. Beware of N+1 issues

Continuing with our Magento example, let’s say that we need to display the brand that each product belongs to. Let’s imagine we have a brand relationship in our Product model:

public function brand()
{
    return $this->belongsTo(Brand::class);
}

If we need to access the brand name in our blade template, it would look something like this:

@foreach ($products as $product)
    //...
    <p>{{ $product->brand->name }}</p>
    //...
@endforeach

Again, this looks fine. I have written code like this countless times in the past. However, there is a small issue here that might not be obvious. While we loaded all of our products into memory using one SQL query in our controller, the brands are retrieved from the database one by one in our forloop. If your relationship isn’t loaded, Laravel will execute an SQL query to retrieve it from the database.

That issue is called “n+1”, it’s called that because we execute 1 SQL query to fetch our products and then N SQL queries to fetch the brands, N being the number of products.

Laravel offers a simple way to fix this issue using eager loading. Eager loading means that we want to retrieve all the related models before using them. Under the hood, Laragento will make only one SQL query to retrieve every related brand. So instead of making N+1 queries, we only make 2.

To use eager loading, you call the with method in your query

public function index()
{
    return view('products', [
        'products' => Products::query()
                        ->select(['id', 'title', 'slug', 'thumbnail'])
                        ->with('brand')
                        ->paginate()
    ]);
}

We can even combine the previous technique and this one by selecting only the columns we need from our relationship, we can do this by adding : followed by the columns we want to select from the relationship.

public function index()
{
    return view('products', [
        'products' => Products::query()
                        ->select(['id', 'title', 'slug', 'thumbnail'])
                        ->with('brand:id,name')
                        ->paginate()
    ]);
}

This way, Laravel will make sure only to select the id and name columns when eager to load the relationship.

Optimizing circular relationships

Let’s say that our product model looks like this,

class Product extends Model
{
    public function category()
    {
        return $this->belongsTo(Category::class);
    }

    public function url()
    {
        return URL::route('product', [
            'category' => $this->category->slug,
            'product' => $this->slug,
        ]);
    }
}

In this model, we have a helper function that uses the related category slug to generate the URL, a simple but useful method in our model. However, let’s assume that we want to show all the products for a given category, our controller would look something like this:

public function show(Category $category)
{
    $category->load('products'); // eager load the products

    return view('categories.show', ['category' => $category]);
}

Now let’s say that we want to show the URL of the product on our view by calling the url method that we defined in the product model:

@foreach($category->products as $product)
    <li>
        <a href="{{ $product->url() }}">{{ $product->name }}</a>
    </li>
@endforeach

The problem here is that we’ve introduced an N+1 issue once again, when we call the url method in the product, we are making a query to fetch the category of each product even though we already have it!. One way to solve this is by eager loading the category in our products. We can do this by adding a .category to the load method call:

public function show(Category $category)
{
    $category->load('products.category'); // eager load the products

    return view('categories.show', ['category' => $category]);
}

Now the N+1 issue is solved, but we are making two SQL queries to fetch the same category, once when the model gets injected into the show method in the controller and the other when we call the load method on that category. Fortunately, there is a way to avoid that by assigning the relationship directly using the setRelation method.

public function show(Category $category)
{
    $category->products->each->setRelation('category', $category);

    return view('categories.show', ['category' => $category]);
}

Selecting large dataset

Using chunk

// when using eloquent
$products = Product::chunk(100, function($products){
    foreach ($products as $product){
     // Process products
    }
});
 
// when using query builder
$products = DB::table('products')->chunk(100, function ($products){
    foreach ($products as $product){
     // Process products
    }
});

The above example retrieves 100 records from the products table, processes them, retrieves another 100 records and processes them. This iteration will continue until all the records are processed.

This approach will make more database queries but highly memory efficient. Usually processing of large datasets will be done in the background. So it is ok to make more queries when running in the background to avoid running out of memory when processing large datasets.

option 2: Using cursor

// when using eloquent
foreach (Product::cursor() as $products){
   // Process a single products
}
 
// when using query builder
foreach (DB::table('Products')->cursor() as $products){
   // Process a single products
}

The above example will make a single database query, retrieve all the records from the table, and hydrate eloquent models one by one. This approach will make only one database query to retrieve all the products. But uses a php generator to optimize the memory usage.

when can you use this?

Though this greatly optimizes the memory usage on the application level, Since we are retrieving all the entries from a table, the memory usage on the database instance will still be higher.

It is better to use a cursor If your web app running your application has less memory and the database instance has more memory. However, if your database instance lacks memory, it is better to stick to chunk.

option 3: Using chunkById

// when using eloquent
$products = Products::chunkById(100, function($products){
    foreach ($products as $product){
     // Process products
    }
});
 
// when using query builder
$products = DB::table('products')->chunkById(100, function ($products){
    foreach ($products as $product){
     // Process products
    }
});

The major difference between chunk and chunkById is that chunk retrieves based on offset and limit. Whereas chunkById retrieves database results based on an id field. This id field usually be an integer field, and in most cases it would be an auto incrementing field.

The queries made by chunk and chunkById were as follows.

chunk

select * from products osts offset 0 limit 100

select * from products offset 101 limit 100

chunkById

select * from products order by id asc limit 100

select * from products where id > 100 order by id asc limit 100

Debug Queries using the Laravel Query Log

Laravel query log that collects all queries within a request. You can enable this log, run your query and dump the output.

DB::enableQueryLog();

App\User::query()
    ->where('created_at', '<', now()->subYear())
    ->with('assignedApps', 'courses')
    ->orderBy('email', 'asc')
    ->limit(5)
    ->get();
dump(DB::getQueryLog());

Listening For Query Events

If you want to receive each SQL query executed by your application, use the listen method. This method is helpful for logging queries or debugging. You may register your query listener in a service provider:

app/Providers/AppServiceProvider.php

namespace App\Providers;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\ServiceProvider;
class AppServiceProvider extends ServiceProvider
{
    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        DB::listen(function ($query) {
           var_dump(
                $query->sql,
                $query->bindings,
                $query->time
            );
        });
    }
    /**
     * Register the service provider.
     *
     * @return void
     */
    public function register()
    {
        //
    }
}