Server Side Processing seems very slow...something configured wrong maybe?

braden87

So I recently set up server side processing with Scroller and the data loads all hunky dory but it takes on the upwards amount of 30+ seconds to actually render the page at the beginning. I know the first load times are usually longer, but I'm not sure if this time is within the usual amount. And that's even with deferRender: true. The actual controller method runs in around 7 seconds.

When scrolling down it will get up to the length amount (default is 99) and it will pause and take up to 15 seconds to display the rows. There's about 41,000 total in this table.

For some odd reason it will query the Ajax source three times on anything higher than 99 records ...I'm assuming each pause in the scroll triggers the Ajax. Is there a way to maybe extend this delay? This would explain the 15 seconds delay when scrolling past 99.

This is how I have it set up:

<script>
        $(document).ready(function () {
            $(LogTable).DataTable({
                serverSide: true,
                ajax: {
                   
                    url: "Log/GetAllLogData",
                    type: "POST",
                    dataType: "json" 
                },
                columns: [
                    {"data": "Id", "name":"Id"},
                    { "data": "SampleType", "name": "SampleType" },
                    { "data": "Value", "name": "Value" },
                    { "data": "Program", "name": "Program" },
                    { "data": "QC", "name": "QC" },
                    { "data": "Comments", "name": "Comments" },
                    { "data": "DateAssigned", "name": "Date Assigned" },
                    { "data": "CheckedInDate", "name": "CheckedInDate" },
                    { "data": "Discarded", "name": "Discarded" },
                ],
                processing: true,
                dom: "ftir",
                scrollY: 400,
                scroller: true,
                deferRender: true,
                order: [[0, 'desc']],
                search: {
                    smart: false
                },
                columnDefs: [{
                    targets: 'no-sort',
                    orderable: false
                },
                {
                    targets: 'date-col',
                    type: 'date',
                    orderSequence: [
                        'desc',
                        'asc'
                    ]
                }]
            });

        
        });
    </script>

And then the controller method that returns the Json:
The var logList that returns all the records does take about 5 seconds to get all the data due to the navigation properties, which I have plans to improve this.

public ActionResult GetAllLogData()
        {
            //Server side parameters
            int start = Convert.ToInt32(Request["start"]);
            int length = Convert.ToInt32(Request["length"]);
            string searchValue = Request["search[value]"];
            string sortColumName = Request["columns[" + Request["order[0][column]"] + "][name]"];
            string sortDirection = Request["order[0][dir]"];


            var logList = (from l in Manager.All<Log>() 
            select new  { l.Id, l.SampleType?.SampleType, l.LotNumber?.Value, l.Program?.Program,
            l.QualityControl?.QC, l.Comments, l.DateAssigned, l.CheckedInDate, l.Discarded }).ToList();

            int totalRows = logList.Count();

            //filter
            if (!string.IsNullOrEmpty(searchValue))
            {
                logList = logList.Where(
                    x => x.Id.ToString().Contains(searchValue.ToLower()) || 
                    x.SampleType.ToString().ToLower().Contains(searchValue.ToLower()) || 
                    x.Value.ToString().ToLower().Contains(searchValue.ToLower()) || 
                    x.Program.ToString().ToLower().Contains(searchValue.ToLower()) || 
                    x.QC.ToString().ToLower().Contains(searchValue.ToLower()) || 
                    x.Comments.ToString().ToLower().Contains(searchValue.ToLower()) || 
                    x.DateAssigned.ToShortDateString().ToLower().Contains(searchValue.ToLower()) || 
                    x.CheckedInDate.Value.ToString().Contains(searchValue.ToLower())).ToList();
            }

            int totalRowsAfterFiltering = logList.Count();

            //sorting
            logList = logList.OrderBy(sortColumName + " " + sortDirection).ToList();

            //paging 
            logList = logList.Skip(start).Take(length).ToList();

            return Json(new { data = logList, draw = Request["draw"], 
                             recordsTotal = totalRows, recordsFiltered = totalRowsAfterFiltering }, 
                             JsonRequestBehavior.AllowGet);
        }

kthorngren

A quick test to eliminate the Scroller extension would be to disable it then set the pageLength option to 99 (or even leave it default at first). You aren't doing any fancy rendering so it would seem the Datatbles should process 99 rows quickly.

I would start by looking at the browser's network tools to see how long it takes the response to be returned then, visually, see how long it takes Datatables to render the table. This should give you a good idea of where to start looking.

It looks like your controller code is processing the 41,000 record list (var logList = (from l in Manager.All<Log>() before filtering and returning the requested rows. Another test I would try is to simulate this list with 100 or 200 rows to see if the times improve.

If this doesn't help and you feel there is a delay in the Datatables render process then the developers will likely need a link to your page to help debug.

Kevin

braden87

So I found the problem with my initial question. There was another table on the same index page on a separate tab that was bringing in a ton of records. Found out my script that moved the data over didn't convert some dates properly. Now it takes roughly 5 seconds to load the page with all the tables and another 5 to load the data into the 41,000 record table. And subsequently about 5 seconds each time it reaches the length while scrolling. I just need to make some minor tweaks and it's all set.

Thank you for the suggestion!