Perfomance - very slow #2496
-
|
Hi, first of all, thanks for your great work and for helping buiding better webapps. This example with only 4 records, take 4 seconds to render... also if i sort, or search. JS Where, u can see the screenshot... the time for the queries is <400ms and if i sort any columns ou search... datatable take 4 to 5 seconds to display the result.
I also post similar issue/problem here : #2482 (comment) |
Beta Was this translation helpful? Give feedback.
Replies: 16 comments 1 reply
-
|
Hello, maybe it is caused through your I have a server side loading of 5k database rows and my table with 100 entries every page is drawn in under 1 second. And I'm currently at over 1k queries in that time. |
Beta Was this translation helpful? Give feedback.
-
|
Thanks for replying. My bet is something about the way i use the query |
Beta Was this translation helpful? Give feedback.
-
|
Maybe try It is an Eloquent Query but works fine for me. |
Beta Was this translation helpful? Give feedback.
-
|
@pirex360 try specifying the the columns that you are using. I usually encounter this slow response when I include a binary field on the selected fields. DataTables::of(Block::with(['doctype', 'material'])->select('id', 'doctype_id', 'material_id', 'etc...'); |
Beta Was this translation helpful? Give feedback.
-
|
Hi, thanks for your effort, but still the performance is bad. The speed improves a bit in production, but still is a bad user experience. I change the query to. : |
Beta Was this translation helpful? Give feedback.
-
|
Were you able to inspect the sql execution time? That might give you a clue where the issue is. It could also be on the eager loaded relations. On some codes, I also select the columns when eager loading. public function query(Document $model)
{
return $model->newQuery()
->select($model->getTable() . '.*')
->when(request('company_id'), function ($q) {
$q->where('company_id', request('company_id'));
})
->when(request('entity_id'), function ($q) {
$q->where('entity_id', request('entity_id'));
})
->with('company:id,name')
->with('entity:id,name')
->with('creator:id,first_name,last_name')
->with('updater:id,first_name,last_name');
} |
Beta Was this translation helpful? Give feedback.
-
|
Yes, the sql execution time is only 300ms. I will try select columns when eager loading, but they are small tables, only with ID,Name and Timestamps, and max 50 rows. |
Beta Was this translation helpful? Give feedback.
-
|
Do you have some rendering functions in the frontend? |
Beta Was this translation helpful? Give feedback.
-
|
Hi there, i tried a lot of options... but still the results are slow. In the frontend And the Service Logic |
Beta Was this translation helpful? Give feedback.
-
|
Not sure but it seems like a N+1 issue? Try removing these 2 added columns. ->addColumn('_supplier', function($data){
$relation = $this->suppliers->firstWhere('no', $data->supplier_id);
$field = $relation['nome'];
return $field;
})
->addColumn('_client', function($data){
$relation = $this->clients->firstWhere('no', $data->client_id);
$field = $relation['nome'];
return $field;
}) |
Beta Was this translation helpful? Give feedback.
-
|
Hi, i just gained about 120ms in 5s, this colums are collections from other database that are fetched via api data of the clients and suppliers, the collections are global variables of the class. So i just need to fetch in the constructor and then i just compare the id to fetch the name. I really worried about this scenario, because all my project is based on dataTables for UI of data. The problem, is that the time of table sorting, table render, search is very similar with 20 records and 2500 records, i dont think it's a query issue... but some DataTables configuration or optimization that i missed. |
Beta Was this translation helpful? Give feedback.
-
|
Hmm, usual cause of slow performance is due to collection, N+1 and slow query count. Can you try removing all the add & edit columns and observe the performance? The issue might not be on the package but by other factors. Also, do you have laravel-debugbar? You can debug further using that package. |
Beta Was this translation helpful? Give feedback.
-
|
For local i use devilbox in a Mac OSX... , i will try in the production server. Thanks for all the support, i will keep up seaching and update this. |
Beta Was this translation helpful? Give feedback.
-
|
Can you remove the drawCallback function just for a test? I had the same issue as I started with DataTables and on my end it was a callback function from DataTables that caused the issue on long client side rendering. |
Beta Was this translation helpful? Give feedback.
-
|
Ok, i will try without drawcallback! I will report soon. I was in a fight with the eloquent query, thinking that has a problem N+1, but the debugbar show me the queries and the times.
],` |
Beta Was this translation helpful? Give feedback.
-
|
Finally the end of this mini-nightmare. I hope the thread will help others. When i install the debugbar, i notice a large number of "GATE" request, because in the ->addColumn i use 3 checks permissions (spatie) , if user Solution : before datatables init -> check only one time the permissions of the user... and use the same logic for every line, but using a local variable with the roles of the current user, because before : 3x Gate Calls Thanks for all the help and the support. |
Beta Was this translation helpful? Give feedback.
-
|
Converted the issue to discussion and marking this as the answer. This is a N+1 issue due to multiple gate calls (But I think it should be cached by spatie to prevent N+1). Thanks! |
Beta Was this translation helpful? Give feedback.
Finally the end of this mini-nightmare. I hope the thread will help others.
When i install the debugbar, i notice a large number of "GATE" request, because in the ->addColumn i use 3 checks permissions (spatie) , if user
@can('xxxx')for 3 buttons (view, edit, delete) - so every line , 3x Gate Requests where made.Solution : before datatables init -> check only one time the permissions of the user... and use the same logic for every line, but using a local variable with the roles of the current user, because before : 3x Gate Calls
@can('xxxx')where made per line ( i think equals 3 queries extra per line ), in 2500 records... imagine the lag.Thanks for all the help and the support.