Magento 2 Database Performance Tips using Laragento DB2 module

MAgento 2 uses outdated d zend framework 1 Query SQL library. It doesn’t have any modern features and best practices. We create Laragento to use the modern Larave Eloquent database ORM with magento 2.

GitHub - Genaker/laragento: Laravel/Eloquent ORM for Magento that allows you to get data directly…

There are some performance tips on how to improve magento 2 performance.

1. Using Selective Columns

Selecting specific columns from a table is a common task when querying data from a database. In Laragento, you can choose which columns you want to retrieve. For example, to retrieve only the name and email columns from the Product model, you could use the following code:

$products = Product::select('name', 'sku')->get();

Eager Loading Relationships with Reducing N+1 Query Problem

Eager Loading is an advanced technique in Laragento that allows you to retrieve related data in a single query, reducing the number of database calls and improving query performance. With eager loading, you can retrieve all the Magento DB data you need with just one query, instead of making multiple queries for related data. For example, a Product has many reviews. Without rushing to load, retrieving Products and related reviews requires two separate queries. However, by using eager loading, you can retrieve magento products and their reviews in a single query, thereby improving performance and reducing overhead.

The N+1 query problem occurs when a query fetches related data for each result. Solve it by using methods like with(), has(), and whereHas() to efficiently load relationships.

Here’s an example :

// Without eager loading
$product = Product::find(1);
$revews = $product->revews;

// With eager loading with Reducing N+1 Query Problem
$product = Product::with('reviews')->find(1);
$revews = $product->revews;

With Laragento eager loading with Reducing N+1 Query Problem can also be done by taking any selected column

// Reducing N+1 Query Problem with selected column
$products = Product::with('review:id,text,date')->find(1);
$reviews = $products->reviews;

Most Magento developers are aware of this issue, but it still crops up. The remedy?

Here’s another illustration:

// Inefficient approach:
$prodcuts = Products::all();
foreach ($prodcuts as $prodcuts) {
    echo $prodcuts->review->text;
}

// Efficient approach:
$products = Products::with('review')->get();

The N+1 problem is common in the magento extensions and magento core code.

Write Optimised RAW Magento SQL queries using Laravel QueryBuilder.

If you’re looking for performance and are okay with writing slightly more code, Laravel’s Query Builder can be a better choice.

Reimagining the previous example with Query Builder:

$transactions = Transaction::select('transactions.*')
    ->join('categories', 'transactions.category_id', '=', 'categories.id')
    ->where('categories.project_id', 1)
    ->get();

This method directly joins the tables, providing a faster query.

Raw SQL eliminates the overheads. However, you miss out on Laravel’s features like Collections, Accessors, and more.

There is two ways to add Laragento:

Laragento GitHub repo and Mage2 faced module :

GitHub - Genaker/mage2: Module to make Magento 2 great again. Old well-known Magneto 1 PHP Facade…