Drill down from FlowField is too slow...

dynewbie

Hi,

I am using Nav 4.0 on SQL: There is about 6.5 million records in GL Entry.
I have done optimization.

The flowfields in Debit Amount, Credit Amount, Balance etc. is too slow when I want to drill down. (Sometimes it takes more than 5 minutes.)

I do not know what to do. I added a new field which only has GL Account and Posting Date fields in SumIndexField but I think Navision does not get the best key. It uses the keys which listed on top.

Do I have to change key order? How can I force Navision to use the key which I want? Or is there any other solution for this drill down bottleneck?

Regards,

awarn

Hi,

The list form that you are drilling down to may need to have the key changed. This is the SourceTableView property. If you want the form to use a different key you need to change that property. Navisin does not pick the best key when opening forms.

Something else you could do is to set the key in code when you open the form.

New function: SetIntelligentKey, called OnOpenForm

IF (GETFILTER("G/L Account No.") <> '') AND (GETFILTER("Posting Date") <> '') then
SETCURRENTKY("G/L Account No.","Posting Date")
ELSE IF GETFILTER("Posting Date") <> '' THEN
SETCURRENTKEY("Posting Date");


You can make this a lot more complicated if you want, and then you might want to call the function from a button push (if the user changed filters), but this sort of thing might help.

-a

kriki

There are a lot of things to check:
-are the SQL-statistics ok
-do you regularly do an indexrebuild?

But after that, it is still possible SQL decides to do a tablescan (=reading ALL records!).
To avoid as much as possible this problem, you need a key that is a perfect match. This means that in the key must be all fields on which you filter AND all fields of the key you use.

To have some more control, it might be a good idea to create a function in which you read the records manually and put them in a temptable and run the entries-form with the temptable.

There is also the possibility to do some index-hinting, but this is an "if-all-else-fails"-solution that can create other problems.