Question 243721 in Laravel Question posted in
The official Laravel Documentation can be found here.

laravel foreach loop with 2k and get relation from 200k tack long long time

i have this model called Product
and i have model called Note_voucher_line

and this is relation inside product

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

now sometimes i have to loop products like this code

$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
}

the model Note_voucher_line has over than 300k rows
and i have index on the migration
and this is the index migration inside note_voucher_lines

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 index called product_id belong to products table
any help here to make it faster
thanks

Tags:

2

Answers


  1. 0

    Eager-loading your relationship can be helpful in this case.

    $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 happens here is that we used the with() method to preload 2k rows from the note_voucher_lines table (assuming your products table contains 2k rows) in each iteration as opposed to one row that gets loaded previously. This reduces the number of network calls made to the database server so that instead of making 300k calls, it now makes 300k/2k calls.

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

    Login or Signup to reply.
  2. 0

    If you don’t need to process all 300k+ rows of note_voucher_lines at once, consider implementing pagination in your code. This way, you can fetch and process a limited number of records at a time, which can greatly improve performance.

    $perPage = 50; // Adjust the number of records per page as needed
$products = Product::whereBetween('id', [$num1, $num2])->get();

foreach ($products as $product) {
    $lines = $product->get_note_voucher_lines()->paginate($perPage);
    
    // Process the lines for this product
}

    Ensure that your database server is properly tuned for performance. This includes setting appropriate memory and buffer sizes, optimizing queries, and regularly maintaining your database (e.g., cleaning up unused data, rebuilding indexes).

    If the data doesn’t change frequently, consider implementing caching mechanisms to store the results of expensive queries temporarily. This can significantly reduce the load on your database.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search