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

rsaritzky · **Posted:** Thu Aug 26, 2010 11:50 pm

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:

Code: Select all

      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:

Code: Select all

IF AppliedVendLedgEntry.FINDSET(FALSE,FALSE) THEN BEGIN

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

Code: Select all

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:

Code: Select all

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:

Code: Select all

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 · **Posted:** Fri Aug 27, 2010 1:32 pm

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 · **Posted:** Fri Aug 27, 2010 4:32 pm

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 · **Posted:** Fri Aug 27, 2010 4:42 pm

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 · **Posted:** Sat Aug 28, 2010 11:46 am

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.

mibuso.com

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

Who is online