Slow Drill Down on Inventory-field

paurola

I am trying to solve why drilling down into Inventory-field on Item Card is very slow in our customers NAV 4.03 Update 6 environment

I get strange results from Client Monitor. The highest Elapsed value is on a line that is executed from Codeunit 1 when form 38 is opened. Elapsed value is 40031 - which is over 40 seconds!

Function Name:
FIND/NEXT

Source Function/Trigger:
AutoFormatTranslate(AutoFormatType,AutoFormatExpr)

Sql Statement:
SELECT * FROM "MYCOMPANY$Item Ledger Entry" WITH (INDEX("MYCOMPANY$Item Ledger Entry$0")) WHERE (("Item No_"='364SC9045G')) AND "Entry No_"<604744 ORDER BY "Entry No_" DESC

Any ideas what Codeunit 1 is doing here and can this be avoided somehow?

kriki

You need to remove the indexhinting from SQL.
Check this for more info on how to disable it : http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/archive/2007/09/19/index-hinting-in-platform-update-for-microsoft-dynamics-nav-4-0-sp3-kb940718.aspx

paurola

I am aware of this Index Hinting problem or "method" and actually that is the reason why I have updated this particular database to Update 6. I have studied index hinting a lot but I am somewhat confused with it. If I should discover a call to the NAV database using a bad key or a bad execution plan, then it would be clear that something should be done in order to get SQL Server to use a better key (index).

The index hinted in this SQL statement is $0. This should be the clustered index which I think is a good index for this purpose. If you are searching records in Item Ledger Entry by Item No. and Entry No., I find it hard to believe that there is a significantly faster key - Correct me if I'm wrong!

//+EDIT
As I think of this, I start to think that a key with both fields on it could be much faster. So perhaps removing the hint for $0 might help after all.
//-EDIT

So, why is this form sending this very slow query to the database?
When I drill down into Inventory-field on the Item card form, the "Item Ledger Entry"-list opens and shows records that apply to the item on Item card. I suppose that when this is done, NAV Client creates a query to the database and that query has the (("Item No_" = 'XXXX')) part of where-statement for finding the records that sum up the value for the Inventory-flow field and the Entry No_ part could be for positioning the record pointer on the form so that the latest viewed are shown. I am going to change the SourceTablePlacement property of the list form to test the how that affects the SQL Statement.

Some lines after the first slow query there is another long lasting query which is also created from AutoFormatTranslate function in Codeunit 1. This line has an SQL Statement "FETCH 35" - again I suppose that this has to do with fetching the rows on the form.

Off topic: I have bought an excellent book by Jörg A. Stryk called "The NAV/SQL Performance field guide", which is of great help on understanding the number of issues there are to be taken into account when optimizing a "problem database" or just a huge database.

xavigepe

Hi. I've found the same problem. I've tried to turn off index hinting in Item Ledger Entry table, but I'm not sure if it works (cause I don't have slow performance always) and even if I have disabled it correctly?. Does anyone know if I have to restart SQL server after creating table [$ndo$dbconfig] to take into account changes done?.

Thanks

Marije_Brummel

No. This is not nesesairy.

The dbconfig table is loaded into the client when starting up.

If the performance is slow every now and then, you might need to hint a specific index.