a record count question

Roelof

I'm using version 5.00 but probably the previous version have the same problem.
I just have a big time to speed up a record count process. Im using the best keys I can create but if I do a rec.Count, it just takes for ever if the recordset is pretty large but the subset want I want to count is small. Like: 30 records on a total of 10.000.

Any help is welcome, because this makes me crazy.

Albertvh

Hi Roelof,

Maybe a stupid question but have you done the following
before doing the COUNT

1) SETCURRENTKEY
2) SETRANGE or SETFILTER

then do the COUNT

hope this helps

Albert

Savatage

note: you should be on 5.1

jlandeen

There is a built in function in Navision to make this process faster (but less accurate). Depending on where and how you're using the count it could help. If you are trying to build a progress bar or give the user some feedback and you don't need to be 100% accurate you can use the COUNTAPPROX function. The one downside is that it is only an approximation.

One other thing....what key are you using? Have you chosen a key that works well given the filters that you are applying? The keys can make all the difference when working with a recordset.

David_Singleton

Savatage wrote:

note: you should be on 5.1

Do you know something that we don't know?

Mbad

SQL or native?

Savatage

Did I jump the Gun :oops:

kuju

Hi,

Have you ever tried the following:

Add a decimal field "MyCount" to your table and fill this up with "initvalue" 1.

Instead of using the count or Countapprox function create a SumIndexField for this field and link this to your key your working with.

In your C/AL code place the necessary filters and use the calcsums function on the "MyCount" field.

So, the result is that NAV doesn't count the records to get to his numbers but uses the SIFT technology which makes it extremely fast!

It really speeds up performance...

nunomaia

Function COUNT isn’t optimized for native database. It must go to every record to make count. In SQL count function a terrible performance. So has alternative you could use SUMINDEX fields, don’t forget that performance will decrease during insert, to maintain SUMINDEX.