Laravel-datatables: Ajax Loading Slowly even by removing get()

[megadola]

First of all, thanks for your great package,
before submitting this issue, i searched and i found that I have to remove get() from my eloquent to let the package handle it as well by using script pagination (as you said before on other issues). So, I did that but with Join ... BUT Loading data is still taking too long time to load 2000 records - So i think pagination is not working as well..

any ideas how can i make it load faster ?
Also, Do you think should i create a view to instead of using join ? for better performance ?

Laravel 5.0
Controller:

        $CustAccount = CustAccount::join('customers', 'customers.sysId', '=', 'custAccount.id')
        ->join('debts', 'debts.sysId', '=', 'custAccount.id')
         ->select('custAccount.id as id', 
                    'custAccount.typeId as typeId', 
                    'customers.custcode as custcode', 
                    'customers.name as name', 
                    'debts.installments as installments', 
                    'debts.net_balance as net_balance', 
                    'debts.net_installments as net_installments', 
                    'debts.last_inv_number as last_inv_number', 
                    'debts.suspension_date as suspension_date')
        ->where('custAccount.isActive', '=', 1)
        ->where('custAccount.isDeleted', '=', 0)
        ->where('custAccount.agent_id', '=', $currentUser);
return Datatables::of($CustAccount)->make(true);

JS:

        processing: true,
        serverSide: true,
        stateSave: true,
        paging: true,
        ajax: path,

[yajra]

What DB are you using? You are correct that removing get() should increase the performance since paging is done on the database side.

Also, I think using join is fine as it decreases the db calls unlike when using eager loading for instance. Maybe try indexing you custAccount table to improve the performance? I am also fine with view but you may have to create additional model for it which may not be convenient?

[yajra]

I just recently helped a friend having the same issue and adding table index and optimizing the query fixed the issue. His records is around 500k.

In this regard, I think adding proper index should fixed your issue too. And also consider your server environment resources too. Thanks!

[megadola]

Thanks dear @yajra , sorry about the too late reply, i will do it by indexing it. Thanks

[kjdion84]

Proper indexing and not using ->get() resolved the issue for me. Thank you.