locked
Please can you help me to make the report fast . It is very slow to bring the report in html to list 6000 rows RRS feed

  • Question

  • User-1355965324 posted

    In controller I am using the code. In html file I am using JS Datatable with row group function . I know the reason for the delay in report. Because I am using  the for each loop

    in cshtml file.  If I can avoid the for each loop, the report would be faster. I  tried to call ajax call to make it fast. But when I call the data using Ajax function , the Data table rowgroup is not working . Please help with suggested code

    [HttpPost]
            public IActionResult AttendanceReportCriteria(ReportViewModel report)
            {
                
                log = ReportService.GetEmployeeAttendanceReport(String.Join(",", report.Depot), String.Join(",", report.Department), String.Join(",", report.Employee), fromDate, toDate, connectionSettings);
                return View("AttendanceReport", report);
                
            }

    In the html

    https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css
    https://code.jquery.com/jquery-3.3.1.js
    https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js
    
    
     <table id="tblAttendance" class="table table-bordered table-striped" style="width:100%;font-size:90%">
                                    <thead class="thead-light">
                                        <tr>
                                            <th>
                                                Date
                                            </th>
                                            <th>
                                                Depot Name
                                            </th>
                                            <th>
                                                Employee Name
                                            </th>
    
                                            <th>
                                                Clocked In
                                            </th>
                                            <th>
                                                Clocked Out
                                            </th>
                                            <th>
                                                N. Attendance
                                            </th>
                                            <th>
                                                Day Off
                                            </th>
    
                                            <th>
                                                Sick
                                            </th>
                                            <th>
                                                Holiday
                                            </th>
                                            <th>
                                                Maternity
                                            </th>
                                            <th>
                                                Time Off
                                            </th>
                                            <th>
                                                Time Off Hrs
                                            </th>
                                            <th>
                                                Hrs
                                            </th>
                                            <th>
                                                Extra Hrs
                                            </th>
                                            <th>
                                                Remark
                                            </th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        @foreach (AttendanceLogModel log in ViewBag.AttendanceLogs as List<AttendanceLogModel>)
                                        {
                                            <tr>
                                                <td> @log.FromDate</td>
                                                <td> @log.DepotName</td>
                                                <td> @log.EmployeeName</td>
                                                <td> @log.ClockedIn</td>
                                                <td> @log.ClockedOut</td>
                                                <td> @Convert.ToInt32(@log.IsNormalAttendance)</td>
                                                <td> @Convert.ToInt32(@log.IsDayOffMarked)</td>
                                                <td> @Convert.ToInt32(@log.IsSickMarked)</td>
                                                <td> @Convert.ToInt32(@log.IsHolidayMarked)</td>
                                                <td> @Convert.ToInt32(@log.IsMaternityLeave)</td>
                                                <td> @Convert.ToInt32(@log.IsTimeOff)</td>
                                                <td> @log.TimeOffHrs</td>
                                                <td> @log.Hrs</td>
                                                <td> @Convert.ToInt32(@log.ExtraTimeHrs)</td>
                                                <td> @log.Remark</td>
    
                                            </tr>
    
                                        }
    
                                    </tbody>
    
                                </table>
    
    
    
    
    
    
    <script>
        $(document).ready(function () {
            $('#tblAttendance').DataTable({
                order: [
                    [2, 'asc'],
                    [1, 'asc']
                ],
                rowGroup: {
                    dataSrc: [2, 1],
                    startRender: function (rows, group) {
                        return $('<tr/>')
                            .append('<td class="td-left" >' + group + '</td>');
    
                    }
                },
                "columnDefs": [
                    {
                        "targets": [1],
                        "visible": false
                    },
                    {
                        "targets": [2],
                        "visible": false
                    },
                    {
                        "className": "dt-center", "targets": "_all"
                    },
                    { "width": "10%", "targets": 0 },
                    { "width": "10%", "targets": 14 },
                ],
                "pageLength": 40,
                scrollY: "500px",
                scrollX: true,
                 paging: true,
                 dom: 'Bfrtip',
                buttons: [
                    'copy',
                    'csv',
                    'excel',
                    {
                        extend: 'pdfHtml5',
                        orientation: 'landscape',
                        pageSize: 'LEGAL' }
                                  
                ]
                 
            });
        });
    </script>

    Please help it would be very appreciated

    Regards

    Pol

    Saturday, June 15, 2019 8:19 AM

All replies

  • User-474980206 posted
    The razor view is probably not the issue nor is the for loop. The query might be, but the real issue is the time it takes for the browser to render a table with 6000 rows (especially IE). you need to implement paging, or virtual scrolling.
    Saturday, June 15, 2019 3:18 PM
  • User-1355965324 posted

    I have checked the sql and  the result is very coming fast. If I can avoid the loop inside the html ,  I think I can bring the report in better speed. The same way I had bring the another report made faster by passing the ajax data without loop inside the html. But I dodnt know how  to call the ajax data to this report because , the datatable has rowgrouping for this report. When I pass the data through Ajax ,  the rowgrouping is not working.

    With Many Thanks

    Pol

    Saturday, June 15, 2019 7:08 PM
  • User1120430333 posted

    You can't avoid the loop if the data is in container that is enumerable. The thing to do as mentioned is implement some kind of paging logic where  a subset of limited data is being displayed. You have to give the illusion of speed by not slowing all the data queried all at once , but rather, page the data being displayed, a subset. 

    Saturday, June 15, 2019 7:47 PM
  • User753101303 posted

    Hi,

    How many rows do you show ? When showing too much the problem is rather in downloading/rendering data rather in querying the db. And in this case you usually try to let the user tell what he wants to see before showing anything and/or using pagination.

    Edit: jut never show 6000 rows (check the size of your HTML pages, more likely this time is spent in  downloading/rendering rows). Make sure the user can easily what he is really looking for (ie you should providing filtering and maybe paging capabilities)

    Saturday, June 15, 2019 10:17 PM
  • User-474980206 posted
    As your rows look compact, could could probably get away with rendering on the page as json, then use data tables Schiller in data mode:

    https://datatables.net/extensions/scroller/

    Saturday, June 15, 2019 11:36 PM
  • User-1355965324 posted

    From the javascript datatable forum says, it would be very delay  to load the data into JS datatable   from  the datatable row inside the html using the loop inside the html.

    So I am trying to solve the delay by calling the ajax int the same way given below. But the problem is when Itry this way  for the report, the grouping of the datatable is gone

     
    $('#tblClockVariance').DataTable({
                "processing": true,
                //"serverSide": true,
    
                "ajax": {
                    "url": "/Report/ClockingVarianceData",
                    "data":  report,
                     "dataSrc": function (json) {
                        return JSON.parse(json);
                    }
                },
    
     "columns": [
                    { "data": "EmployeeName" },
                    { "data": "FromDate" },
                    { "data": "ClockedIn" },
                    { "data": "ClockedOut" },


      

    Sunday, June 16, 2019 6:24 PM
  • User585649674 posted

    When are you calling the below code. It should be after the ajax call  and not in document.ready.

    $('#tblClockVariance').DataTable({})

    something like below

    $.ajax({
    url:'url',
    data:{},
    success:function((data){
    $('#tblClockVariance').DataTable({})
    })
    })

    for is slightly faster than foreach, especially when there is 6000 records

    Monday, June 17, 2019 6:37 AM
  • User-1764593085 posted

    Hi polachan,

    But the problem is when Itry this way  for the report, the grouping of the datatable is gone

    For your above question,make sure that you have gotten correct data from ajax and then check the correct property name of json result( such as emplyeeName not EmployeeName), and then populate it into dataTable with rowGroup using specific columns as dataSrc:

    $(document).ready(function () {
                $('#example').DataTable({
                    
                     "ajax": {
                            "url": "/Report/ClockingVarianceData",
    "data": report, "dataSrc": function (json) { return JSON.parse(json); } }, "columns": [ { "data": "employeeName" }, { "data": "formDate" }, { "data": "clockedIn" }, { "data": "clockedOut" }, //...other columns ], rowGroup: { dataSrc: ['formDate','employeeName'], startRender: function (rows, group, level) { return $('<tr/>') .append('<td colspan="3" class="td-left" >' + group + '</td>') } } }, //... }); });

    Best Regards,

    Xing

    Monday, June 17, 2019 7:50 AM
  • User753101303 posted

    Once again loading 6000 rows or more will always feel slower than loading data the user really wants to see at this time (check the size and download/rendering time for this payload which is likely where most of the time goes).

    You really should let the user to provide some filtering criterias *before* loading all that or loading the first page or top n rows to avoid loading too much data the user doesn't care about anyway. Loading all and then allowing to filter is sometimes done in a desktop app that runs on a local network but you really avoid that for a web app (doing the other way round ie letting the user to filter before loading data rather than wasting time to load thousand of rows and then have the user to filter anyway among that)

    Tuesday, June 18, 2019 8:43 AM