How to speed up loading table rows when using datatable in Laravel

Question

Im using datatable and I am facing a very strange situation. The data needs a long time to appear, especially if the records are very large. I'm not using Yajra and not looking for using it in laravel, with datatable all are fine but this is the isuue I face now, is there any solution to make data fast? I know may I have to use pagination but pagination without ajax is a big problem specially the search in datatable Hope you have solution for this issue

Controller:

  public function index()
   {
    $data = User::all();

   return view('managedashboard.ownerdashboard.allcustomers.archive', compact('data'));
     }

Datatable:

        TableDatatablesEditable = function() {
            var e = function() {
                var e = $("#sample_1"),
                    t = e.dataTable({
                        dom: "Bfrtip",
                        buttons: [{
                            extend: "print",
                       exportOptions: {
                             columns: ['.export'],
                                      },
                            className: "btn dark btn-outline"
                        }, {
                            extend: "pdf",
                        exportOptions: {
                             columns: ['.export'],
                                      },                       
                            className: "btn green btn-outline"
                        }, {
                            extend: 'csvHtml5',
                       exportOptions: {
                             columns: ['.export'],
                                      },


                       className: "btn-outline"
                        }],
                        language: {
                            lengthMenu: " _MENU_ records"
                        },
                        columnDefs: [{
                            orderable: !0,
                            targets: [0]
                        }, {
                            searchable: !0,
                            targets: [0]
                        }],
                        order: [
                            [2, "desc"]
                        ],
                        lengthMenu: [
                            [5, 10, 15, 20, -1],
                            [5, 10, 15, 20, "All"]
                        ],
                        pageLength: 20,
                        dom: "<'row' <'col-md-12'B>><'row'<'col-md-6 col-sm-12'l><'col-md-6 col-sm-12'f>r><'table-scrollable't><'row'<'col-md-5 col-sm-12'i><'col-md-7 col-sm-12'p>>"
                    });
                $("#sample_editable_1_wrapper");
                e.on("click", ".delete", function(e) {
                    e.preventDefault();
                    var a = $(this).parents("tr")[0],
                        l = $(this).closest("tr").attr("id"),
                        n = {
                            _token: $("input[name='_token']").val()
                        };
                    n = jQuery.param(n), BootstrapDialog.confirm("Are you sure?", function(e) {
                        if (e) {
                            var o = n,
                                r = "/" + locationUrl[3]+"/"+locationUrl[4] + "/" + l;
                            ajaxCall("DELETE", r, o, "result", "", !1, "delete"), t.fnDeleteRow(a)
                        }
                    })
                })
            };
            return {
                init: function() {
                    e()
                }
            }
        }();
    jQuery(document).ready(function() {
        TableDatatablesEditable.init()
    });

php jquery ajax datata

Answer 1

Try this example. This is only example.

Change as per your requirement. Include in header

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.10.12/datatables.min.css"/>

<script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.10.12/datatables.min.js"></script>

Your table looks like

<div class="row">
    <div class="col-md-12">
               <table class="table table-bordered" id="posts">
                    <thead>
                           <th>Id</th>
                           <th>Title</th>
                           <th>Body</th>
                           <th>Created At</th>
                           <th>Options</th>
                    </thead>                
               </table>
        </div>
</div>

Your javascript code

<script>
    $(document).ready(function () {
        $('#posts').DataTable({
            "processing": true,
            "serverSide": true,
            "ajax":{
                     "url": "{{ url('allposts') }}",
                     "dataType": "json",
                     "type": "POST",
                     "data":{ _token: "{{csrf_token()}}"}
                   },
            "columns": [
                { "data": "id" },
                { "data": "title" },
                { "data": "body" },
                { "data": "created_at" },
                { "data": "options" }
            ]    

        });
    });
</script>

And finally Your controller should be.

public function allPosts(Request $request)
    {

        $columns = array( 
                            0 =>'id', 
                            1 =>'title',
                            2=> 'body',
                            3=> 'created_at',
                            4=> 'id',
                        );

        $totalData = Post::count();

        $totalFiltered = $totalData; 

        $limit = $request->input('length');
        $start = $request->input('start');
        $order = $columns[$request->input('order.0.column')];
        $dir = $request->input('order.0.dir');

        if(empty($request->input('search.value')))
        {            
            $posts = Post::offset($start)
                         ->limit($limit)
                         ->orderBy($order,$dir)
                         ->get();
        }
        else {
            $search = $request->input('search.value'); 

            $posts =  Post::where('id','LIKE',"%{$search}%")
                            ->orWhere('title', 'LIKE',"%{$search}%")
                            ->offset($start)
                            ->limit($limit)
                            ->orderBy($order,$dir)
                            ->get();

            $totalFiltered = Post::where('id','LIKE',"%{$search}%")
                             ->orWhere('title', 'LIKE',"%{$search}%")
                             ->count();
        }

        $data = array();
        if(!empty($posts))
        {
            foreach ($posts as $post)
            {
                $show =  route('posts.show',$post->id);
                $edit =  route('posts.edit',$post->id);

                $nestedData['id'] = $post->id;
                $nestedData['title'] = $post->title;
                $nestedData['body'] = substr(strip_tags($post->body),0,50)."...";
                $nestedData['created_at'] = date('j M Y h:i a',strtotime($post->created_at));
                $nestedData['options'] = "&emsp;<a href='{$show}' title='SHOW' ><span class='glyphicon glyphicon-list'></span></a>
                                          &emsp;<a href='{$edit}' title='EDIT' ><span class='glyphicon glyphicon-edit'></span></a>";
                $data[] = $nestedData;

            }
        }

        $json_data = array(
                    "draw"            => intval($request->input('draw')),  
                    "recordsTotal"    => intval($totalData),  
                    "recordsFiltered" => intval($totalFiltered), 
                    "data"            => $data   
                    );

        echo json_encode($json_data); 

    }