Speed up record Load time laravel using Datatable

Question

I am working with laravel project which uses a mysql database. I have 40,000 record in my database, it's take too much time around 20 min to load page. following is the function i use to get data.

plase check my code and help me to solve this issue. Code is here

CustomerController

public function index()
{
    //
    abort_if(Gate::denies('customer_access'), Response::HTTP_FORBIDDEN, '403 Forbidden');

   $customers = Customer::all();

    return view('admin.customers.index', compact('customers'));
}

IndexController

@extends('layouts.admin')
@section('content')
@can('customer_create')
<div style="margin-bottom: 10px;" class="row">
    <div class="col-lg-12">
        <a class="btn btn-success" href="{{ route("admin.customers.create") }}">
            {{ trans('global.add') }} {{ trans('cruds.customer.title_singular') }}
        </a> | <a class="btn btn-success" href="importcustomer">
            Import or Export
        </a> 
    </div>
</div>
@endcan
<div class="card">
<div class="card-header">
    {{ trans('cruds.customer.title_singular') }} {{ trans('global.list') }}
</div>

<div class="card-body">
    <div class="table-responsive">
        <table id="tabls" width="100%"  class=" table table-bordered table-striped table-hover datatable datatable-User">
            <thead>
                <tr>
                
                    
                    <th class="small-col">
                        &nbsp;
                    </th>
                    <th class="small-col">
                        {{ trans('cruds.customer.fields.cid') }}
                    </th>
                    <th class="big-col">
                        {{ trans('cruds.customer.fields.cname') }}
                    </th>
                    <th class="big-col">
                        {{ trans('cruds.customer.fields.address') }}
                    </th>
                    <th class="big-col">
                        {{ trans('cruds.customer.fields.county') }}
                    </th>
                    <th class="big-col">
                        {{ trans('cruds.customer.fields.country') }}
                    </th>
                    <th class="big-col">
                        {{ trans('cruds.customer.fields.pcode') }}
                    </th>
                    <th class="big-col">
                        {{ trans('cruds.customer.fields.phone') }}
                    </th>
                    <th class="big-col">
                        {{ trans('cruds.customer.fields.mobile') }}
                    </th>
                    <th class="big-col">
                        {{ trans('cruds.customer.fields.email') }}
                    </th>
                    <th class="big-col">
                        {{ trans('cruds.customer.fields.web') }}
                    </th>
                    <th class="big-col">
                        {{ trans('cruds.customer.fields.mcat') }}
                    </th>
                    <th class="big-col">
                        {{ trans('cruds.customer.fields.scat') }}
                    </th>
                    <th class="small-col">
                        {{ trans('cruds.customer.fields.business') }}
                    </th>
                    <th class="big-col">
                        {{ trans('cruds.customer.fields.lead') }}
                    </th>
                    <th class="big-col">
                        {{ trans('cruds.customer.fields.rep') }}
                    </th>
                   
                    
                </tr>
            </thead>
            <tbody>
                @foreach($customers as $key => $supplier)
                    <tr data-entry-id="{{ $supplier->id }}">
                        
                        <td>
                            @can('customer_show')
                                <a class="btn btn-xs btn-primary" href="{{ route('admin.customers.show', $supplier->id) }}">
                                    {{ trans('global.view') }}
                                </a>
                            @endcan

                            @can('customer_edit')
                                <a class="btn btn-xs btn-info" href="{{ route('admin.customers.edit', $supplier->id) }}">
                                    {{ trans('global.edit') }}
                                </a>
                            @endcan

                            @can('customer_delete')
                                <form action="{{ route('admin.customers.destroy', $supplier->id) }}" method="POST" onsubmit="return confirm('{{ trans('global.areYouSure') }}');" style="display: inline-block;">
                                    <input type="hidden" name="_method" value="DELETE">
                                    <input type="hidden" name="_token" value="{{ csrf_token() }}">
                                    <input type="submit" class="btn btn-xs btn-danger" value="{{ trans('global.delete') }}">
                                </form>
                            @endcan

                        </td>
                        <td>
                            {{ $supplier->cid ?? 'NA' }}
                        </td>
                        <td>
                            {{ $supplier->cname ?? '' }}
                        </td>
                        <td>
                            {{ $supplier->address ?? '' }}
                        </td>
                        <td>
                            {{ $supplier->county ?? '' }}
                        </td>
                        <td>
                        {{ $supplier->country ?? '' }}
                            
                        </td>
                        <td>
                            {{ $supplier->pcode ?? '' }}
                        </td>
                        <td>
                            {{ $supplier->phone ?? '' }}
                        </td>
                        <td>
                            {{ $supplier->mobile ?? '' }}
                        </td>
                        <td>
                            {{ $supplier->email ?? '' }}
                        </td>
                        <td>
                            {{ $supplier->web ?? '' }}
                        </td>
                        <td>
                            {{ $supplier->mcat ?? '' }}
                        </td>
                        <td>
                            {{ $supplier->scat ?? '' }}
                        </td>
                        <td>
                            {{ $supplier->business ?? '' }}
                        </td>
                        <td>
                            {{ $supplier->lead ?? '' }}
                        </td>
                        <td>
                            {{ $supplier->rep ?? '' }}
                        </td>
                        
                    </tr>
                @endforeach
            </tbody>
        </table>
    </div>
</div>
</div>
<style>
.small-col {
width: 100px !important;
}
.big-col {
width: 200px !important;
}

table#tabls{
table-layout:fixed;
}</style>
@endsection
@section('scripts')
@parent
<script>
$(function () {
let dtButtons = $.extend(true, [], $.fn.dataTable.defaults.buttons)
@can('customer_delete')
let deleteButtonTrans = '{{ trans('global.datatables.delete') }}'
let deleteButton = {
text: deleteButtonTrans,
url: "{{ route('admin.users.massDestroy') }}",
className: 'btn-danger',
action: function (e, dt, node, config) {
  var ids = $.map(dt.rows({ selected: true }).nodes(), function (entry) {
      return $(entry).data('entry-id')
  });

  if (ids.length === 0) {
    alert('{{ trans('global.datatables.zero_selected') }}')

    return
  }

  if (confirm('{{ trans('global.areYouSure') }}')) {
    $.ajax({
      headers: {'x-csrf-token': _token},
      method: 'POST',
      url: config.url,
      data: { ids: ids, _method: 'DELETE' }})
      .done(function () { location.reload() })
  }
  }
  }
  dtButtons.push(deleteButton)
  @endcan

  $.extend(true, $.fn.dataTable.defaults, {
  order: [[ 1, 'desc' ]],
  pageLength: 25,
  });
  let options = {
  "sScrollX": "100%",
  "sScrollXInner": "110%",
  "bScrollCollapse": true,
  "colReorder": true,
  
  };
  $('#tabls').DataTable(options);
  //   {
  //       buttons: dtButtons,  
  //   }),
  
  
  $('a[data-toggle="tab"]').on('shown.bs.tab', function(e){
    $($.fn.dataTable.tables(true)).DataTable()
        .columns.adjust();
        
  });

  })

  </script>

  @endsection

Please advise me to solve this issue Thank you for your time.

It would be better to do the [pagination](https://laravel.com/docs/8.x/pagination) — iamab.in, Nov 16 '20 at 11:46

score 0 · Answer 1 · answered Nov 16 '20 at 10:30

0

Please refer this one for datatable integration https://github.com/yajra/laravel-datatables

answered Nov 16 '20 at 10:30

Nowfal

26
1

Firstly use the yajra package Nowfal has suggested so it creates the data properly in the back end. Secondly implement server side rendering https://datatables.net/examples/data_sources/server_side – justrusty Nov 16 '20 at 10:42

score 0 · Answer 2 · answered Nov 16 '20 at 12:30

Use server side datatables Install this

a working example from my side into controller

 if ($request->ajax()) {
        $timeZone = $request->timeZone;
        $query = Order::query();
        $data = $query->with('buyerOrders', 'sellerOrders', 'oderItems', 'teamMemberOrder')
            ->where('sellerId', Auth::id())
            ->where('status', OrderStatusEnum::COMPLETED)
            ->orderBy('createdAt', 'DESC')
            ->get();
        return Datatables::of($data)
            ->addIndexColumn()
            ->addColumn('C_name', function ($row) {
                return $row->buyerOrders->getFullNameAttribute();
            })
            ->addColumn('teamMember', function ($order) {
                if ($order->teamMemberId) {
                    return $order->teamMemberOrder->getFullNameAttribute();
                } else {
                    return 'N/A';
                }
            })
            ->addColumn('price', function (Order $order) {
                return $order->oderItems->map(function ($oderItem) {
                    return $oderItem->unitPrice * $oderItem->productQuantity;
                })->sum();
            })
            ->addColumn('isScheduled', function ($row) {
                if ($row->isScheduled == 1) {
                    return '
                <span class="badge badge-pill badge-info">Scheduled </span>
                ';
                } else {
                    return '
                <span class="badge badge-pill badge-success">Immediate</span>
                ';
                }
            })
            ->addColumn('booking', function ($row) {
                return $row->id;
            })
            ->addColumn('orderType', function ($row) {
                if ($row->isDeliverable == 1) {
                    return '
                <span class="badge badge-pill badge-info">Deliverable </span>
                ';
                } else {
                    return '
                <span class="badge badge-pill badge-success"> Non Deliverable</span>
                ';
                }
            })
            ->addColumn('orderPlace', function ($order) use ($timeZone) {
                return Carbon::parse($order->updatedAt)->setTimezone($timeZone)->format('Y-m-d H:i:s');

            })
            ->rawColumns(['isScheduled', 'orderType'])
            ->make(true);
    }
    return view("Store.order.completeOrder");
}

Speed up record Load time laravel using Datatable

2 Answers2