1

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,

Tal
  • 391
  • 2
  • 11

1 Answers1

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 (253, see the answer).

Community
  • 1
  • 1
Oleg
  • 220,925
  • 34
  • 403
  • 798
  • Thanks Oleg, that is helpful. The problem is wider than just a table size, as it depends in the where part that add dynamic to this all problem. – Tal May 22 '15 at 14:10
  • @Tal: It's clear that `SELECT rows FROM sys.partitions` can help only in some very easy cases where one need to display the exact data from one table. Because of that I described how to use functions in `jsonReader` to prevent `COUNT(*)`. Do you tried this? – Oleg May 22 '15 at 14:20
  • Not yet, but I will. Seems like the right direction. Suppose I want to "fix" the count by an Async ajax call that will return me the real count later, how can I do that without reloading the grid again? – Tal May 22 '15 at 14:23
  • @Tal: You can just don't return any `total` currently and to use `parseInt(response.page) + 1`, but the code can be something like `jsonReader: { total: function (response) { return response.total || parseInt(response.page) + 1; }}`. You will use `page + 1` only if `total` is undefined or 0 in the server response. – Oleg May 22 '15 at 14:31
  • Thanks, let ne refrase my question: the server will never return back the total. The grid will always finish to load without the total, so the +1 plays perfectly fine here. – Tal May 22 '15 at 14:44
  • What I want to do is after tge grid finish to load, initiate a call in the background to retrieve the real count, and when the call return recreate the total back into the grid. – Tal May 22 '15 at 14:45
  • @Tal: The `total` is just set it later using `setGridParam` or just using `var p = $("#gridid").jqGrid("getGridParam");` to get the reference to parameters and then set `p.lastpage` to new value (correct total number of pages) and `p.records`. If you want to display the information in the pager you can use `$("#gridid")[0].updatepager(false, false);` You can set `p.viewrecords = true` before and then `updatepager` will display the correct number of records. – Oleg May 22 '15 at 14:55
  • Got it! Thanks Oleg, will try it. – Tal May 22 '15 at 14:57
  • Works great! Thanks again. – Tal Jun 01 '15 at 21:05