Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
Controlling VSIFT selection in BC
bbrown
Member Posts: 3,268
I'm working on tweaking a process in BC18 (On premise). Part of the process uses a Query object that is summing "Warehouse Entry" records. I've isolated the issue to the VSIFT index it uses to calc that sum.
The 2 indexes of concern are Key5 and Key8. Both shown below. The process is summing "Qty. (Base)". The query is filtered on all the fields in key8. Unfortunately, all those fields are also included in Key5. So BC uses Key5. Not surprising, this is how VSIFT has worked for years.
If I drop the query into T-SQL and run it using Key8, the result is better than a 50% improvement. I'm looking for how I could get BC to use key8. In NAV, I could simply disable the SIFT maintenance on Key5 to accomplish this. Not an option with BC.
Any thoughts?
key(Key5; "Item No.", "Bin Code", "Location Code", "Variant Code", "Unit of Measure Code", "Lot No.", "Serial No.", "Entry Type", Dedicated, "Package No.")
{
SumIndexFields = "Qty. (Base)", Cubage, Weight, Quantity;
}
key(Key8; "Location Code", "Item No.", "Variant Code", "Zone Code", "Bin Code", "Lot No.")
{
SumIndexFields = "Qty. (Base)";
}
The 2 indexes of concern are Key5 and Key8. Both shown below. The process is summing "Qty. (Base)". The query is filtered on all the fields in key8. Unfortunately, all those fields are also included in Key5. So BC uses Key5. Not surprising, this is how VSIFT has worked for years.
If I drop the query into T-SQL and run it using Key8, the result is better than a 50% improvement. I'm looking for how I could get BC to use key8. In NAV, I could simply disable the SIFT maintenance on Key5 to accomplish this. Not an option with BC.
Any thoughts?
key(Key5; "Item No.", "Bin Code", "Location Code", "Variant Code", "Unit of Measure Code", "Lot No.", "Serial No.", "Entry Type", Dedicated, "Package No.")
{
SumIndexFields = "Qty. (Base)", Cubage, Weight, Quantity;
}
key(Key8; "Location Code", "Item No.", "Variant Code", "Zone Code", "Bin Code", "Lot No.")
{
SumIndexFields = "Qty. (Base)";
}
There are no bugs - only undocumented features.
0
Answers
But what if you don't have a unique field? Well in my case, I add one, typically "myVSIFT1:Boolean" Add this to the end of the VISIFT INDEX and then add to the Flow Field as Filter WHERE "myVISIFT1" = FALSE this will Force NAV/BC to choose the VISIFT you choose.
An even better solution though, is if you are running on SQL Enterprise version.
1/ Script out the VSIFT fields you want optimized
2/ Set Maintain VISFT to False on all Indexes
3/ Put required VSIFTS back in manually through SQL
Now when NAV sends a command like SELECT SUM(Amount) FROM ILE .... The SQL Engine will find that there are indexes (VSIFTS) that already have the SUM/GROUP BY in them and it will calculate the best one to use based on statistics.