What exactly does COUNT count?

  • Article

The command COUNT is pretty simple. But on the SQL option there are a number of different ways to count records, and each can give a different result. This is how the different ways of counting works in NAV on SQL:

 

There are two commands you can use: COUNT or COUNTAPPROX. As the name suggests, COUNTAPPROX is only designed to give an approximate count. But this does not necessarily mean that COUN is exact (I will come back to this later).

COUNTAPPROX:
This command doesn't actually count anything. It only asks SQL Server to make a query plan for a SELECT statement. Then it reads the estimated number of records from the query plan. This is how COUNTAPPROX looks in a profiler trace:

SET

SHOWPLAN_ALL ON

SELECT

* FROM "CRONUS International Ltd_$Standard Text" WITH (READUNCOMMITTED)

SET

SHOWPLAN_ALL OFF 

It is the same as if you click on Query -> "Display Estimated Execution Plan (Ctrl+L)" in SQL Server Management Studio, instead of running a query normally (Ctrl+E / F5). This tells SQL Server to only compile a query plan, but not actually run the query. The query plan will contain "estimated number of rows", which in turn is based on SQL Server statistics. This is the number that COUNTAPPROX returns.

 

COUNT:

COUNT works differently, depending on whether there is a filter on the table or not. Without a filter it can just go to Table-Information, and get the number of records from there. This is quicker than going to the actual table and count every record. On the SQL Side, it means counting the number of rows from the sysindexes table, like this:

SELECT

SUM(rows), SUM(reserved) FROM [dbo].[sysindexes]

But sysindexes is not always up to date! Only just after having updated statistics, can you rely on the numbers in this table. So COUNT, without a filter, will not always give you the correct number.

 

If you have a filter, for example:

Cust.SETRANGE("Currency Code",'EUR');

i := Cust.COUNT;

Then the method of just looking up table-information doesn't work. So NAV has to count the records the hard way. In this case we finally get an actual COUNT SQL command:

SELECT

COUNT(*) FROM "CRONUS International Ltd_$Customer"

But even this method of counting is still not necessarily accurate, because it reads uncommitted data. If you run the following code from another client, and leave the CONFIRM-dialog:

OnRun()
Cust.INSERT(TRUE);
Cust."Currency Code" := 'EUR';
Cust.MODIFY;

IF NOT CONFIRM('Continue?') THEN;

ERROR('Transaction rollback.');

Then, the COUNT above will include a record which never existed / was never committed.

The only way to get an exact count, is by applying a lock. For example:

Cust.LOCKTABLE;

i := Cust.COUNT;

This will give you an exact count, whether you have a filter or not. On the SQL side it will look like this:

SELECT

COUNT(*) FROM "CRONUS International Ltd_$Customer" WITH (UPDLOCK)

Because of the UPDLOCK it only counts committed records. And because of the lock, NAV knows not to just look up the number of records from table information, but to run a SELECT COUNT on SQL Server.

Lars Lohndorf-Larsen

Microsoft Dynamics UK

Microsoft Customer Service and Support (CSS) EMEA

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.