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
- 266 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 384 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
flowfields and SQL-Server (doesn't use the index)
recall
Member Posts: 36
Hello,
I have a procedure that takes on Navision native DB about 1 Minute but on SQL-Server more than 2 hours :roll: .
What takes the most of the time is a setrange() command on a flowfield.
This flowfield calculates its value from another table. On this (other/foreign) table I just created an index so that it should speed up, but it's not
Navision shows a dialog that the (foreign) table is searched (with a speed of 10 datasets per second ](*,) ).
So what can I do ?
Thanks.
I have a procedure that takes on Navision native DB about 1 Minute but on SQL-Server more than 2 hours :roll: .
What takes the most of the time is a setrange() command on a flowfield.
This flowfield calculates its value from another table. On this (other/foreign) table I just created an index so that it should speed up, but it's not
Navision shows a dialog that the (foreign) table is searched (with a speed of 10 datasets per second ](*,) ).
So what can I do ?
Thanks.
0
Comments
1. Use a SETCURRENTKEY statement on the main table.
2. Use SETRANGE/SETFILTER on every field in the SETCURRENTKEY statement. Set them in order and do not skip any fields.
3. Review the flowfilter definitions and the sumindexfields and related keys of the secondary table. Navision will use the first key on the secondary table that has the sumindexfield defined and all field that are being filtered by the flowfilter definition. Adjust the flowfilter and secondary table to control this.
Use the available performance monitoring tools to see what is actually happening and to help you make adjustments.