How to set up $ndo$dbconfig hint to get the desired result

Hi,

In NAV2009 SQL on SQL2005, I'm having a performance problem in the Vendor Ledger "Apply Entries" screen. When the user clicks "Apply Entries" from the Vendor Ledger Entry card, it takes nearly 2 minutes for the "apply" screen to be fully populated and unlocked for entry.

I've tried to read and understand the information in the Performance Guide and here on MIBUSO about index hints and the $ndo$dbconfig table. I've also run the Client Monitor to isolate the problem area. So here's what I've done so far.

1. I've isolated the problem statement, which is when function CalcApplnRemainingAmount calls function HandlChosenEntries. In CalcApplnRemainingAmount, the filters set are:

      AppliedVendLedgEntry.SETCURRENTKEY("Vendor No.",Open,Positive);
      AppliedVendLedgEntry.SETRANGE("Vendor No.","Vendor No.");
      AppliedVendLedgEntry.SETRANGE(Open,TRUE);
      AppliedVendLedgEntry.SETRANGE("Applies-to ID",VendEntryApplID);

      IF ApplyingVendLedgEntry."Entry No." <> 0 THEN BEGIN
        VendLedgEntry.CALCFIELDS("Remaining Amount");
        AppliedVendLedgEntry.SETFILTER("Entry No.",'<>%1',VendLedgEntry."Entry No.");
      END;

Then HandlChosenEntries executes the FIND:

IF AppliedVendLedgEntry.FINDSET(FALSE,FALSE) THEN BEGIN

From the Client Monitor, the SQL Statement being executed is:

SELECT  * FROM "XXXX$Vendor Ledger Entry" WITH (READUNCOMMITTED)  WHERE (("Entry No_"<>6996419)) AND (("Vendor No_"='V09218')) AND (("Open"=1)) AND  "Entry No_"<6996418 ORDER BY "Entry No_" DESC

The Execution plan shows:

Sort[2,1];Nested Loops[3,2];Index Seek($4)[5,3];Clustered Index Seek(MCBT$Vendor Ledger Entry$0)[7,3]

and the SQL Index shows:

Vendor No.,Open,Positive,Due Date,Currency Code,Entry No.

I'm not exactly sure how to read the execution plan - it seems to indicate the information from the $ndo$dbconfig entry I've made (see below) but it also shows that it is using the clustered index (Entry No.) instead of index 4 which is "Vendor No.,Open,Positive,Due Date,Currency Code,Entry No.". When both indexes are shown, I'm not sure how to interpret it.

So I was trying to create an entry in $ndo$dbconfig to override this. I have tried serveral entries. The most recent entry is the following:

IndexHint=Yes;Company="XXXX";Table="Vendor Ledger Entry";Key="Vendor No.","Open","Positive";Index="4";

I have tried other variations, including Recompile Hints, etc. But the query never appears to change.

Since we have nearly 2 million records in the Vendor Ledger Entry table, this is problematic.

Any suggestions on if/how to correctly override the index selection?

Thanks

Ron