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

rsaritzky

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

kriki

Before starting with $ndo$dbconfig hints, did you try to rebuild its index? Maybe the index or table is badly fragmented and maybe the statistics are not up-to-date. The indexrebuild fixes both.

The fact that there is also the clustered index seek is normal. After having found the record(s) in the secondary index, SQL needs to do a bookmarklookup to get the complete record.

rsaritzky

I'll try your suggestion, but the database is newly converted/upgraded (just a few days) and I did a full rebuild/reorg before loading it onto the production server.

Thx

kriki

rsaritzky wrote:

..before loading it...

Before loading what on the production server?

If you mean: before loading the native backup on the server, you need a rebuild after it.

pdj

Your SETCURRENTKEY doesn't match the ORDER BY clause. The SELECT statement looks like it origins from a form, not C/AL. The IndexHint gets triggered by the current key (ORDER BY) but it doesn't match.
I'll suggest to re-check which statement causes the problem.