Slow performance when loading more than 10k records

on page load: 8.00 seconds

Question: Does anyone have to face a similar issue when the data more than 10k, it's will slow to return the data? Any idea for optimize ya? When user access to the pages, it will took almost 8 seconds to load all the data even I already index in the table.

System details

PHP 7.3, Laravel 8.4, Laravel-Yajra-Datatables v9.18.

Code

Controller

public function getPlayerlistAction(Request $request)
    {
        if ($request->ajax()) {

            $player = Player::where('parent_id', Auth::user()->id)
                        ->with(['getGames' => function ($q){
                            $q->select('id', 'title', 'display_name');
                        }])
                        ->orderBy('created_at', 'desc')
                        ->get()
                        ->toArray();

            return Datatables::of($player)
                //->addIndexColumn()
                ->addColumn('games', function ($row) {
                    $data = $row['get_games']['display_name'];
                    return $data;
                })
                ->addColumn('phone_number', function ($row) {
                    $data = "";
                    if($row['phone_number'] == '000'){
                        $data = "-";
                    }else{
                        $data = $row['phone_number'];
                    }
                    return $data;
                })
                ->addColumn('balance', function ($row) {
                    $data = number_format($row['balance'], 2);
                    return $data;
                })
                ->addColumn('status', function ($row) {
                    if ($row['status'] == 1) {
                        $actionBtn = '<span class="badge badge-pill badge-success">' . __('mainlanguage.enabledtxt') . '</span>';
                    } else if($row['status'] == 0) {
                        $actionBtn = '<span class="badge badge-pill badge-danger">' . __('mainlanguage.disabledtxt') .'</span>';
                    }
                    return $actionBtn;
                })
                ->addColumn('action', function ($row) {
                   $actionBtn  = '<span><button class="btn btn-primary btn-sm mt-1 mr-1 setscrore" data-gameusername="' . $row['game_username'] . '" data-game="' . $row['get_games']['title'] . '" data-username="' . $row['player_no'] . '" data-gid="' . $row['games_id'] . '"><i class="fa fa-credit-card"></i> ' . __('mainlanguage.setscoretxt') . '</button>';
                   $actionBtn .= '<button class="btn bg-orange color-palette btn-sm mt-1 mr-1 resetpwd" data-gameusername="' . $row['game_username'] . '"data-gid="' . $row['games_id'] . '"data-username="'.$row['player_no'].'" ><i class="fa fa-unlock"></i> ' . __('mainlanguage.resetpasstxt') . '</button>';
                   if($row['status'] == 1){
                        $actionBtn .= '<button class="btn btn-danger btn-sm mt-1 mr-1 disable" datatype=0 data-gameusername="' . $row['game_username'] . '" data-gid="' . $row['games_id'] . '" data-username="' . $row['player_no'] . '"><i class="fa fa-ban"></i> ' . __('mainlanguage.disabletxt') . '</button>';
                   }else{
                        $actionBtn .= '<button class="btn btn-success btn-sm mt-1 mr-1 disable" datatype=1 data-gameusername="' . $row['game_username'] . '" data-gid="' . $row['games_id'] . '" data-username="' . $row['player_no'] . '"><i class="fa fa-ban"></i> ' . __('mainlanguage.enabledtxt') . '</button>';
                   }
                   $actionBtn .= '<button class="btn bg-teal color-palette btn-sm mt-1 mr-1 gamelog" data-gamename="'.$row['get_games']['title'].'" data-gameusername="' . $row['game_username'] . '" data-gid="' . $row['games_id'] . '" data-username="' . $row['player_no'] .'"><i class="fa fa-scroll"></i> ' . __('mainlanguage.gamelogtxt') . '</button>';
                   $actionBtn .= '<button class="btn bg-maroon color-palette btn-sm mt-1 mr-1 transactionlog" data-gameusername="' . $row['game_username'] . '" data-gid="' . $row['games_id'] . '" data-username="' . $row['player_no'] .'"><i class="fa fa-book"></i> ' . __('mainlanguage.tranactilogtxt') . '</button></span>';
                   return $actionBtn;
                })
                ->rawColumns(['games', 'balance', 'status', 'action', 'phone'])
                ->make(true);
        }
    }

JS

$('.playertable').DataTable({
            "language": {
                "url": "//cdn.datatables.net/plug-ins/9dcbecd42ad/i18n/" lag
            },
            processing: true,
            {{-- serverSide: true,  --}}
            responsive: true,
            searching:  true,
            "order": [[ 5, "desc" ]],
            ajax: {
                url: "{{ route('getplayerlist') }}",
                type: 'GET',
                data: function (d) {
                    {{-- d.name = $('#name').val();
                    d.email = $('#email').val(); --}}
                }
            },
            columns: [
                    {data: 'games', name: 'games'},
                    {data: 'player_no', name: 'username'},
                    {data: 'game_username', name: 'gameid'},
                    {data: 'balance', name: 'balance'},
                    {data: 'phone_number', name: 'phone'},
                    {data: 'created_at', name: 'createdate'},
                    {data: 'status', name: 'status'},
                    {data: 'action', name: 'action', orderable: false, searchable: false, 'className': "dt-center"},
                ]
        });

CodePudding user response：

Uncomment serverSide: true and

Remove this

->get()->toArray();

and replace it with this:

->latest();

CodePudding user response：

Change your code to :

$player = Player::where('parent_id', Auth::user()->id)
      ->with(['getGames' => function ($q){
          $q->select('id', 'title', 'display_name');
  }])
  ->orderBy('created_at', 'desc')
  ->query();

  return Datatables::eloquent($player)
  //....