Laravel Pipeline Facade - To slow

Level 24

Hey there! The pipeline facade itself isn’t the bottleneck here; it’s really nothing more than a series of closures. The cause is more likely what you’re doing in those individual pipes. Without seeing the pipes, it’s difficult to say where the issue is. If the pipes are doing nothing more than altering a database query, it could just be that you need to start thinking about adding appropriate indexes to your database table.

1 like

Level 1

@LukeDowning19 thanks for the answer

The pipes have no impact. They just do simple things. If I remove them, the time will be the same

I also created another Model, but with less information. I thought maybe the Model was too complex with lots of data, and that was having impact. But I was wrong. Still no luck with performance

Even If I remove the pipelines and just do something like:

return DB::select( DB::raw('Select id from diaries') );

It will take much time.

Maybe you're right, I need to start to think about indexes. I am already reading about that.

I thought it could be something related to the pipelines

Level 27

@SilvaDreamdeal Assuming that id is the primary key in your diaries table (for example, generated by using $table->id() in a migration), it is already indexed. Selecting a single column like that, especially one that’s indexed and of an integer type (unless you’re using UUIDs, of course) should never take long unless you’re fetching an awful lot of them.

For example, I just did a select id from [table] on a table with about 50,000 rows in a remote production database server (SQL Server), and that query took about 240 ms. And I hope for your application’s sake that you’re not fetching 50,000 rows in any of your queries – because if you do, that will always be a bottleneck that will slow things down, no matter what you do.

Level 1

@kokoshneta The problem is that we are talking about 2.000 raws.

Is it to much to process?

I'm using 36 char ID's as primary key. Could this be the problem?

Level 27

@SilvaDreamdeal A few thousand rows shouldn’t be too much of a problem.

But using a 36-character ID is not going to help. Why do you do this? If you need something that isn’t just an integer, you’d be better off using a UUID instead – databases (many, at least) have native support for these and store them internally as bits, rather than strings, making selection, comparison and filtering much faster.

Also, why do you need to select 2,000 IDs in a single query?

Level 1

@kokoshneta first of all, thank you very much for trying to help

My table as almost 2.000 raws of information. The pipeline above maybe is processing all the 2.000 raws and returns 10 with "paginate()" right? maybe I could do something with this.

In my production server everything is ok. It is a good server, but it could gave me problems in the future My problem is localy or in my Development server, its weaker thatn the production

I usually don't use primary keys with this format. However, the project was developed before my arrival at the company and the database was already structured that way.

Do you think that changing the primary key format to a lighter one would help? I think that even with 2,000 records the application should be faster

Level 27

@SilvaDreamdeal Without seeing your actual pipes, there’s no way to say for sure, but based on the code you’ve provided here, it doesn’t look like anything is processing every row in the database, no. There’s no reason it should be. If you’re just implementing a searching system, all the filtering and processing is done in the database and only the matching rows are returned.

If there are only about 2,000 records in the table, a simple query to fetch rows should be quite fast, unless it’s insanely complex.

I don’t think the problem here is the fact that you’re using pipelines or that your table (or even your ID column type) is too heavy, especially if everything is fast enough on the deployment server.

Is your development machine connecting to the production database on the remote server? The latency on that would be a more likely culprit, especially if you have too many database queries per page load. For example, connecting to a remote SQL Server running on a Windows Server has proven to be finicky for me in the past, with remote connections taking more than 20 times as long as local connections, and simple view pages taking in excess of 20 seconds to load.

Level 1

@kokoshneta even If I remove the pipes, the time is to much

->through([
                
            ])

I assume that the pipes are not the problem. Because even without them it takes too much time.

Database is local, server is local. Everything is local

In the development server everything is inside the dev server, the same in production

There are no external connections. Even running locally I created a copy of the database and I'm accessing to localhost:3306

Level 122

Install laravel debugbar to check the DB queries and the execution time.

You could for instance, have some database thrashing view composer which prepares data for another part of the page.

https://github.com/barryvdh/laravel-debugbar

Level 1

@Snapey Never used debugbar but I will really try it. Is there some tutorial that you know that is helpfull or can I just follow the github?

Level 122

@SilvaDreamdeal You don't really need a tutorial. Its very simple to install and use

Level 1

@Snapey Maybe its a silly question

Laravel is my backend, its an api.

I have a Vuejs application making the calls

How can I add the bar to my frontend (vuejs app) ? is it possible? Because if not, how can I use this tool?

Level 27

@SilvaDreamdeal I don’t think that works, no – you should call backend URIs directly in the browser instead to see how fast the backend itself responds.

Level 1

Maybe its not the queries that are giving problems

In my Search.php action I've made:

<?php

namespace App\Domains\Works\Actions;

use App\Domains\Works\Support\Search\Search as SearchPipeline;
use Lorisleiva\Actions\Action;

class Search extends Action
{
    public function authorize()
    {
        return auth()->user()->hasPermission(["super", "works.read"]);
    }

    public function rules()
    {
        return [
            "code"            => ["nullable"],
            "name"            => ["nullable"],
            "status"          => ["nullable"],
            "commissioner_id" => ["nullable", "uuid"],
            "overseer_id"     => ["nullable", "uuid"],
            "engineer_id"     => ["nullable", "uuid"],
            'limit'           => ['nullable', 'min:1', 'max:25'],
            'sortBy'          => ["nullable"],
            'sortDesc'        => ['nullable']
        ];
    }

    public function handle()
    {
        return [];
    }
}

The request took 1 minute, just to give me an empty array...

What could be the problem?

Routes.php

<?php

namespace App\Domains\Works\Support;

class Routes
{
    public static function routes()
    {
        \Route::group(['prefix' => 'admin/works-settings/works', 'middleware' => ["auth:sanctum"], 'namespace' => 'App\Domains\Works\Actions'], function () {

            \Route::name("admin.works.status.search")->get("/status", "Status\Search");

            \Route::name("admin.works.create")->post("/", "Create");

            \Route::name("admin.works.search")->get("/", "Search");

            \Route::name("admin.works.list")->get("/list", "ByUser");

            \Route::name("admin.works.get")->get("{id}", "Get");

            \Route::name("admin.works.update")->put("{id}", "Update");

            \Route::name("admin.works.equipments.export")->post("{id}/equipments/export", "Equipments\Export");

            \Route::name("admin.works.equipments.export.download")->get("{file}/equipments/download", "Equipments\Download");

            \Route::name("admin.works.delete")->delete("{id}", "Delete");
        });
    }
}

Level 122

Could be the authorise function. Try commenting it.

https://prnt.sc/pVfNCkq6nICP

Level 1

@Snapey Still the same.

removed the authorize and the rules. And still very slow

Level 1

@snapey and @kokoshneta

I think I discovered the problem. Its my model... If I do something like:

public function toArray(){
return parent::toArray();
}

The time: 3 seconds

But how and why is it using the model? I just have the routes as the following:

<?php

namespace App\Domains\Works\Support;

class Routes
{
    public static function routes()
    {
        \Route::group(['prefix' => 'admin/works-settings/works', 'middleware' => ["auth:sanctum"], 'namespace' => 'App\Domains\Works\Actions'], function () {

            \Route::name("admin.works.status.search")->get("/status", "Status\Search");

            \Route::name("admin.works.create")->post("/", "Create");

            \Route::name("admin.works.search")->get("/", "Search");

            \Route::name("admin.works.list")->get("/list", "ByUser");

            \Route::name("admin.works.get")->get("{id}", "Get");

            \Route::name("admin.works.update")->put("{id}", "Update");

            \Route::name("admin.works.equipments.export")->post("{id}/equipments/export", "Equipments\Export");

            \Route::name("admin.works.equipments.export.download")->get("{file}/equipments/download", "Equipments\Download");

            \Route::name("admin.works.delete")->delete("{id}", "Delete");
        });
    }
}

And my Search is:

<?php

namespace App\Domains\Works\Actions;

use Lorisleiva\Actions\Action;

class Search extends Action
{
    public function authorize()
    {
        return true;
    }

    public function rules()
    {
        return [
        ];
    }

    public function handle()
    {
        return [];
    }
}

And yes, I am sure that my request is requesting the route to the Search.php

I've already made an echo, just to be sure, and it goes inside that Search.php

But how and why is it using model?

Level 122

@SilvaDreamdeal 3 seconds is still horrible. your responses should be 200ms or less

Level 122

Should you be using the asController method rather than handle() ?

Best Answer

Level 27

@SilvaDreamdeal What version of Laravel is your app using? The syntax you’re using for the get() method calls is an old one that hasn’t been supported since Laravel 7, so I assume it’s an older installation.

What do you get if you simply open the search endpoint directly in a browser, without involving your frontend involved at all? Just open a browser tab and go straight to yourdomain.test/ (or whatever your localhost address is). If you do that, Debugbar should be able to tell you roughly what’s taking a long time – if indeed that still takes a long time, if it’s the database queries or something else.

Level 1

@kokoshneta accessing the server directly with the browser is instantaneous. the results came right away

Level 27

@SilvaDreamdeal Then that tells you that it’s not Laravel or the database itself – if the results are in the sub-300 ms range (give or take, depending on the complexity of the route and the number of queries), then all is well there.

The issue must then be related to the frontend instead, the bottleneck being somewhere between Vue and Laravel. Perhaps a misconfiguration in how Vue is accessing the backend or how the server is processing the API calls to the backend? I’ve never used Vue, so I can’t help much there, unfortunately.

Level 1

@Snapey as controller is giving error

@kokoshneta so you think the problem could be related to frontend -> backend or backend->frontend connection? I'm still not able to see the debugbar

https://prnt.sc/9UpqtpeIRwFt

Level 1

@kokoshneta

installed the debugbar

composer require barryvdh/laravel-debugbar --dev

and added to my app.php:

Am I missing some step?

Level 27

@SilvaDreamdeal Have you made sure APP_DEBUG is set to true in your .env file? It should just work out of the box, then (though I’ve only used it in Laravel 8+ before).

You could also try Clockwork, which is a brilliant tool and very easy to install.

Max100

Level 6

FWIW, if the data access is faster on your production server than on your local, maybe you have some type of logging enabled on your local sql server. That could slow down your database access. I'd try turning off all db logging except for errors to see if that improves the speed.

Level 1

@Max100 I think maybe its not related with queries

In my comment above you can see that and, if you have some idea I would be very grateful

Max100

Level 6

@SilvaDreamdeal As stated above, 3 seconds is still bad. If the same code runs fine on the production server, it suggests something may not be properly configured on your local server. Even assuming the production server is more powerful than your local server, the difference still should not be so great.

I doubt db logging (or Laravel logging) would account for such a significant slowdown, but it does affect performance.

The Laravel-debugbar let's you examine database access, which will provide a better picture of what's happening.

Level 1

@Max100 but the debugbar is not appearing. Missing some step? (comment above)

Followed: https://github.com/barryvdh/laravel-debugbar

I've accessed via browser directly to the api and got the results in the same second So, maybe is really something between frontend and API

Level 1

@max100 , @kokoshneta, @snapey

First of all, thanks for the help. it was exhausting to find the problem. But, I think I got it. Thanks to you all.

After making sure the backend was fine, I went to the Vuejs application. I commented my requests and tested them all 1 by 1. I noticed that one of them takes a long time.

So I went back to trying to run that same call directly on the backend. It always gives me a timeout error.

tomorrow I'll try to find the error, because I think I might be, unintentionally, doing some loop. My server was taking so long doing that Search because its stuck waiting for the other request to end

Any solution or problem, I'll put it back here

Level 27

@SilvaDreamdeal It would make sense for the load time to always be a minute, then – 60 seconds is the default timeout period for many things in PHP.

Level 1

@kokoshneta

for example. This is some code of my Diaries. There are the Diary Model (code bellow is from this model) and the User Model. When I do a listing of diaries, its slow, very slow

But when I comment the User model and do a \DB::table('users') the time is reduced in almost 100% Its incredible the difference. Its almost in the same second

private function getUserCreated(){
        return \DB::table('users')->select('id', 'name', 'email')->where('id', $this->user_id)->first();//User::where('id', $this->user_id)->first();
    }

    private function getUserEdited(){
        return \DB::table('users')->select('id', 'name', 'email')->where('id', $this->user_id)->first();//User::where('id', $this->edited_by)->first();
    }

    private function getUser(){
        return \DB::table('users')->select('id', 'name', 'email')->where('id', $this->user_id)->first();//User::where('id', $this->user_id)->first();
    }

ToArray function:

public function toArray()
    {
        $machines = $this->getMachines();

        $workers = $this->getWorkers();

        $workUnits = $this->getWorkUnits();

        $workerWorkUnits = $this->getWorkerWorkUnits();

        $machineWorkUnits = $this->getMachinerWorkUnits();

        $this->addMachinesWorkedHours($machines);

        $this->addWorkersWorkedHours($workers);

        return [
            "id"              => $this->id,
            "code"            => $this->code,
            "date"            => $this->date,
            "status"          => $this->status,
            "machines"        => $machines->toArray(),
            "workers"         => $workers->toArray(),
            "work_units"      => $workUnits->toArray(),
            "worker_work_units" => $workerWorkUnits->toArray(),
            "machine_work_units"=> $machineWorkUnits->toArray(),
            "files"           => $this->files()->get(),
            "work_id"         => $this->work_id,
            "work_object"     => $this->getWorkObject(),
            "description"     => $this->description,
            "approvals"       => $this->approvals,
            "user_created"    => $this->getUser(),
            "editions_qty"    => $this->editions_qty,
            "edited_by"       => $this->edited_by,
            "updated_at"      => $this->updated_at,
            "created_at"      => $this->created_at,
            "user_edited"     => $this->getUserEdited(),
            "readed"          => $this->hasBeenReaded(),
            'user_created'    => $this->getUserCreated(),
            'rejected'        => $this->rejected,
            'rejectedHistory' => $this->getRejections(),
            'approval_history'=> $this->getApprovalHistory()
        ];
    }

My user model is not very complex. It's a simple model that gets the fields from the database, and gets an array of the roles, permissions and jobs they have associated with them. It still makes a huge difference

but if we analyze it in depth, the daily rate uses the user model. The user model uses a works model. And the works use a daily model, to know the daily rates that are created for a given work. This generates a big cycle here. And we ended up making calls to the daily rate table several times.

Is it better to have one model to use for the listings where I have the basic information that I need or is there a way to use the main one?

Level 27

@SilvaDreamdeal Are the works and daily models relationships on the user? I’m having trouble figuring out exactly how your different data structures actually fit together, or what you’re really trying to do in the big picture. It doesn’t help that we can’t see where the functions you’ve quoted here actually reside. I get the feeling that you’re writing some quite un-Laravel-like code which is causing a lot of redundancy, but it’s hard to pinpoint what’s going on exactly.

The highest level appears to be diaries (as in the things that people use to jot down what they did and what happened every day?). Presumably each diary or diary entry is related to a user, the person who writes it(?), but what do the works, daily and rate models? And how is all this stored – separate tables for each model?

It would help if you could show:

all the model classes that are involved (diaries, users, works, daily(?), rates…)
the database table structure associated with each model
the actual controller function that is called when you hit the endpoint (the one you posted above just does return [], which makes no sense)

https://prnt.sc/8WTO7V2hS2HW

Level 1

@kokoshneta

We have works. The work is a general construction work where you put prices, descriptions of the work, etc

We have Diary. Each day one user needs to create a diary where he describes whats happening in the work. So, the Diary belongs to one work and one user The diary also needs to have the user that is editing the diary. The creator will always be the same. The editor will change and save the last editor ID.

We have users, where we have the registered user and fields like name, email, etc

but I confirm that there was some kind of redundancy here

I started to work on it, to stop using models everywhere and to put things in a lighter way, and the application has big differences

By avoiding using Models everywhere, in certain situations I can improve the performance from 35 seconds to 1 second for example.

A good example is when I'm going to list the Diaries. I have a filter where you can choose the user who created it, so you can only see diaries from that user. For that I needed to do a getAll Users from the database to populate my select box. I'm doing it without returning the User:: model.

Instead I changed it to \DB::table('users')->get()

speed went from 35s loading model to 1 second. We have hundreds of users, and in each model we calculate performances to get the performance of a user. That was being calculated when I do the getAll Users. But theres no need to do that

Level 27

@SilvaDreamdeal Using models shouldn’t make much of a difference there, though. Models are generally quite small and lightweight. I have a page that has over 3,000 models, and it loads in less than 500 ms.

If using models makes it take that much longer, then it’s because you’re doing something wrong in one or more of your models, making it run some very heavy loops or N+1 queries or something like that. Perhaps some events or something like that.

If I understand your screenshot correctly, your database tables have these foreign keys:

works table: commissioner and engineer (both → users.id)
diaries table: work_id, created_by (→ users.id), updated_by (→ users.id)
users table: none
role_user table: user_id, role_id
permission_role table: permission_id, role_id

Is that correct?

Let’s say you have 20 works and 200 users; with one diary per work every day, that’s about 600 diaries in a month. That’s not a lot.

Let’s say we want all the diaries from the past month (about 600 records). You’d fetch that like this:

$diaries = Diary::query()
	->with(['work.commissioner', 'work.engineer', 'createdBy', 'updatedBy'])
	->whereDate('created_at', '>', now()->subMonth())
	->get()
;

That will run six database queries and set all the relationships on each diary model. This should take no more than 2–300 ms.

1 like

Level 122

@SilvaDreamdeal what does this function look like;

$this->addWorkersWorkedHours($workers);

has a loop or loops?

1 like

Level 1

@kokoshneta about the relationships you are correct

And in everything you are correct to

seems stupid but I ran into a problem that wasn't helping at all. In my frontend, first of all I was making calls to the server to fill in my filters (database users to be able to search by username or by work name, for example. I was getting all the works name and users name from DB)

All these calls were made in a method called beforeCreate() which in Vue is the first method to be executed (if I'm not mistaken). Only after the page is completely loaded I do the call to the getDiaries() method. The method took a long time because it was only executed at the end of the others being executed.

1 - I placed the API calls correctly (first loading the information and then filling the filters) 2 - Improved the models, removing redundancies. The models were really buggy. It was what was holding everything back

Putting this together I have responses between 0 to 300ms

@kokoshneta, @snapey and @max100 I want to thank you for your patience and help, because without your help I would certainly still be looking for the problem and how to solve it

Level 27

@SilvaDreamdeal Glad to hear you’ve solved it! You can use the “Set Best Answer” link to mark the problem as solved and get the thread off the “Unsolved” list (if you can find a post that best represents what led you to the solution).