COUNT Issue

pvar

I have noticed that the COUNT function in Navision is not returning the exact count always. Based on the recommendation in some of the other posts regarding this same issue I have optimized the table in Navision and then I got the correct count.

We are using Navision 3.6 on SQL database. I have 3 questions.

1. Are there other factors contributing to this behavior?
2. Does COUNT perform better in any of the latest versions like 5.0 or 2009?
3. How often do you need to optimize the table?

Thanks

kine

Count on SQL is based on statistics. SQL is not going through the table to count the records.If you have wrong numbers, you need to update statistics.

pvar

I was talking about the COUNT command in Navision. Is it based on update statistics?

kine

if I remember it correctly, count in NAV leads to SELECT count(*) statement, and SQL will return the count based on statistics and not by counting the records in the set (by reading them). It means when there are old statistics for the table,the result can be incorrect. By "optimizing" the table the statistics are updated, thus result is correct.
But I am not expert in MS SQL, that's what my memory told me... :-k

pvar

That's what I was thinking too, but the two counts differ and that is why I turned to here for help.

I beleive the update statistics in SQL makes the count in sql (select count(*)) correct but the doubt is will that makes the count in Navision also correct or do we need to do the optimize in Navision for the Navision count to work properly? Confused!

kine

NAV optimization is only rebuilding indexes, updating statistics and deleting zero sift records. It is all what is done through SQL commands, there is nothing "NAV" in this... ;-)

pvar

Agreed but eventually that fixed the count in Navision also and that is what puzzling me! what makes the count in Navision go wrong? Again back to my question, is this a version related bug?

dmccrae

We need to differentiate between locked and unlocoked tables.

The COUNT in NAV will not issue a COUNT(*) but will go directly to the sysindexes table and read a record count from there, which is an approximate value maintained by SQL Server. This is fast, but can be incorrect (with unlocked tables, all reads from SQL can be 'incorrect' because of the Read Uncommitted isolation level used.)

If you lock the table from NAV with LOCKTABLE, then NAV will issue a SELECT COUNT(*) against SQL Server and this will be 100% correct.

So if you absolutely need a correct value when reading any data as a rule, you must lock the table, or what you read can be inaccurate.

Check these by turning on the Client Monitor and note the SQL statement being sent.

pvar

Fantastic explanation! I tested your theory by locking the table and I got the correct count!!

I always wanted to know the technique used behind many operations like this in Navision but the on line help fell short of explaining things in detail. Do you have any technical documentation on how things work in Navision?

Thanks

DenSter

dmccrae wrote:

The COUNT in NAV will not issue a COUNT(*) but will go directly to the sysindexes table and read a record count from there, which is an approximate value maintained by SQL Server. This is fast, but can be incorrect (with unlocked tables, all reads from SQL can be 'incorrect' because of the Read Uncommitted isolation level used.)

If you lock the table from NAV with LOCKTABLE, then NAV will issue a SELECT COUNT(*) against SQL Server and this will be 100% correct.

I am a bit surprised (understatement) that COUNT does not always just return the correct count of records. We have COUNT and COUNTAPPROX available to us, and from what the helpfile says I've always thought that COUNTAPPROX is an approximation of the number of records, and COUNT will always return the exact count. On SQL Server I've always thought that COUNTAPPROX goes to the system tables, and COUNT simply counts the records. Why do we even have two count methods?

Having to do a LOCKTABLE to get an accurate COUNT should be in the help file by the way.

dmccrae

The reason for the two methods is that COUNTAPPROX came in a later version specifically to satisfy the requirement of fast but inaccurate counts for any applied filter.

The optimization for COUNT applies only when there are no filters set, and takes advantage of the fact that SQL Server maintains a record count for the entire table which is not necessarily accurate, but often is - thus the query against sysindexes. Now, by the time COUNTAPPROX was available, COUNT was already producing fast counts in app code and to change this to doing a COUNT(*) would have meant performance regressions for large tables. So it was left as it is. I do agree with you that this is not made explicit in the documentation.

And COUNTAPPROX does exactly as it says, and works with filters. It does this by using SHOWPLAN and asks SQL Server for its estimate of what will match the filter. This is pretty quick and a good approach for an approximation, for progress dialogs etc.

Regarding the accuracy of COUNT, we need to consider what data you get in general for unlocked tables - applicable to FIND, GET, FINDSET and so on. Here we are working with Read Uncommitted isolation so you essentially cannot and should not trust that what you read is current. For the purposes of accurate business logic this is not acceptable in general, which is why it does not matter in that case that the COUNT might be wrong. Because any other read you do might be wrong too. You have to LOCKTABLE to perform consistent business logic, regardless.

Regarding the point about technical documentation then I would say there is nothing like this produced by Microsoft. The best way to determine behavior is to use the Client Monitor in my opinion and try out some very simple statements (but note that many AL methods (e.g. FIND) can produce different SQL statements after several executions because, to adapt to the best pattern). Its a great tool for that. Also, of course this forum and the various blogs tend also to provide much of the implementation details like this.

kine

Ok, it means I mixed two things together. Thanks for the explanation how it works... =D>

Yaroslav_Gaponov

Hi

I agree that COUNT function does not always return the correct value.
I use code like this without any LOCK functions:

CLEAR(rec);
rec.FIND('-');
COUNT_REAL := rec.NEXT(2147483647);

DenSter

Which, if I understand Dean's explanation, could now be replaced with:

rec.LOCKTABLE;
TheAccurateCount := rec.COUNT;

Yaroslav_Gaponov

Yaroslav_Gaponov

DenSter wrote:

Which, if I understand Dean's explanation, could now be replaced with:

rec.LOCKTABLE;
TheAccurateCount := rec.COUNT;

...but I think that my method is cleaner and more understandable

rec.FIND('-');
TheAccurateCount := rec.NEXT(2147483647);

DenSter

You should put a smiley face in your post when you are joking

kine

Yaroslav Gaponov wrote:

DenSter wrote:

Which, if I understand Dean's explanation, could now be replaced with:

rec.LOCKTABLE;
TheAccurateCount := rec.COUNT;

...but I think that my method is cleaner and more understandable

rec.FIND('-');
TheAccurateCount := rec.NEXT(2147483647);

It depends, because do not forget about dirty reads... still you can read records which are not commited... and thus the count can be wrong in next statement...

DenSter

Yes, it depends on the timing really. So what it is is this:

rec.LOCKTABLE;
TheAccurateCountAtTheSplitMilliNanoSecondThatYouIssueTheCountCommand := rec.COUNT;

The next split milli nano second all bets are off

poppins

Hi, I know it is an old post but I have an issue where I need to use COUNT
I guess COUNT will give me correct values if I am working with a temporary record, I am wrong?