How to detect serverside paging request?

mnbob70

I would like to optimize returning RecordsTotal so that I do not have to retrieve a record count on every page request. I've found that getting a count when paging results in some performance issues while using entity framework 6 whereas using Skip/Take is quick. If a user is just paging there should be no reason for the count to change yet I have to return that value every time. I'm trying to figure out how I can determine that nothing but a page change request has occurred so that I can return a cached RecordsTotal instead of performing another query server side.

bindrid

Easy enough to do.

In your:

  ajax:data:function(dataTableParms){
        // keep track of when something changes 
       // that would cause the total count to change and set this variable to true
        dataTableParms.needPageCount = false;
}

add dataFilter to your ajax such that

    ajax: { 
        blah:blah,
        blah:blah,
        dataFilter : function (resp) {
            if(resp.recordsTotal > -1) {
                sessionStorage.setItem("recordsTotal", resp.recordsTotal);
            }
            else {
                resp.recordsTotal = sessionStorage.getItem("recordsTotal");
             }
             return resp;
        }
     }, 
     blah:blah,

Then add code in your server to response to needPageCount.

mnbob70

Thank you. That definitely helps. By any chance is there an event associated with the paging control? That would be the easiest way to track a need for count change. Clicking any of the page buttons or Previous or Next should not require recordsTotal. I think that this would be a worthwhile optimization for DataTables.

mnbob70

Looks like the page event is what I need to work with.

bindrid

I was fixing a different problem but I think it might be applicable here.
Whenever there is a key press on the search box, the search is applied to the table.
When serverSide is set to true, that means that if you search for "one", you would have just fired off three round trips to the server. (I think Allen is planning to address this in the future).

I, for my solution, removed that event handler and added my own that required an enter key to cause it to initiate a search. Then for clarity, I also added a small search button next to the search window. Those event handlers could be used to tell the ajax handler that this is a criteria change and get a records total.

If your search options are simple, such as just using the search box, you can do something like

        ajax:data:function(dataTableParms){

            var lastSearch = sessionStorage.getItem("lastSearch");
            if(lastSearch === dataTableParms.search.value){
                dataTableParms.needPageCount = false;
            } else {
                // the very first time the page is loaded, lastSearch will be null 
                // and search.value will be an empty string so it should cause needPageCount to be set to true
                // If you have more than one data table on a page or using this on more than one page, you will have
                // a more unique naming convention for the sessionStorage key.
                dataTableParms.needPageCount = true;
                sessionStorage.setItem("lastSearch", dataTableParms.search.value);
            }

        }

bindrid

Almost Forgot, If you allow your user to change rows per page, you will have to track that change too.

mnbob70

Thanks. I had already dealt with both the search and column filters by requiring the enter key to start the searching/filtering. Because I am using ASP.NET MVC and EF6 for database access I'm finding bad performance issues especially as the number of filters increases. I used sample classes that someone else had created and made available in a different discussion on these forums but I am making tweaks to improve performance. I believe my next optimizations are to change how I get the count of records.

Right now I have the following on the client-side based on your comments. I pass in the records total and assign it to the returned value when not -1.

            ajax: {
                contentType: "application/json; charset=utf-8",
                data: function (data) {
                    if (paging) {
                        data.RecordsTotal = sessionStorage.getItem("recordsTotal");
                        paging = false;
                    } else {
                        sessionStorage.removeItem("recordsTotal");
                        data.RecordsTotal = -1;
                    }
                    return data = JSON.stringify(data);
                },
                dataFilter: function (data, type) {
                    var d = JSON.parse(data);
                    if (d.recordsTotal > -1) {
                        sessionStorage.setItem("recordsTotal", d.recordsTotal);
                    }
                    return data;
                },
                type: "POST",
                url: '@Url.RouteUrl("dthandler")'
            },

        documentsTable.on('page.dt', function () {
            paging = true;
        });

bindrid

All of my calls go against web methods in ASMX pages and from their to the database. My database does all of the work for figuring out what data should be sent, thet totals, etc. I have about 25,000 records with a page size of 15 (by default).

At this point, I have not had any performance issues, which is pretty good since I am more of a front end and middle layer guy than a db guy.

btw, I did post my classes, lol, you might be using mine

allan

I've seen performance issues in the past when using EF with very large data sets. Direct access to the database with ADO.NET is usually the way to go to address that.

Allan

mnbob70

Allan: Why is it necessary to return a total record count when paging? That value shouldn't change. My issue seems to be that the count query is taking longer than my paging query which uses skip and take. My next optimization is to use ADO.NET for the count because I do not like the EF SQL that is being generated.

allan

Why is it necessary to return a total record count when paging?

Two reasons:

1. For the information element shown at the bottom of the table telling the user how many records are in the table
2. So DataTables knows if there is a next page or not.

I'm not sure how EF works, but I've seen it take a really long time with large data sets. I've wondered if it is downloading all of the contents of the table from the database and then processing them locally rather than translating them to an SQL query. I don't know if that is the case or not, but the performance impact does seem to suggest it.

A direct ADO.NET query of a simple COUNT(id) should be very fast.

Allan

mnbob70

I guess I did not phrase that well enough. I understand why a count is needed but during paging DataTables should be able to use a cached value of the record count because the value is not going to change if all a user is doing is changing the page number.

kthorngren

during paging DataTables should be able to use a cached value of the record count because the value is not going to change if all a user is doing is changing the page number.

This is only true of the table is static. There may be cases where the number of records in the table change at anytime.

Kevin

allan

Yup. You could use a session storage parameter on the server-side to keep the count if you know it won't change.

Allan