Free jqGrid 4.8.0 - Setting the total number of pa

Question

due to the bad performance on count(*) with our DB (when we deal with 50M records), we don't want to return back to total number of records in each slice of records we retrieve. Instead we want to work without it. I could set the total number of records to be MAX_INT for that matter within jsonReader:{records: MAX_INT}, but I want to make this better.

What I would like to do is to set the records for MAX_INT when the grid loads (that part I know how), but in parallel initiate a call for count(*) at the background, eventually when the real counter will be retrieved, I would like to override the MAX_INT with the real counter which is the real number of records.

How do I override the number of records counter?

Thanks,

Answer 1

First of all the it's well-known problem with COUNT(*). If one need to get just the number of rows of some specific table of the database then one can get the information very easy and very quickly

SELECT rows
FROM sys.partitions
WHERE index_id IN (0, 1)
    AND object_id = OBJECT_ID('dbo.Tablename')

In more common case (if the data are not only in one partition) one should use SUM(rows) instead of rows. See

I suppose that one could use in the same way sys.dm_db_partition_stats

SELECT row_count
FROM sys.dm_db_partition_stats
WHERE index_id IN (0, 1) AND object_id = OBJECT_ID('dbo.Tablename')

You can examine your database and test whether COUNT(*) returns the same values line the above SQL statements or some other close variants.

Independent from the problem with COUNT(*) you can use records or total defined as functions and don't return any records or total from the server. The records is not important in the most cases at all. It will be displayed on the pager if viewrecords: true option is used. You can use default viewrecords: false and to have no problem with setting the records field in the server response.

The total property of the server response will be used on the other side to set lastpage parameter of jqGrid, which will be used to verify whether "Next Page" and "Last Page" buttons of the pager will be enabled or not. What you can do is

jsonReader: {
    total: function (response) {
        return parseInt(response.page) + 1;
    }
}

alternatively you can return any other value like Number.MAX_VALUE for example or max integer 9007199254740992 (2⁵³, see the answer).