slow flowfield

Reinhard

Hi guys, I have been searching the forum but could not find a good solution to my problem:

I have a flowfield that is Sum: Quantity for Job Ledger Entry, with the filters:

Employee No.,Posting Date,Job No.,Phase Code,Payroll Control Code

All is fine. Now I add another filter: Type CONST Resource.

This field has been added to the key as well. However the flowfield now takes several minutes to calculate.

Please if you know something help me out I am at a total loss right now.

kine

SQL or Native DB?

garak

if SQL which NAV version? And how looks your customized key?

Regards

Reinhard

Database:
Native Nav 4.3

Key:
Job No., Phase Code, Employee No., Posting Date, Payroll Control Code, Type

matttrax

I don't know if this is the case, I've actually always wondered but never investigated.

The Type field is at the end of your key, but in your FlowField it is always Resource. Maybe it filters on that first? So it's not really sorted on that field, but it is trying to apply the filters it knows it can apply before you add any.

So in short, try moving type to the start of the key and see if it is faster.

Reinhard

Matttrax: I investigated what you suggested, but unfortunately it didn't speed it up.

Also, to clarify, if I remove the Type constraint from the flowfield, it is almost instantaneous. If I drill down on the flowfield to the ledger entries and select the correct key, and then filter on Type it is also really fast.

matttrax

Perhaps running client monitor could tell you why it's taking so long.

Write a little script to calculate if for one record. Compare the records that client monitor is going through to the records you think it should have to search to calculate the correct value.

kapamarou

To test your problem do the following. Create a blank form that shows Only your flowfield. If it is fast then maybe the field you added affects another flowfield's calculation (I think this happens because Nav uses the first key in order that satisfies the constraints.). You'll need to use the client monitor in order to see which key is used and how much time it takes.

kriki

First and for all: on which fields are you filtering?
Second : what is the type of the filter? (1 value, a range of values, ...)

I would restore the original key.
I would create a new one with the fields of the filters (the most selective I would place first).
I would NOT maintain the SQLIndex, and maintain ONLY the lowest level of the SIFTIndex.

[Topic moved from 'NAV/Navision' forum to 'SQL Performance' forum]

Reinhard

By creating a form with a button that just did CALCFIELDS and running the client monitor I saw that it was filtering on Type first. I recreated the key like this:
Employee No.,Posting Date,Job No.,Phase Code,Payroll Control Code,Type

And this reduced the time from 74 seconds to about .4

Thank you for the useful suggestions.

Belias

reinhard:

Database:
Native Nav 4.3

kriki wrote:

First and for all: on which fields are you filtering?
Second : what is the type of the filter? (1 value, a range of values, ...)

I would restore the original key.
I would create a new one with the fields of the filters (the most selective I would place first).
I would NOT maintain the SQLIndex, and maintain ONLY the lowest level of the SIFTIndex.

[Topic moved from 'NAV/Navision' forum to 'SQL Performance' forum]

Hi, first of all, i think kriki didn't noticed that the database is Native ...anyway, i saw that Reinhard put the most selective field at the beginning...shouldn't be the less selective instead for a native DB?or am i wrong?

David_Singleton

Belias wrote:

...anyway, i saw that Reinhard put the most selective field at the beginning...shouldn't be the less selective instead for a native DB?or am i wrong?

With Native database, the order of the fields in the key on a flow field only matter (from a performance point of view) if a range is involved. This is why we often see "posting date" as the last element of a key, since posting date generally filters on a range of dates.

So basically if you have Type and No. int he key, and you can use SETRANGE on both, then it does not matter the order of the keys.

But Alain should move this to the correct forum, because this does not apply to SQL.

kriki

Belias wrote:

Hi, first of all, i think kriki didn't noticed that the database is Native

:oops:

[Topic moved from 'SQL Performance' forum to 'NAV/Navision' forum]