mibuso.com

Microsoft Business Solutions online community
It is currently Wed Jul 30, 2014 11:05 am

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 5 posts ] 
Author Message
 Post subject: How to set up $ndo$dbconfig hint to get the desired result
PostPosted: Thu Aug 26, 2010 11:50 pm 
Offline

Joined: Wed Jun 20, 2001 7:01 am
Posts: 252
Location: Los Angeles, CA
Country: United States (us)
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


Top
 Profile E-mail  
 
 Post subject: Re: How to set up $ndo$dbconfig hint to get the desired resu
PostPosted: Fri Aug 27, 2010 1:32 pm 
Online
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7683
Location: Milan
Country: Italy (it)
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.

_________________
Regards,Alain Krikilion
Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting

»»» Mark your calendar: NAV TechDays 2014 - 20 & 21 November 2014 ««« Visit the conference website: http://www.navtechdays.com


Top
 Profile  
 
 Post subject: Re: How to set up $ndo$dbconfig hint to get the desired resu
PostPosted: Fri Aug 27, 2010 4:32 pm 
Offline

Joined: Wed Jun 20, 2001 7:01 am
Posts: 252
Location: Los Angeles, CA
Country: United States (us)
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

_________________
Ron


Top
 Profile E-mail  
 
 Post subject: Re: How to set up $ndo$dbconfig hint to get the desired resu
PostPosted: Fri Aug 27, 2010 4:42 pm 
Online
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7683
Location: Milan
Country: Italy (it)
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.

_________________
Regards,Alain Krikilion
Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting

»»» Mark your calendar: NAV TechDays 2014 - 20 & 21 November 2014 ««« Visit the conference website: http://www.navtechdays.com


Top
 Profile  
 
 Post subject: Re: How to set up $ndo$dbconfig hint to get the desired resu
PostPosted: Sat Aug 28, 2010 11:46 am 
Offline

Joined: Wed Nov 24, 1999 8:01 am
Posts: 578
Location: Aalborg,Denmark
Country: Denmark (dk)
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.

_________________
Regards
Peter


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: