Optimize Laravel's foreach loop with 2k and improve relational retrieval from 200k to reduce execution time

Question

I have a model called "Product" I have a model called Note_voucher_line

This is the relationship within the product

public function get_note_voucher_lines()
{
    return $this->hasMany('App\Models\Note_voucher_line','product_id','id')->orderBy('date','asc')->orderBy('note_voucher_id','asc');
}

Now sometimes I have to loop the product of code like this

$products = Product::whereBetween('id',[$num1,$num2])->get();
foreach($products as $product)
{
    $lines = $product['get_note_voucher_lines'];
    // when i use this relation it tack long long time
}

Model Note_voucher_line has over 300k lines I have index on migration This is note_voucher_lines internal index migration

Schema::table('note_voucher_lines', function($table) {
    $table->foreign('note_voucher_id')->references('id')->on('note_vouchers');
    $table->foreign('user_id')->references('id')->on('users');
    $table->foreign('journal_entry_id')->references('id')->on('journal_entries');
    $table->foreign('warehouse_id')->references('id')->on('warehouses');
    $table->foreign('product_id')->references('id')->on('products');
    $table->foreign('cost_center_id')->references('id')->on('cost_centers');
    $table->foreign('unit_id')->references('id')->on('units');
    $table->foreign('is_it_bonus')->references('id')->on('status');
    $table->foreign('note_voucher_type_id')->references('id')->on('note_voucher_types');
    $table->foreign('posting_state_id')->references('id')->on('posting_status');
    $table->foreign('product_total_quantity_id')->references('id')->on('product_total_quantitys');
    $table->foreign('is_componentable')->references('id')->on('status');
    $table->foreign('approved_state_id')->references('id')->on('approval_status');
    $table->foreign('currency_id')->references('id')->on('currencies');
    $table->foreign('branch_id')->references('id')->on('branches');
    $table->foreign('created_by')->references('id')->on('users');
    $table->foreign('deleted_by')->references('id')->on('users');
});

There is an index named product_id in the product table Any help here to make it faster Thanks

Answer 1

In this case, eagerly loading your relationship can be helpful.

$products = Product::whereBetween('id',[$num1,$num2])->with('get_note_voucher_lines')->get();
foreach($products as $product)
{
    $lines = $product->get_note_voucher_lines;
    // This should be faster and note that this is the right way to fetch laravel relation not as an array
}

What's happening here is that we are preloading 2k rows from the note_voucher_lines table on each iteration using the with() method (assuming your products table contains 2k rows), instead of the previously loaded row. This reduces the number of network calls made to the database server, now instead of 300k calls it makes 300k/2k calls.

Note: You should also consider using block loading for your product to avoid hitting memory limits as your data continues to grow. https://laravel.com/docs/10.x/eloquent#chunking-results

Answer 2

The main reason for the delay seems to be the lazy loading of the get_note_voucher_lines relationship.

Every time you access this relationship within a loop, Laravel will make a separate query to get the related rows. This is called the N 1 problem.

To mitigate this, use eager loading:

$products = Product::whereBetween('id',[$num1,$num2])->with('get_note_voucher_lines')->get();

You can also use chunking to process big data:

Product::whereBetween('id',[$num1,$num2])->with('get_note_voucher_lines')->chunk(100, function ($products) {
foreach ($products as $product) {

     $lines = $product->get_note_voucher_lines;
}});

Make sure there is an index on the id field. You mentioned having an index, but make sure it's a proper index and not just a foreign key constraint.