Filter on FlowField

ta5

Hi
I'm working with a quite big solution, the overall performance is very good, everyone is happy. However, I've been asked a few times, whether and how it would be possible to speed up filtering on flow fields, for example on "Balance"-Field on Table customers.
Thanks in advance
Thomas

Marije_Brummel

Which version of NAV are you running? This is easier to achieve with NAV2013

ta5

Hi Mark
Its 2009. If you don't mind, can you describe a possible solution for both versions? At the moment I only need a general approach (idea), so no need for scripts, etc.
Thanks in advance
Thomas

pdj

In older versions of NAV (before the indexed views) each Key had a property called SIFTLevelToMaintain.
That made it possible to decide exactly which levels of a Key should have maintained an accumulated value.
This is gone now, and NAV only creates accumulated values for the leafs of the index.

To get the same flexibility as before you need to create a new Key for each level you wish to have.
I would expect you can optimize filtering on Cust.Balance by ensuring you have a Key in the Detailed Cust. Ledg. Entry table with only the Customer No. field, and of course Amount as SumIndexField (assuming you don't set Dimension Filter or Currency Filter). That should optimize it a lot, as it currently is using this Key: "Customer No.,Initial Entry Due Date,Posting Date,Currency Code" - which has a LOT of index entries. You might need to place the index before the original Key. I think NAV just uses the first SIFT index it can.
(To reduce the overhead by maintaining the actual index, you could mark it as MaintainSQLIndex=No)

Please let me know if it works :-)

ta5

Hi Peter
Thanks a lot for your answer. I have tested it, but with no considerable gain. I guess I have to use sql server profiler to see what sql server does, means see the impact on the query plan.
Regards
Thomas

pdj

Strange... :-k
I'll let you know if I come up with a reason or different suggestion.