Adjust Item Cost Problem

vikram7_dabas

I m using NAV 3.7 DB in SQL,DB having more than 50,000Items. When I run this Batch,it runs for 3-4 Days and after that My system hang.At Last I have to EndTask the Application.

garak

on sql 2000 or 2005. Do you have maint. jobs like rebuildindex and updatestats?
Are this so slow on NAS or also if u run it with the client?
Which option are selected? Item or Item,Location,Variant?
How often you run the adjustment?

generic

Please read this post.

viewtopic.php?f=16&t=36378


The funny part is that NAV MVP religiously defend the NAV product, but when they actually see posts like this.
I don't see them provide a solution.

You can try and search for some performance tunning threads, but I'm guessing you probably have.

smkolsoft

One of the reason for Adjust Cost entry going into infinite loop is
the presence of Blank item code in Item master.
I have encountered this problem in ver 4.0.

Please check your Item master.


Thanks

kriki

[Merged posts from another topic and closed the other topic]
http://www.mibuso.com/forum/viewtopic.php?t=36883

bstoyano

smkolsoft,

I have the same problem with Nav 4, sp1. Can you please tell me where is this Item master?

Thanks a lot in advance.

hari_hh

Hi! I would like to understand the issue further.
1.0 Is the ACIE batch job going into indefinite loop?
2.0 Is Manufacturing covered?
3.0 All relevant production orders status changed
4.0 In one of my earlier problems, the solution delivered was FG itself was consumed in BOM in same PO. so you could check it.

In one of my earlier projects, 3.7 batch job was, as reported by you, taking weeks. So what we did in a stand-alone was (this is NOT for MVPs), take the batch job of 4.0 and it worked! Got result in under 4 hours!! \:D/ \:D/

bstoyano

Hi,

hari_hh wrote:

Hi! I would like to understand the issue further.
1.0 Is the ACIE batch job going into indefinite loop?
2.0 Is Manufacturing covered?
3.0 All relevant production orders status changed
4.0 In one of my earlier problems, the solution delivered was FG itself was consumed in BOM in same PO. so you could check it.

In one of my earlier projects, 3.7 batch job was, as reported by you, taking weeks. So what we did in a stand-alone was (this is NOT for MVPs), take the batch job of 4.0 and it worked! Got result in under 4 hours!! \:D/ \:D/

1. about the infinite loop - I started it on friday and today NAV was not responding. I couldn't see any information as to which entry or item it is processing. This looks like an infinite loop - I don't know how to check whether it is into an infinite loop
2. Yes, our client is using Manufacturing.
3. I don't understand exactly this question. As in any production company there are production orders that are firm planned, planned, finished ... i.e. of any status.
4. What is FG?

And one more thing that might be important - the customer had unfinished/uninvoiced sale, purchase and production orders from 2006. Last month they finished and invoiced a large part of them. As a result now for some items there are item ledger entries with Posting date in 2006 that have a check in the column "Applied entries to adjust".

One of the developpers here suggested that some sort of transaction file is getting too big as the Adjust Cost does its job, and that's why it may take days, weeks or even months to adjust the affected entries.
Another fact that you may be interested in - there are about 43 000 items and almost all of them have Costing method=Average.

Thanks in advance

hari_hh

about the infinite loop - I started it on friday and today NAV was not responding. I couldn't see any information as to which entry or item it is processing. This looks like an infinite loop - I don't know how to check whether it is into an infinite loop

Could STILL be in running mode; week / 10days running is not unknown especially in 3.7
Better run it on a stand-alone DB and check
2. Yes, our client is using Manufacturing.
OK3. I don't understand exactly this question. As in any production company there are production orders that are firm planned, planned, finished ... i.e. of any status.

All finished production orders are to be closed (status changed to finish)
4. What is FG?

Finished Goods - could be sub-assembly too. What I meant to say was an output from an production order
is itself consumed as part of BOM for the same production order. Did this happen? You could write a query
to find this out.

And one more thing that might be important - the customer had unfinished/uninvoiced sale, purchase and production orders from 2006. Last month they finished and invoiced a large part of them. As a result now for some items there are item ledger entries with Posting date in 2006 that have a check in the column "Applied entries to adjust".

Per se, I don't think this is a problem.


Year-end activities have not happened in the past?
Period of posting in G/L still covers 2006?

Since when the batch job has not been run?

One of the developpers here suggested that some sort of transaction file is getting too big as the Adjust Cost does its job, and that's why it may take days, weeks or even months to adjust the affected entries.
Another fact that you may be interested in - there are about 43 000 items and almost all of them have Costing method=Average.

Again, per se it is not an issue.
All purchases booked?

bstoyano

Hi Hari_hh,

Thanks for your quick reply.
10 days is a huge period of time. But I shall try in a stand-alone dbase as you suggest.
About the status of production orders - what is wrong if there are unfinished orders?
About using products as components for the products themselves - definitely NO.

About year-end activities - I checked in Form 100 Accounting periods - none is checked as closed (if that is what you asked). Posting in GL Setup is restricted from 01.07.2009 until 30.09.2009. But I may not change this because Customer does not want account balance figures for previous periods to change.

The last time ACIE was run was in the beginning of August, i.e. a month ago.

Usually they don't book quantities for purchases.

vikram7_dabas

Dear concern
My problem not solved I have tried all given solutions but still same problem....................

Deep

This depends on a number of factors like, whether there is any blank record in item table due to which it ends in an infinite loop, the frequency of running AICE, transactions since the last run, hardware, and many more.
We also encountered this problem.
We just added a few lines of code for filtering items by category.
This gave results and the process ran.
We manually changed the item categories and ran the process for entire categories, and it was successful.

On a copy of the database (copy prior to AICE run), we tried tweaking the code for commiting the AICE per item. We ran the process for a range of items, and then an Item category, and compared the value entries over excel (entried generated after using COMMIT and that of the original code. There was no difference and the results were exactly same. I understand from our technical experts that commiting MUST not be used in code frequently, as it may result in transaction inconsistency.
Since I am not a technical resource, I would also like to hear some ideas from our experts in this forum over the use of "commit" on the AICE code.

klavin

I had found personally while running the Adjust Cost the longer it goes, the slower it starts to run and it has a domino effect... whether it is based off that it isn't committed until the report is run, where it is storing the temp information locally, how resources are used or what I am not sure.

What I do know, is I experimented with having a Codeunit fire off the ACIE report


This is only an example, I want to get across

OnRun()
//loop through and grab the first 500 item numbers, use NEXT(500), whatever your method of choice...
Item.FINDFIRST;
WHILE (condition) DO BEGIN  //Repeat Loop, on the Item if you wish and increment i to 500, whichever.
   StartNo := Item."No.";
   Item.NEXT(500);
   EndNo := Item."No.";
   TempString := StartNo + '..' + EndNo;
   RunAdjustCost(TempString);
   IF StartNo = EndNo THEN   //we're at the end
      (condition) := FALSE;
END;

RunAdjustCost(FilterString : Text[50])  //Function in Codeunit all variables below are LOCALS to this function.  This is important.

CLEAR(AdjustCost);             //LOCAL - MUST BE LOCAL.
AdjustCost.SetItemFilter(FilterString);   //Function inside of the Report to filter on Item Number
AdjustCost.USEREQUESTFORM(FALSE);
AdjustCost.RUN;
COMMIT;                           //Commit this set, move to the next set

Report 795 ( ACIE )

SetItemFilter(PassedFilter : Text[250])
ItemNoFilter := PassedFilter;

All I do know is that it ran significantly faster. We then set it up to run with the Job Queue to run nightly and it worked great on a nightly basis. Try it out, may not be a complete solution for you but something to start getting it along. At least every 500 it is committing.

Give it a shot, let me know if there is any result; I know it worked wonders for us on a large record set.

Kevin

bstoyano

Thank you all guys,

I asked our developer to add an Item Filter in the ACIE Form so that I can limit the range of Items to be processed at one time. In NAV 5.0 you already have such a field. I think it works fine, just have to split the item nomenclature in several ranges and run ACIE several consecutive nights

davmac1

I had to troubleshoot a problem where it was taking two hours to post a single item with adjust cost turned on.
The problem in this case was receiving a serialized item using average cost with a quantity of 5,000.
What NAV was doing was processing each serial number 5000 times * 3 - NAV likes to repeat itself).
So it was running thru the code 5000 * 5000 * 3 times = 75 million times!
Each serial number gets its own item ledger entry and value entry.

Solutions:
1) handle serial numbers outside of standard NAV OR
2) don't receive serial numbers - turn them on at shipping OR
3) don't use average cost OR
4) rewrite the costing routines

The customer elected to go with option 2.

Your problem is probably similar - a large quantity of transactions for the same item number.

bstoyano

davmak,
i must regretfully confess that you may prove right . For the ranges that I pre-defined it works fine for all except one. Now I will focus on in. I have already noticed one particular item that Adjust Cost literally cycles over and over. Maybe this item causes the problem. I will investigate further ...

bstoyano

No success. We didn't manage to push the ACIE through the data. So briefly we created new items to replace the old ones, made some "manual" adjustments on the Item Ledger entries of the guilty items (STRONGLY NOT RECOMMENDED UNLESS ABSOLUTELY INEVITABLE) and ACIE passed in 40 minutes.

Eugene

the problem is in very lengthy transaction. To solve the problem we added COMMIT after each item is adjusted

in codeunit 5895 (Inventory Adjustment) we modified two functions MakeSingleLevelAdjmt and MakeWIPAdjmt by adding COMMIT:


in MakeSingleLevelAdjmt:

IF TheItem.FINDSET THEN BEGIN
  REPEAT
    ...
    UpdateItemUnitCost;
    COMMIT;

in MakeWIPAdjmt:

WITH ProdOrderLine DO
  IF TheProdOrderLine.FIND('-') THEN
    REPEAT
      ProdOrderLine := TheProdOrderLine;
      CalcMfgCostAndUpdOutputEntries(ProdOrderLine);
      COMMIT;

Eugene

and even after shortening the transactions we put this job for the night.
if u plan to run this process at day (when there is a load from other concurrent users) you should also add the re-locking after commit, something like:

SLEEP(1000); //to allow other users to intervene with their tasks
T32.LOCKTABLE;
IF T32.FINDLAST THEN;
T5802.LOCKTABLE;
IF T5802.FINDLAST THEN;

adjusting one item takes from 1 to 20 minutes here so it is a constantly running process every night. If you do not run it for a month or so then it may take a week to adjust all items up to date. So you clearly need an abort mechanism when your task runs out of time, something after COMMIT like IF TimeIsUp THEN AbortCalculations and EXIT;

Eugene

one more thing that will help alot:
In codeunit 5895 change all FIND('-') with FINDSET (some FIND('-') can be changed with FINDFIRST)
look how this codeunit is optimized in NAV5.0

bstoyano

Thank you very much for the explanations and for the code. Next time we have similar difficulties I shall try to persuade some of the developpers to embed this code for me. I hope I don't come accross such trouble any more.