Sdílet prostřednictvím

What exactly does COUNT count?

  • Článek

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.

Comments

  • Anonymous
    May 11, 2008
    Hi Lars, this is news for me. I believed than COUNT returns exact number of rows in all cases. I I want get fact and approximately nuwber of rows I use COUNTAPPROX. If I need exact nuber of rows I use COUNT and I accept situation that this statement is slow. This works If I use filters, but not works if I need exact number of rows without filter. Exact number of rows in whole table I can not get in NAV client. I knew that I can create view in SQL and link this as NAV table, but this is not solution. Jan Stepanek

  • Anonymous
    May 13, 2008
    Hello Jan, I agree with your logic, that COUNT should be exact, and COUNTAPPROX can be approximate. COUNT returns the exact number of rows whenever possible. But this requires a LOCKTABLE. Even if you use a view in SQL and count there, then this will also be unprecise, because it will include uncommitted records. Also, if you use COUNT with a filter, it will include uncommitted records. So it is not precise in any case. So again, you have to use LOCKTABLE before COUNT, and that is the only way to get the exact number of records. Best regards Lars

  • Anonymous
    May 13, 2008
    Hi Lars, Thank you for explanation, if I use LOCKTABLE before COUNT I get correct number of rows. Bat this behavior is not accptable. I must use different C/AL code on SQL and native db - LOCKTABLE+COUNT and COUNT. I must review C/AL code in existing aplication and correct it. I prefering that NAV statement COUNT send to SQL allways SELECT COUNT(*). best regards, Jan

  • Anonymous
    May 15, 2008
    Hi Jan, It is not something that is going to change - sorry. We can always discuss what is the best design, but the design that was chosen is based on the fact that SELECT COUNT is not accurate anyway, unless you run it in a transaction. So this is not something I can help changing - I can only explain the way that it works, Best regards Lars