Controlling VSIFT selection in BC

bbrown
bbrown Member Posts: 3,268
in SQL Performance
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)";
}




There are no bugs - only undocumented features.

Answers

  • bbrown
    bbrown Member Posts: 3,268
    10 seconds after clicking send I see my problem. Set a filter on "Zone Code"
    There are no bugs - only undocumented features.
  • David_Singleton
    David_Singleton Member Posts: 5,479
    There are two more tricks that can help in this case. The issue (which I am sure you know) is that NAV complier goes through VSIFTs sequentially and uses the first one that has all the require fields, and then stops searching. So to use a VISIFT later in the list you need to filter on a column that is not in any of the higher VISIFTs. In your case you found Zone Code, so all good.

    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.
    David Singleton

Categories