Code Optimization question for CU 5895 Inventory Adjustment

KYDutchie

Hello everybody,

I attended the Directions 2007 pre conference training about SQL optimization by Hynek Muhlbacher, and now I am asked to help out with a serious performance issue. I also attended the Changing Alphabets session, by Mark Brummel, and both classes were great! =D>

The adjust cost - Item Entries (Report 795) is running for multiple days at our customer site. I was asked to look at CU5895 and see what I could do. Because of the size of the the Item Ledger Entry table, we want to change the keys only as a last resort. (Which I think needs to be performed anyway)

The customer has recently upgraded from version 3.1 to version 4.0SP3. As a first step I changed all the FIND('-'),FIND('+') into FINDFIRST,FINDLAST and FINDSET by taking version 5.0 as a reference. However there is one trigger that was not changed in both 4.0 SP3 and 5.0. It is trigger CalcTransEntryOldCost.

I thought I have a go at it [-o<

This is the original Trigger:

CLEAR(CostElementBuf);
WITH CostElementBuf DO BEGIN
  TransValueEntry2 := TransValueEntry;
  TransValueEntry.SETCURRENTKEY("Item Ledger Entry No.","Entry Type");
  TransValueEntry.SETRANGE("Item Ledger Entry No.",ItemLedgEntryNo);
  TransValueEntry.SETRANGE("Entry Type",TransValueEntry."Entry Type"::"Direct Cost");
  TransValueEntry.FIND('+');
  REPEAT
    IF TransValueEntry."Item Charge No." = '' THEN BEGIN
      IF AdjmtBuf.GET(TransValueEntry."Entry No.") THEN
        TransValueEntry.AddCost(AdjmtBuf);
      "Actual Cost" := "Actual Cost" + TransValueEntry."Cost Amount (Actual)";
      "Actual Cost (ACY)" := "Actual Cost (ACY)" + TransValueEntry."Cost Amount (Actual) (ACY)";
      TransValueEntry2 := TransValueEntry;
    END;
  UNTIL TransValueEntry.NEXT(-1) = 0;
  TransValueEntry := TransValueEntry2;
END;

And I changed it to:

CLEAR(CostElementBuf);
WITH CostElementBuf DO BEGIN
  TransValueEntry2 := TransValueEntry;
  TransValueEntry.SETCURRENTKEY("Item Ledger Entry No.","Entry Type");
  TransValueEntry.ASCENDING(FALSE);    //Added this statement to try to increase performance
  TransValueEntry.SETRANGE("Item Ledger Entry No.",ItemLedgEntryNo);
  TransValueEntry.SETRANGE("Entry Type",TransValueEntry."Entry Type"::"Direct Cost");
  //TransValueEntry.FIND('+');    //Replaced this statement with next statement
  TransValueEntry.FINDSET;
  REPEAT
    IF TransValueEntry."Item Charge No." = '' THEN BEGIN
      IF AdjmtBuf.GET(TransValueEntry."Entry No.") THEN
        TransValueEntry.AddCost(AdjmtBuf);
      "Actual Cost" := "Actual Cost" + TransValueEntry."Cost Amount (Actual)";
      "Actual Cost (ACY)" := "Actual Cost (ACY)" + TransValueEntry."Cost Amount (Actual) (ACY)";
      TransValueEntry2 := TransValueEntry;
    END;
  //UNTIL TransValueEntry.NEXT(-1) = 0; //Replaced this statement with the next statement
  UNTIL TransValueEntry.NEXT = 0;
  TransValueEntry := TransValueEntry2;
END;

My biggest question is, Did I do it correctly without breaking it?
I want to be a little more assured before loading this on the customers test server and start another Adjust cost process.

Also, can I speed up the process by eliminating data? It looks like it is processing Item Ledger Entries with a posting date of 2002.
I know the process looks at the Item Ledger Entry field "Applied Entry to Adjust". Can I set it so I will not look at the old ledger entries anymore?

Thank you,

Willy Martens

ara3n

The code change looks right, but the performance gain won't be that much. Most of the time 99% ItemLedgerEntry has one Direct cost entry type.


As far has adjust cost routine goes, this has been already discussed before that after update adjust cost looks at all ILE. There is a field in ILE that you can "Applied Entry to Adjust" You can set to false, and it won't look at old records, but it's a lot easier to filter by item and run it over a period of a week and finish all the items. Afterwards adjust cost will run the usual speed.

pdj

You will get a run-time error even though the on-line states this:

FINDSET on-line help wrote:

Furthermore, FINDSET only supports descending loops. If you want to loop from the bottom up, you should use FIND(‘+’).

It should say that is only supports ascending loops, the rest is correct.

Alex_Chow

If you're experiencing serious performance problem with the adjust cost process, you should try convincing the customer to go on v5.0. The Inventory Periods functionality is created specifically to address this issue on the prior versions.

DarkSide

I heard of people added a starting date filter somewhere in the code although I have not done this myself. It prevents Navision from looking back too far for making adjustments. If you try this you're going to need to do A LOT of testing.