Huge Performance Issue with Report 795 - NAV 5.0

headley27

A poster described issues with Report 795 (Adjust Cost - Item entries) after an upgrade from 4.0 to SP3.

http://www.mibuso.com/forum/viewtopic.php?t=18943&highlight=795

I am experiencing a huge performance issue after converting from 3.7 to 5.0.

The report used to finish in about 20 minutes but I'll be lucky if this finishes in 20 hours by the looks of it.
I have 40,000 items, 4 locations with SKUs, we use average costing and do some light manufacturing. The database is SQL 2000.

I will try the client monitor mentioned in the post that I have referenced but can someone shed some light on what may be happening?

Thank you very much.

ara3n

Has it ever made it to the end?
I've seen it go through endless loop for items that have BOM.

In order to run it and make sure that it finishes.

You should allow it to filter by Item no.


http://www.mibuso.com/forum/viewtopic.php?t=18943&highlight=


As about the performance, use the client monitor to find why it's taking the so long.


oops this is 5.0.

It already has filtering. :oops:
Never mind.

headley27

Hi ara3n.

The last time the report completed 100% was last Friday using 3.7.

We just upgraded to 5.0 and I've been able to get through 15% of the item list, but it took hours.

I'm not hitting an endless loop, it is just horribly slow.

Also, about the Client Monitor...where do I find it?
I have searched Mibuso.Com, DynamicsUser.Net and the 5.0 Installation CD without success.

*** EDIT ***
Please ignore my last question.
I was looking externally for the Client Monitor.
Now I see it. Tools > Client Monitor.

Thanks again.

headley27

I am still having difficulty with this report. ](*,)

I ran the client monitor on a single part number that has approx. 600 item ledger entries. I don't have an exact count but the largest Client Monitor Entry No was over 100,000. Also, the runtime for this 1 item was 1.5 minutes.

Should this be?

There are just so many hits on the database I am not sure what to look for. There are over 200 queries to the database per second (over 39,000 in total). Over 16,000 are SELECT TOP 1 NULL or SELECT TOP 1 * queries against the Item Application Entry table.

Also, I did notice that the report is taking entries into consideration right back to the first day we started using Navision, over 3 years ago.
I made sure that the Adjust Cost - Item Entries Report was run before the upgrade to 5.0. I did the same with the Post Cost to G/L report.
Many of these items would have been purchased, received, shipped and invoiced in full many times over since 2004. We do have replacement stock on our shelves currently that may or may not be fully invoiced but it is looking back over 3 years?

Should this be?

Any suggestions would be greatly appreciated.

ara3n

It should not look at entries dated back to 2004.
After you run the adjust cost for that item. How long does it take to run again for the same item?

ara3n

Can you look at item ledger entries in 2004 and see if "Applied Entry to Adjust" is set to "No"?

All the historical entries should be set to No..
When adjust cost routine runs it marks them to No so that it doesn't look at them again.

Alex_Chow

One way you can make this process more efficient is to use the Inventory Periods and run adjust cost per period.

Marije_Brummel

Can you please post the complete

SELECT TOP 1 NULL

Statement?

My guess it that with the tuning of the 5.0 project a covering index has been slipped.

Thanks.

headley27

ara3n wrote:

Can you look at item ledger entries in 2004 and see if "Applied Entry to Adjust" is set to "No"?

All the historical entries should be set to No..
When adjust cost routine runs it marks them to No so that it doesn't look at them again.

Thanks ara3n. My fear was that I wouldn't be able to keep my costs updated correctly due to the long runtime. I noticed the behavior that you mentioned earlier today after posting my message.

It may take me a bit to get through this report but I will eventually get there. I will simply have to run the 'initial pass' in stages, over multiple days.

You are correct, when an item is completed, the subsequent pass ignores the item. Thanks again.

headley27

Alex Chow wrote:

One way you can make this process more efficient is to use the Inventory Periods and run adjust cost per period.

Thanks Alex. I saw this table when looking through the Client Monitor data. I wish I could remember the message I got when trying to close Inventory Periods in a test database. I believe it failed stating I had open entries and I needed to either run the Adjust Cost - Item entries report, the Post Cost to G/L report, or something very similar.

Once I make it through the process I am going to look into closing the older Inventory Periods. It makes a lot of sense to me that this should be done. Thanks again.

headley27

Mark Brummel wrote:

Can you please post the complete

SELECT TOP 1 NULL

Statement?

My guess it that with the tuning of the 5.0 project a covering index has been slipped.

Thanks.

Hi Mark. I'm not sure what 'a covering index has been slipped' means but here are examples of the SELECT TOP 1 NULL Statements:

SELECT TOP 1 NULL FROM "CompanyName$Item Application Entry" WITH (UPDLOCK) WHERE (("Transferred-from Entry No_"=44677))

SELECT TOP 1 NULL FROM "CompanyName$Item Application Entry" WITH (UPDLOCK) WHERE (("Outbound Item Entry No_"=44677)) AND (("Item Ledger Entry No_"<>44677)) AND (("Cost Application"=1)) AND (("Transferred-from Entry No_"=0))

SELECT TOP 1 NULL FROM "CompanyName$Item Application Entry" WITH (UPDLOCK) WHERE (("Output Completely Invd_ Date"={ts '1753-01-01 00:00:00.000'})) AND (("Item Ledger Entry No_"=44677))

Thank you.

Marije_Brummel

My guess was wrong.

The queries you send are not on fields in the W1 version.

Try to create indexes which include all fields in the where clause.

SQL can read the index instead of the clustered index and will be much faster.

Put the most selective field in front.

Good luck.

headley27

Mark Brummel wrote:

Try to create indexes which include all fields in the where clause.

SQL can read the index instead of the clustered index and will be much faster.

Put the most selective field in front.

Thanks for your advice. I'll give it a try.

headley27

I am following up with this to say that splitting up the item list into small manageable chunks took a few days but it worked.

As mentioned earlier, when an item is completed (tagged with 'Cost is Adjusted'), subsequent passes ignore the item.

Now the full report now runs in under 5 minutes.

Thanks for your help and your suggestions!!

\:D/

ara3n

that's good to hear.