Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 262 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 385 SQL Performance
- 33 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
Filter on FlowField
ta5
Member Posts: 1,164
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
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
0
Comments
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
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 :-)
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
I'll let you know if I come up with a reason or different suggestion.
Peter