Slow loading when data more than 1000 #931

stefensuhat · 2017-01-20T03:38:00Z

Summary of problem or feature request

I used this plugin for very long time. but after few days it slow me down (really slow). I try to query > 1K data. It keep loading for at least 5 seconds or more to show the data. It happen when change pagination too.

Code snippet of problem

public function getData()
{
    $brands = Brand::select('id', 'name');

    return Datatables::of($brands)
        ->addColumn('action', function ($brand) {
            return '
            <a href="/brands/' . $brand->id . '/edit" class="btn btn-xs btn-primary">
                <i class="fa fa-pencil"></i> Edit
            </a>
            <a href="#" id="delete-button" data-id="' . $brand->id . '" class="btn btn-xs btn-danger">
            <i class="fa fa-times"></i> Delete
            </a>
            ';
        })
        ->make(true);
}

System details

Digital Ocean $10.

Operating System Ubuntu 16.04
PHP Version 7.1
Laravel Version 5.3
Laravel-Datatables Version Latest

The text was updated successfully, but these errors were encountered:

mverma16 · 2017-01-20T12:26:13Z

I am facing the same issue, with more than 1 lac records in database. But my query and scenario is bit complex. In my table I am showing data from more than 3 tables. And I am edit column values before rendering the data into table.

This is the code for my view

   /**
     * 
     *
     * function to return table view
     */
    public function inbox_ticket_list() {
        $table = \Datatable::table()
                ->addColumn(
                        "", Lang::get('lang.subject'), Lang::get('lang.ticket_id'), Lang::get('lang.priority'), Lang::get('lang.from'), Lang::get('lang.assigned_to'), Lang::get('lang.last_activity'), Lang::get('lang.created-at'))
                ->noScript();

        return view('themes.default1.agent.helpdesk.ticket.inbox', compact('table'));
    }

In my view i am using custom blade file for loading Datatble JavaScript

{!!$table->render('vendor.Chumper.template')!!}
{!! $table->script('vendor.Chumper.ticket-javascript') !!}

In my JavaScript blade file I am calling a route for server side processing, which generates query builder for my table with joins and then calls a function which render table.
ticket-javascript.blade

<?php
$segments = \Request::segments();
$segment = "";
foreach($segments as $seg){
    $segment.="/".$seg;
}
?>
<script type="text/javascript">
        function myFunction()
        {
            return jQuery('#chumper').dataTable({
                "sDom": "<'row'<'col-xs-6'l><'col-xs-6'f>r>"+
                        "t"+
                        "<'row'<'col-xs-6'i><'col-xs-6'p>>",
                "sPaginationType": "full_numbers",
                "bProcessing": true,
                "bServerSide": true,
                "lengthMenu": [[10, 25, 50, 100, 500], [10, 25, 50, 100, 500]],
                "ajax": {
                    url: "{{url('filter')}}",
                    data: function (d) {
                        d.labels = $('select[name=label]').val();
                        d.tags = $('select[name=tag]').val();
                        d.segment = "{{$segment}}";
                    }
                },
                "aaSorting": sort,
                "columnDefs": [
                    { "searchable": false, "targets": [6,7] },
                    { "visible": last, "targets": 6 },
                    {"visible": create, "targets":7},
                ],
                "fnCreatedRow": function (nRow, aData, iDataIndex) {
                    var str = aData[3];
                    if (str.search("#000") == -1) {
                        $("td", nRow).css({"background-color": "#F3F3F3", "font-weight": "600", "border-bottom": "solid 0.5px #ddd", "border-right": "solid 0.5px #F3F3F3"});
                        $("td", nRow).mouseenter(function () {
                            $("td", nRow).css({"background-color": "#DEDFE0", "font-weight": "600", "border-bottom": "solid 0.5px #ddd", "border-right": "solid 0.5px #DEDFE0"});
                        });
                        $("td", nRow).mouseleave(function () {
                            $("td", nRow).css({"background-color": "#F3F3F3", "font-weight": "600", "border-bottom": "solid 0.5px #ddd", "border-right": "solid 0.5px #F3F3F3"});
                        });
                    } else {
                        $("td", nRow).css({"background-color": "white", "border-bottom": "solid 0.5px #ddd", "border-right": "solid 0.5px white"});
                        $("td", nRow).mouseenter(function () {
                            $("td", nRow).css({"background-color": "#DEDFE0", "border-bottom": "solid 0.5px #ddd", "border-right": "solid 0.5px #DEDFE0"});
                        });
                        $("td", nRow).mouseleave(function () {
                            $("td", nRow).css({"background-color": "white", "border-bottom": "solid 0.5px #ddd", "border-right": "solid 0.5px white"});
                        });
                    }
                }
            });
        }
</script>

Function to build querybuilder

public function table(){
        // if (Auth::user()->role == 'admin') {
            $ticket = new Tickets();
            $tickets = $ticket
                    ->leftJoin('ticket_thread', function ($join) {
                        $join->on('tickets.id', '=', 'ticket_thread.ticket_id')
                        ->whereNotNull('title')
                        ->where('ticket_thread.is_internal', '<>', 1);
                    })
                    ->leftJoin('ticket_thread as ticket_thread2', 'ticket_thread2.ticket_id', '=', 'tickets.id')
                    ->Join('ticket_source', 'ticket_source.id', '=', 'tickets.source')
                    ->leftJoin('ticket_priority', 'ticket_priority.priority_id', '=', 'tickets.priority_id')
                    ->leftJoin('users as u', 'u.id', '=', 'tickets.user_id')
                    ->leftJoin('users as u1', 'u1.id', '=', 'tickets.assigned_to')
                    ->leftJoin('ticket_attachment', 'ticket_attachment.thread_id', '=', 'ticket_thread.id')
                    ->leftJoin('teams', 'teams.id', '=', 'tickets.team_id')
                    ->leftJoin('ticket_collaborator', 'ticket_collaborator.ticket_id', '=', 'tickets.id')
                    ->select(
                        'tickets.id',
                        // 'tickets.team_id',
                        'ticket_thread.title',
                        'tickets.ticket_number',
                        'ticket_priority.priority',
                        'u.user_name as user_name',
                        'u1.user_name as assign_user_name',
                        \DB::raw('max(ticket_thread.updated_at) as updated_at'),
                        \DB::raw('min(ticket_thread.updated_at) as created_at'),
                        'u.first_name as first_name',                        
                        'u.last_name as last_name',
                        'u1.first_name as assign_first_name',
                        'u1.last_name as assign_last_name',
                        'ticket_priority.priority_color',
                        'teams.name',
                        DB::raw('COUNT(DISTINCT ticket_thread2.id) as countthread'),
                        DB::raw('COUNT(ticket_attachment.thread_id) as countattachment'),
                        DB::raw('COUNT(ticket_collaborator.ticket_id) as countcollaborator'),
                        'tickets.status',
                        'tickets.user_id',
                        'tickets.priority_id', 'tickets.assigned_to',
                        'ticket_status.name as tickets_status',
                        'ticket_source.css_class as css',
                        DB::raw('substring_index(group_concat(ticket_thread.poster order by ticket_thread.id desc) , ",", 1) as last_replier'),
                        DB::raw('substring_index(group_concat(ticket_thread.title order by ticket_thread.id asc) , ",", 1) as ticket_title'),
                        'u.active as verified')
                    ->groupby('tickets.id');
        return \Ttable::getTable($table); // call to function which renders table
    }

Finally the function which renders data in table

public static function getTable($tickets) {
        return \Datatables::of($tickets)
                        ->addColumn('id', function ($tickets) {
                            return "<input type='checkbox' name='select_all[]' id='" . $tickets->id . "' onclick='someFunction(this.id)' class='selectval icheckbox_flat-blue' value='" . $tickets->id . "'></input>";
                        })
                        ->addColumn('title', function ($tickets) {
                            if (isset($tickets->ticket_title)) {
                                $string = mb_substr($tickets->ticket_title, 0, 20, 'UTF-8');
                            } else {
                                $string = Lang::get('lang.no-subject');
                            }
                            $collab = $tickets->countcollaborator;
                            if ($collab > 0) {
                                $collabString = '&nbsp;<i class="fa fa-users"></i>';
                            } else {
                                $collabString = null;
                            }
                            $attachCount = $tickets->countattachment;
                            if ($attachCount > 0) {
                                $attachString = '&nbsp;<i class="fa fa-paperclip"></i>';
                            } else {
                                $attachString = '';
                            }
                            $css = $tickets->css;
                            $titles = '';
                            if ($tickets->ticket_title) {
                                $titles = $tickets->ticket_title;
                            }
                            $tooltip_script = self::tooltip($tickets->id);
                            return "<div class='tooltip1' id='tool" . $tickets->id . "'>
                            <a href='" . route('ticket.thread', [$tickets->id]) . "'>" . ucfirst($string) . "&nbsp;<span style='color:green'>(" . $tickets->countthread . ") <i class='" . $css . "'></i></span>
                            </a>" . $collabString . $attachString . $tooltip_script .
                                    "<span class='tooltiptext'  id='tooltip" . $tickets->id . "'>Loading...</span></div>";
                        })
                        ->addColumn('ticket_number', function ($tickets) {
                            return "<a href='" . route('ticket.thread', [$tickets->id]) . "' title='" . $tickets->ticket_number . "'>#" . $tickets->ticket_number . '</a>';
                        })
                        ->addColumn('priority', function ($tickets) {
                            $rep = ($tickets->last_replier == 'client') ? '#F39C12' : '#000';
                            $priority = $tickets->priority;
                            if ($priority != null) {
                                $prio = '<button class="btn btn-xs ' . $rep . '" style="background-color: ' . $tickets->priority_color . '; color:#F7FBCB">' . ucfirst($tickets->priority) . '</button>';
                            } else {
                                $prio = $tickets->last_relier_role;
                            }
                            return $prio;
                        })
                        ->addColumn('user_name', function ($tickets) {
                            $from = $tickets->first_name;
                            $url = route('user.show', $tickets->user_id);
                            $name = $tickets->user_name;
                            if ($from) {
                                $name = utfEncoding($tickets->first_name) . ' ' . utfEncoding($tickets->last_name);
                            }
                            $color = '';
                            if ($tickets->verified == 0 || $tickets->verified == '0') {
                                $color = "<i class='fa fa-exclamation-triangle'  title='" . Lang::get('lang.accoutn-not-verified') . "'></i>";
                            }
                            return "<a href='" . $url . "' title='" . Lang::get('lang.see-profile1') . ' ' . ucfirst($name) . '&apos;' . Lang::get('lang.see-profile2') . "'><span style='color:#508983'>" . ucfirst(str_limit($name, 30)) . ' <span style="color:#f75959">' . $color . '</span></span></a>';
                        })
                        ->addColumn('assign_user_name', function ($tickets) {
                            if ($tickets->assigned_to == null && $tickets->name == null) {
                                return "<span style='color:red'>Unassigned</span>";
                            } else {
                                $assign = $tickets->assign_user_name;
                                if ($tickets->assigned_to != null) {
                                    $assign = utfEncoding($tickets->assign_first_name) . ' ' . utfEncoding($tickets->assign_last_name);
                                
                                $url = route('user.show', $tickets->assigned_to);
                                return "<a href='" . $url . "' title='" . Lang::get('lang.see-profile1') . ' ' . ucfirst($assign) . '&apos;' . Lang::get('lang.see-profile2') . "'><span style='color:green'>" . mb_substr(ucfirst($assign), 0, 30, 'UTF-8') . '</span></a>';
                            } else{
                                $url1 = "#";
                                return "<a href='" . $url1 . "' title='" . Lang::get('lang.see-profile1') . ' ' . ucfirst($tickets->name) . '&apos;' . Lang::get('lang.see-profile2') . "'><span style='color:green'>" . mb_substr(ucfirst($tickets->name), 0, 30, 'UTF-8') . '</span></a>';
        
        
                                }
                            }
                        })
                        ->addColumn('updated_at', function ($tickets) {
                            $TicketDatarow = $tickets->updated_at;
                            $updated = '--';
                            if ($TicketDatarow) {
                                $updated = $tickets->updated_at;
                            }
                            return '<span style="display:none">' . $updated . '</span>' . UTC::usertimezone($updated);
                        })
                        ->addColumn('created_at', function ($tickets) {
                            $TicketDatarow = $tickets->created_at;
                            $updated = '--';
                            if ($TicketDatarow) {
                                $updated = $tickets->created_at;
                            }
                            return '<span style="display:none">' . $updated . '</span>' . UTC::usertimezone($updated);
                        })
                        ->make();
    }

This works fast for first 500 records but gets slow after data increases.
I am using php7, WAMP, mysql 5.7.14

@ssuhat Sorry for adding this long comment, but your issue seems same like mine and I don't want to raise a duplicate issue for it.

yajra · 2017-01-20T14:12:35Z

Not really sure on this but I'm using Digital Ocean too on my demo app and response is a bit fast I think. There must something else happening on your server? Maybe try checking your logs and your server resources?

stefensuhat · 2017-01-20T14:35:26Z

@yajra I'm using Laravel Forge to handle my server. So I think it's not server problem.

mverma16 · 2017-01-20T17:44:27Z

@yajra Do you have any suggestion for me?

mariani10 · 2017-01-27T13:26:53Z

Hi, i have the same problem.

im doing select of 8 fields with 3 join in a table of 25.000 records and this is slowly. (8 second per 25 records per page)

some tips for speed up the thing? Thanks

AdrienPoupa · 2017-02-03T00:24:37Z

Maybe you could enable the slow queries log and see if there is any recommendation?

mverma16 · 2017-02-03T10:38:51Z

@ssuhat and @mariani10, it's just a suggestion(ignore if you've already done it). Check execution time of your queries and try to optimize them. In my case the query I wrote was slow so it made datatable loading slow. After optimizing the query it's working faster now.

AdrienPoupa · 2017-02-03T11:14:52Z

Adding indexes can be the key to better performances

trongloikt192 · 2017-07-06T01:50:49Z

Replace:
$brands = Brand::select('id', 'name');
=>$brands = Brand::query();

yajra · 2017-07-06T02:05:43Z

Yes, indexing and optimizing the query is the solution for slow performance. And of course avoid using collection. Thanks!

alexkb · 2018-06-20T01:34:42Z

Just to add to this discussion, after you've made sure your passing the query to Datatables (and not the Collection - see comments above) - yes, you can add indexes, but you actually want to create composite indexes of the columns you're selecting. e.g. you want a composite index on columns foo, bar, created_at if you're query looks like this SELECT foo, bar, MAX(created) WHERE ... GROUP BY foo, bar'. When I did this I found my query speed was reduced by 50%.

BRVK · 2018-07-22T04:11:27Z

There is no way to get fast. I am using own laravel pagination 1000 records and after applying datatable and removed jquery datatable bottom pagination. That's fine to me

alkhachatryan · 2018-11-01T11:23:47Z

Replace:
$brands = Brand::select('id', 'name');
=>$brands = Brand::query();

This is the best solution.

yajra added performance question labels Jan 20, 2017

yajra closed this as completed Jul 6, 2017

github-actions bot locked and limited conversation to collaborators Oct 21, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Slow loading when data more than 1000 #931

Slow loading when data more than 1000 #931

stefensuhat commented Jan 20, 2017

mverma16 commented Jan 20, 2017 •

edited

yajra commented Jan 20, 2017

stefensuhat commented Jan 20, 2017

mverma16 commented Jan 20, 2017

mariani10 commented Jan 27, 2017

AdrienPoupa commented Feb 3, 2017

mverma16 commented Feb 3, 2017

AdrienPoupa commented Feb 3, 2017

trongloikt192 commented Jul 6, 2017

yajra commented Jul 6, 2017

alexkb commented Jun 20, 2018

BRVK commented Jul 22, 2018

alkhachatryan commented Nov 1, 2018

Slow loading when data more than 1000 #931

Slow loading when data more than 1000 #931

Comments

stefensuhat commented Jan 20, 2017

Summary of problem or feature request

Code snippet of problem

System details

mverma16 commented Jan 20, 2017 • edited

yajra commented Jan 20, 2017

stefensuhat commented Jan 20, 2017

mverma16 commented Jan 20, 2017

mariani10 commented Jan 27, 2017

AdrienPoupa commented Feb 3, 2017

mverma16 commented Feb 3, 2017

AdrienPoupa commented Feb 3, 2017

trongloikt192 commented Jul 6, 2017

yajra commented Jul 6, 2017

alexkb commented Jun 20, 2018

BRVK commented Jul 22, 2018

alkhachatryan commented Nov 1, 2018

mverma16 commented Jan 20, 2017 •

edited