Manufacturing - Calculate Low-level code extremely slow

lzr

We have a problem where the calculate low-level code takes over 3 days to finish. We are using about 55k items and 6 levels. Anyone have any similar experience or any advice on what to do? Perhaps there is a fix for this?

We are using NAV 2009 SP1 with classic client and SQL Server 2008

ara3n

I suggest to sql profiler and find which process take the longest.

Beside tuning indexes.

You will have to change back the from findset to find('-') since number of records returned is higher than Setup.

You could also build single instance CU where you can store records and don't have to go sql again to retrieve them.

lzr

Thanx ara3n,

I have changed it to find('-') and changed SQLIndex. However I notice that SQL server load is only 5-10%. I am thinking of rewriting the code and using temporary tables for Item, Production BOM Header and Production BOM Line. Read out all data, calculate low level code and the write back the data. How much gain do you think this will give?

lzr

I have been busy with other projects but now I am onto this problem again. I copied the calc. low level codeunit from version 4.0 and ran it on my data in NAV 2009.
It only takes 10 minutes to run instead of 4 days (!)

But in 4.0 the below code doesn't exist:

ELSE
REPEAT
// handle items which are part of un-certified BOMs
IF ProdBOMHeader.GET(ProdBOMLine."Production BOM No.") THEN
IF ProdBOMHeader.Status <> ProdBOMHeader.Status::Certified THEN
IF ProdBOMHeader.GET(Item."Production BOM No.") THEN
CalcLevelsForBOM(ProdBOMHeader);
UNTIL ProdBOMLine.NEXT = 0;


Below you see the original code from calc. low level code in 2009:

NoofItems := 0;
IF NOT HideDialogs THEN
  IF NOT CONFIRM(Text000,FALSE) THEN
    EXIT;
Window.OPEN(
  '#1################## \\' +
  Text001);
Window.UPDATE(1,Text002);

Item.LOCKTABLE;
Item.MODIFYALL("Low-Level Code",0);
ProdBOMHeader.LOCKTABLE;
ProdBOMHeader.MODIFYALL("Low-Level Code",0);

ProdBOMLine.SETCURRENTKEY(Type,"No.");
IF Item.FINDSET THEN
  REPEAT
    Window.UPDATE(2,Item."No.");
    Window.UPDATE(3,ROUND(NoofItems / Item.COUNT * 10000,1));
    ProdBOMLine.SETRANGE("No.",Item."No.");
    ProdBOMLine.SETRANGE(Type,ProdBOMLine.Type::Item);
    IF ProdBOMLine.ISEMPTY THEN BEGIN
      // handle items which are not part of any BOMs
      IF ProdBOMHeader.GET(Item."Production BOM No.") THEN
        CalcLevelsForBOM(ProdBOMHeader);
    END ELSE
      REPEAT
        // handle items which are part of un-certified BOMs
        IF ProdBOMHeader.GET(ProdBOMLine."Production BOM No.") THEN
          IF ProdBOMHeader.Status <> ProdBOMHeader.Status::Certified THEN
            IF ProdBOMHeader.GET(Item."Production BOM No.") THEN
              CalcLevelsForBOM(ProdBOMHeader);
      UNTIL ProdBOMLine.NEXT = 0;
    NoofItems := NoofItems + 1;
  UNTIL Item.NEXT = 0;
NoofItems := 0;
Window.UPDATE(1,Text003);
ProdBOMHeader.RESET;
ProdBOMHeader.SETRANGE("Low-Level Code",0);
IF ProdBOMHeader.FINDSET(TRUE) THEN
  REPEAT
    Window.UPDATE(2,ProdBOMHeader."No.");
    Window.UPDATE(3,ROUND(NoofItems / ProdBOMHeader.COUNT * 10000,1));
    CalcLevelsForBOM(ProdBOMHeader);
    NoofItems := NoofItems + 1;
  UNTIL ProdBOMHeader.NEXT=0;

By just uncomment the section about uncertified BOMS I got it down to 10 minutes. Anyone care to explain why this code is needed in 2009 and not in 4.0? As I see it the last section updates all BOMS not previously updated and should take care of those components that belong to an item which is not certified?