jringeisen's avatar

Laravel Datatables : 138 Rows and its taking 5 seconds to load, why?

Hey guys, I have been working on this project for the last 6 hours and I cannot figure out for the life of me why it's taking forever for this datatable to load. The more rows I add the longer it takes. I had 300 rows and it took 20 seconds to load. I am using server side and I am also using the yajra/laravel-datatables package. Any help would be greatly appreciated.

View :

<script src="{!! asset('js/plugins/dataTables/datatables.min.js') !!}"></script>
<script>
$(function() {
    $('#example')
        .on( 'processing.dt', function ( e, settings, processing ) {
            $('#processingIndicator').css( 'display', processing ? 'block' : 'none' );
        } )
        .DataTable( {
            responsive: true,
            processing: true,
            serverSide: true,
            ajax: '{{ route("getClients") }}',
            deferRender: true,
            columns: [
                { data: 'clients_name',
                    render: function(data, type, full, meta) { 
                        return '<a data-toggle="tab" href="#'+full.id+'">'+data+'</a>'; 
                    } 
                },
                { data: 'clients_email' },
                { data: 'clients_phone' },
                { data: 'clients_address' },
            ],
            dom: '<"html5buttons"B>lTfgitp',
            buttons: [
                {extend: 'copy'},
                {extend: 'csv'},
                {extend: 'excel', title: 'Client Database'},
                {extend: 'pdf', title: 'Client Database'},
            ],
    } );
});

Controller :

public function getClients()
{
    return Datatables::of(Client::select(['id','clients_name','clients_email','clients_phone','clients_address'])->where('user_id', Auth::user()->id))->make(true);
}

Route :

Route::get('/getclients', 'Users\Clients\ClientController@getClients')->name('getClients');
0 likes
12 replies
shakti's avatar

Can you please add ``` before writting code here so that we can check your code

priestd09's avatar

I had the same problem. My solution was to have the data cached in redis from the query. I can load 1000s of rows in less than a sec.

This use case works for me.

On development side, when there is a change in data, I clear the cache and pull new one for timing purposes.

Also, add paging: true in your config

Hope this helps

jringeisen's avatar

@priestd09 I found the problem. Not sure why it's doing this but I found the problem lol. I added the debug bar to my laravel project and the debug bar is showing 423 view being rendered and 150 queries being executed.

Apparently I need to find a more effective way to run my foreach loops. When I remove the foreach loop on this page everything works great.

jringeisen's avatar

So I have my datatable rows linked to tabs, so when you click a row to the right of the datatable it displays a tab. Well, I found if I have 138 records in my datatable it loads 138 tabs, this is where the delay is. I have to find a way to only load the tab of the selected row and this is proving to be difficult.

Snapey's avatar

Or you eager-load all the tab data in the controller?

jringeisen's avatar

@Snapey looking into eager-loading now. I haven't used this yet, so I'll take a look and see what I come up with.

jringeisen's avatar

@Snapey good news is I was able to get my views from 423 to 6 and my queries from 150 to 4 using eager-loading. The bad news is I still have a delay due to all the tabs loading.

jringeisen's avatar

I figured out a solution to my problem. The way the page is setup is the datatables load a list of clients, when you click on a client it loads a tab. What was happening when the datatable was loaded it also created the same amount of tabs, so for instance 138 rows meant 138 tabs which is what was causing the page to load super slow.

My solution

<a href="{!! url("clients/'+full.id+'") !!}">'+data+'</a>

When you click the href it gets the client and pass a client variable to the view which is then passed to the tab. So instead of loading 138 tabs it only loads the tab of the clicked client. The only downfall to this solution is the page is refreshed each time you click a clients name. I suppose I could do this via ajax to make it more user friendly.

priestd09's avatar

Eager loading totally slipped my mind! @jringeisen It's one of those things that normally gets added with all my queries when needed.

I'm glad you've found a solution to your problem.

jringeisen's avatar

@priestd09 I have gone through all my queries and added it where needed, so that helped a lot. Thanks for the help everyone!

Please or to participate in this conversation.