Adjust Cost Item Entries Takes Too Long

melsadek

Hi there,

We have a manufacturing and fabrication customer that heavily uses the Manufacturing functional area of NAV 2009 Classic. One of their biggest challenges right now is that as their Item Ledger (and consequently their Value Ledger) grows, their Adjust Cost Item Entries (ACIE) routine takes a long time to process completely. In recent months not even a full 24 hour run cycle is getting it done. Couple that with the fact they run their plant 24/7, and the frustration of not getting this report to run successfully starts to mount.

In another topic someone mentioned that it might be worthwhile to add a COMMIT to the adjust cost routine after each item is processed in order to atomize the transaction, so that if the routine must be killed not all the work is lost (and consequently avoid the dreadful transaction log rollback). I'm not too sure where the COMMIT should be added, but I did insert it into the Inventory Adjustment codeunit in the following function:

MakeSingleLevelAdjmt(VAR TheItem : Record Item)
LevelNo[1] := LevelNo[1] + 1;

UpDateWindow(LevelNo[1],WindowItem,WindowAdjust,WindowFWLevel,WindowEntry,0);

TheItem.SETCURRENTKEY("Low-Level Code");
IF TheItem.FINDLAST THEN
  TheItem.SETRANGE("Low-Level Code",TheItem."Low-Level Code");

WITH Item DO
  IF TheItem.FINDSET THEN BEGIN
    REPEAT
      Item := TheItem;
      GetItem("No.");
      UpDateWindow(WindowAdjmtLevel,"No.",WindowAdjust,WindowFWLevel,WindowEntry,0);

      REPEAT
        LevelExceeded := FALSE;
        AdjustItemAppliedCost;
      UNTIL NOT LevelExceeded;

      AdjustItemAvgCost;
      PostAdjmtBuf;
      UpdateItemUnitCost;

// After adjustments are made on that particular item, commit to the database
      COMMIT;
    UNTIL (TheItem.NEXT = 0) OR LevelExceeded;
  END;

I need to get this done for the client, but first I'd like to ask the community if this is the proper place to put it in, and if there are any concerns I should be aware of due to this commit.

Your feedback is appreciated.

bbrown

You say "NAV 2009 Classic" but that looks like older version code. Can you verify your NAV database version?

What have you done in terms of improving the runtime performance of Adjust Cost?

melsadek

I can confirm they're using NAV 2009 R2 objects.

I haven't touched optimization of this report yet, because it's a much lengthier endeavour. Right now I'm solely interested in making sure I have a proper COMMIT after each item is adjusted.

bbrown

You're right on the code. Not sure what I was looking at. :oops: (Downside of working with so many different systems and versions)

melsadek

So can you confirm for me about the COMMIT's location?

jreynolds

melsadek wrote:

So can you confirm for me about the COMMIT's location?

Be very careful about adding COMMITs unless you fully understand the entire process. It this case what you are suggesting is definitely not correct.

The way adjust cost is written it must run through to completion without any possibility of having the results partially committed. This is primarily because of the AppliedEntryToAdjustBuf. This is a temporary table that is written to throughout the process. Near the end of the entire process SetAppliedEntryToAdjustFromBuf (called from MakeMultiLevelAdjmt) uses this temporary table to update the “Applied Entry to Adjust” field in the Item Ledger Entry table. By committing the data after processing each item you run the risk that the process will end leaving some data committed but the "Applied Entry to Adjust" field not properly updated.

Additionally, the periodic commits will release locks on the tables and allow other users to post inventory transactions. This will cause problems with the Item Register that adjust cost creates and expects to remain valid throughout the entire process.

melsadek

Be very careful about adding COMMITs unless you fully understand the entire process. It this case what you are suggesting is definitely not correct.

The way adjust cost is written it must run through to completion without any possibility of having the results partially committed. This is primarily because of the AppliedEntryToAdjustBuf. This is a temporary table that is written to throughout the process. Near the end of the entire process SetAppliedEntryToAdjustFromBuf (called from MakeMultiLevelAdjmt) uses this temporary table to update the “Applied Entry to Adjust” field in the Item Ledger Entry table. By committing the data after processing each item you run the risk that the process will end leaving some data committed but the "Applied Entry to Adjust" field not properly updated.

Additionally, the periodic commits will release locks on the tables and allow other users to post inventory transactions. This will cause problems with the Item Register that adjust cost creates and expects to remain valid throughout the entire process.

Thanks for that explanation. So then where would you recommend I place it? Or do you not recommend it at all?

jreynolds

melsadek wrote:

Thanks for that explanation. So then where would you recommend I place it? Or do you not recommend it at all?

It's not as simple as placing a COMMIT. You need a strategy for dealing with the issues I mentioned. These are only the two issues I am aware of; but, there may be other issues as well.

SilverX

You are right keeping an eye on everything jreynolds has written. First think about the frequency ACIE is run. The job should be able to finish in a sufficient timeframe.

For your current problem you might think about creating a worklist table and running ACIE (Report 795) for each item first before increasing the frequency.

melsadek

Hi SilverX,

Do you mean to say the location of the COMMIT is correct, or do you mean to say that it is not correct and I should follow jreynolds advice?

I am thinking of creating an item dataitem as an outer loop on the ACIE report and place my commit there. That way the adjust cost routine data is commited to the DB after the ACIE report completely finishes adjusting values for a particular item. What do you think?