Slow controller performance with more than 5000 rows of data

[edpmasterindo]

Hello... i got very slow performance when loading more than 5000 rows of data, but paginated only show 10 rows per page.
It takes about 4 secs to completely loaded the table, with laravel normal table and pagination it loads instantly.

Here is my controller:

public function index(Request $request)
    {
        if($request->ajax()){
            return DataTables::of(User::with('roles')->orderBy('name')->get())
                ->addIndexColumn()
                ->addColumn('role', function(User $user){
                    return $user->roles->first() ? $user->roles->first()->name : '';
                })
                ->addColumn('action', function(User $user){
                    return '
                        <a class="btn btn-link btn-sm text-decoration-none text-info" href="/dashboard/resetpassword/'.$user->id.'" data-bs-toggle="tooltip" data-bs-title="Reset Password"><i class="bi bi-arrow-repeat"></i></a>
                        <a class="btn btn-link btn-sm text-decoration-none text-warning" href="'.route("users.edit", $user).'" data-bs-toggle="tooltip" data-bs-title="Edit User"><i class="bi bi-pencil-square"></i></a>
                        <form action="'.route('users.destroy', $user).'" method="post" class="d-inline">
                            <input type="hidden" name="_method" value="delete">
                            <input type="hidden" name="_token" value="'.csrf_token().'" />
                            <button type="submit" class="btn btn-link btn-sm text-decoration-none text-danger" data-bs-toggle="tooltip" data-bs-title="Delete User" onclick="return confirm(\'are you sure?\')"><i class="bi bi-trash3  "></i></button>
                        </form>
                    ';
                })
                ->tojson();
        }

        return view('dashboard.users.index');
    }

I check with clockwork the database is run normally with only running 5 queries, but the controller part is so slow...
Any idea what cause the problem?

[yajra]

Use query instead of collection:

User::with('roles')->select('users.*')

[edpmasterindo]

thank you... it loads instantly now....
what really the problem with collection?

[yajra]

Collection loads all records before searching/paging which causes a large memory usage.