What exactly does COUNT count?


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 (4)

  1. Jan Stepanek says:

    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

  2. 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

  3. Jan Stepanek says:

    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

  4. 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

Skip to main content