Error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 31360520 bytes)

Good day guys! this from the previous developer codes and I want to optimize this one because it's have error in fetching data. Anyone has suggestions to optimize this code?

 public function render()
    {
        $dateFrom = $this->dateFrom;
        $dateTo = $this->dateTo;

        $serviceInvoiceQuery = DB::table('transaction_summaries AS ts')
        ->select(
            'ts.id',
            'ts.wv_invoice_no',
            'ts.sb_invoice_no',
            'ts.all_total_debits',
            'ts.all_total_credits',
            'ts.transaction_status_id',
            'jo.jo_no',
            'cp.name',
            'transac_stat.name AS transac_stat_name',
            'transac_stat.id AS transac_stat_id',
            'st.status AS status_name',
            'st.id AS status_id',
        )
            ->leftJoin('job_orders AS jo', 'ts.jo_no', '=', 'jo.id')
            ->leftJoin('client_profiles AS cp', 'jo.customer_id', '=', 'cp.id')
            ->leftJoin('transaction_statuses AS transac_stat', 'ts.transaction_status_id', '=', 'transac_stat.id')
            ->leftJoin('statuses AS st', 'ts.status_id', '=', 'st.id')
            ->where('ts.transaction_type_id', '=', EnumsServiceInvoice::SERVICE_INVOICE)
            ->orderBy('ts.created_at', 'DESC');

        if (!empty($this->search)) {
            $serviceInvoiceQuery->where(function ($query) use ($dateFrom, $dateTo) {
                if (!is_null($dateFrom) && !is_null($dateTo)) {
                    $dateFrom = Carbon::parse($this->dateFrom);
                    $dateTo = Carbon::parse($this->dateTo)->addHour(23)->addMinute(59)->addSecond(59);
                    $query->where('ts.created_at', '>=', $dateFrom)
                        ->where('ts.created_at', '<=', $dateTo);
                }

                $query->where(function ($query) {
                    $query->where('ts.wv_invoice_no', 'LIKE', '%' . $this->search . '%')
                    ->orWhere('ts.sb_invoice_no', 'LIKE', '%' . $this->search . '%')
                    ->orWhere('jo.jo_no', 'LIKE', '%' . $this->search . '%')
                    ->orWhere('cp.name', 'LIKE', '%' . $this->search . '%');
                });
            });
        } else {
            if (!is_null($dateFrom) && !is_null($dateTo)) {
                $dateFrom = Carbon::parse($this->dateFrom);
                $dateTo = Carbon::parse($this->dateTo)->addHour(23)->addMinute(59)->addSecond(59);
                $serviceInvoiceQuery->where('ts.created_at', '>=', $dateFrom)
                    ->where('ts.created_at', '<=', $dateTo);
            }
        }

        $serviceInvoice = collect();

        $serviceInvoiceQuery->chunk(100, function ($results) use (&$serviceInvoice) {
            $serviceInvoice = $serviceInvoice->merge($results);
        });

        $serviceInvoice = $serviceInvoiceQuery->paginate(10);

        return view('livewire.billing.service-invoice-table', [
            'serviceInvoice' => $serviceInvoice,
        ]);
    }

The setup is that our client was using wamp for there server since it was just offline and there are only 4 users using the app.

and I already tried this in my php.ini

memory_limit = 4096M but the problem still occures

LaryAI

3 years ago

Level 58

The error you are receiving is due to the fact that the code is trying to allocate more memory than is available. The solution is to optimize the code to reduce the amount of memory it is trying to allocate.

One way to do this is to reduce the number of columns you are selecting in the query. For example, you could remove the transac_stat_name and status_name columns from the query, as they are not necessary for the query to run.

You could also reduce the number of records you are retrieving by adding a limit clause to the query. This will limit the number of records that are returned, which will reduce the amount of memory needed to store the results.

$serviceInvoiceQuery-&gt;limit(100);

Finally, you could also reduce the number of records you are retrieving by adding a where clause to the query. This will limit the number of records that match the criteria, which will reduce the amount of memory needed to store the results.

$serviceInvoiceQuery-&gt;where('ts.transaction_type_id', '=', EnumsServiceInvoice::SERVICE_INVOICE);

JussiMannisto

3 years ago

Level 50

Memory limit

It looks like your PHP memory limit change is not taking effect. If you're running php-fpm, you'll need to restart it to reload the settings. But I REALLY don't recommend using a global 4 GB limit. That setting is per-script, so your server will run out of memory if many memory-intensive requests come in at the same time. You should fix your memory usage in the code instead.

Query bug

You're running out of memory because you're retrieving the entire result set to memory. Then you're immediately discarding the results.

You're retrieving the data in chunks and merging the results to the $serviceInvoice collection. But then you're running paginate() on the original query and overwriting the $serviceInvoice collection you just created. Therefore, this part does nothing (except consume all of your available memory):

$serviceInvoice = collect();

$serviceInvoiceQuery->chunk(100, function ($results) use (&$serviceInvoice) {
	$serviceInvoice = $serviceInvoice->merge($results);
});

All you need to do is run the paginate() command. It retrieves the results for you.

SQL

When you're using LIKE conditions with the wildcard character (%) at the start, your column indexes cannot be used. This is a performance killer. If at all possible, I'd use full text indexes instead.

Laravel 9 added framework level support for full text searches: https://laravel.com/docs/9.x/releases#full-text. You can also do it with raw SQL.

Please or to participate in this conversation.