Improve a slow query

This is my API query.

It is super duper slow. (18380 ms and 10MB memory according to telescope. By the way, anyone has a clue how to make the debugbar work on API's as telescope is too slow for me)

I know there are probably many things that can be improved, but I would like to hear from more experienced devs about what can be done.

Appreciate every response, especially the critical ones (I want to improve:)

    Route::get('accounts', function () {
        $accounts = AccountMaster::paginate(10);
        return AccountResource::collection($accounts->load('address', 'telephone', 'titles', 'categories'));
    });

And this is the API resource for it

   public function toArray(Request $request): array
    {
        $locations = [];
        foreach ($this->address as $address) {
            $location = $address->locations?->location_name;
            $locations[$location]['location'] = $location;
            $locations[$location]['fullAddress'] = $address->house_no . ' ' . ($address->streets?->street_name . ', ') .($address->citys ? $address->citys->city_name  . ', ' : '') . ($address->states ? $address->states->state_code  . ' ' : '') . ($address->zipcode ? $address->zipcode->zipcode : '');
            $locations[$location]['addressFirst'] = $address->house_no . ' ' . ($address->streets?->street_name) . ($address->apt_no ? ' #' . $address->apt_no . ', ': ', ');
            $locations[$location]['addressLast'] = ($address->citys ? $address->citys->city_name  . ', ' : '') . ($address->states ? $address->states->state_code  . ' ' : '') . ($address->zipcode ? $address->zipcode->zipcode : '');
        }
        foreach ($this->telephone as $telephone) {
            $location = $telephone->locations?->location_name;
            $locations[$location]['telephone'] = $telephone->fullPhone;
        }
        $locations = array_values($locations);

        $locationsString = '';
        foreach ($locations as $location) {
            $locationsString .= $location['location'] . "\n";
            $locationsString .= $location['addressFirst'] . ($location['addressLast'] ?? null)  . "\n";
            $locationsString .= ($location['telephone'] ?? null) . "\n";
        }

        return [
            'id' => $this->account_id,
            'businessName' => $this->business_name ? $this->business_name : '',
            'title' => $this->titles?->title_name ? $this->titles?->title_name . ' ' : '',
            'firstName' => $this->first_name . ' ',
            'lastName' => Str::title($this->last_name),
            'benReb' => $this->father_name,
            'maidenName' => $this->maidan_name,
            'locations' => $locations,
            'business' => $this->account_type === 2 ? true : false,
            'businessCategories' => $this->categories?->pluck('business_category_name'),
            'images' => $this->images,
            'share' => $this->name . "\n" . ($this->account_type !== 2 ? "Ben Reb: {$this->father_name}\nMaiden Name: {$this->maidan_name}\n" : '') . $locationsString,
        ];
    }

LaryAI

2 years ago

Level 58

The slow query can be improved by optimizing the database queries and reducing the number of database calls. Here are a few suggestions:

Eager Load Relationships: Instead of loading the relationships one by one, you can eager load them using the with method. This will reduce the number of database queries.

$accounts = AccountMaster::with('address', 'telephone', 'titles', 'categories')->paginate(10);

Use Select Statements: Instead of loading all the columns from the database, you can use the select method to fetch only the required columns. This will reduce the memory usage.

$accounts = AccountMaster::with('address', 'telephone', 'titles', 'categories')
    ->select('account_id', 'business_name', 'first_name', 'last_name', 'father_name', 'maidan_name', 'account_type')
    ->paginate(10);

Reduce Database Calls in the Resource: Instead of querying the database multiple times in the toArray method of the resource, you can eager load the required relationships in the controller and pass them to the resource.

Controller:

$accounts = AccountMaster::with('address.locations', 'telephone.locations', 'titles', 'categories')->paginate(10);
return AccountResource::collection($accounts);

Resource:

public function toArray(Request $request): array
{
    $locations = [];
    foreach ($this->address as $address) {
        // ...
    }
    foreach ($this->telephone as $telephone) {
        // ...
    }
    // ...
}

By eager loading the relationships in the controller, you can avoid additional database queries in the resource.

These optimizations should help improve the performance of the query. However, it's important to analyze the database indexes and query execution plan to identify any further optimizations that may be required.

Dave Wize

2 years ago

Level 18

@LaryAI I'm already doing an eager load while requesting the resource.

Anyway, I didn't see any improvement by doing the with on the eloquent call (I have a reason I'm doing it later, I plan to switch eventually to a scout search instead of a regular eloquent builder, and scout doesn't allaw eager loading directly).

Snapey

2 years ago

Level 122

copy your query into a regular webpage then you can run debugbar

Alternatively have a look at Ray

Snapey

2 years ago

Level 122

look out for things like this

$address->citys->city_name

and

$address->zipcode->zipcode

definitely n+1 issues

PovilasKorop

2 years ago

Level 11

You mentioned just the seconds and megabytes from telescope, but have you looked at the actual QUERIES there? That telescope page should have all the answers for you

Dave Wize

2 years ago

Best Answer

Level 18

The main issue was that it missed indexes, as this is an old database (not built with migrations). I added some, and the search time was drastically reduced

Thanks for all of you for the try

Please or to participate in this conversation.