Inventory Report - Performance Issue

Viji_Ganesan

Hi All,

I have created a new excel report, with item as data item and Item Ledger Entry table as record variable.
The report basically calculates the quantity for the particular item depending upon the posting date given by the user in the option tab, say for particular item,
1. asofqty=posting date(0d..(startdate-1d))--quantity field from Item ledger entry table
2. IncQty = Posting date(startdate..Enddate)--quantity field from Item ledger entry table
3. RemQty, which is taken from remining quantity field from Item ledger entry table irrespective of posting date.

The location code and bin code are also the filters given in the option tab.

I have written the following code in the OnAftergetrecord trigger of item dataitem,
Since the no of records are more than one lak in the table it takes approx 1 hr for generating report for one month.

DateG := CALCDATE('-1D',StartDateG);
IF (LocationCodeG <> '') AND (BinCodeG <> '') THEN BEGIN
LocationG.RESET;
LocationG.SETRANGE(LocationG.Code,LocationCodeG);
IF LocationG.FIND('-') THEN BEGIN
BinG.RESET;
BinG.SETRANGE(BinG."Location Code",LocationG.Code);
BinG.SETRANGE(BinG.Code,BinCodeG);
IF BinG.FIND('-') THEN BEGIN
ItemLederEntryG.RESET;
ItemLederEntryG.SETCURRENTKEY("Item No.","Location Code","Bin Code","Posting Date");
ItemLederEntryG.SETRANGE(ItemLederEntryG."Item No.",Item."No.");
ItemLederEntryG.SETRANGE(ItemLederEntryG."Posting Date",0D,DateG);
ItemLederEntryG.SETRANGE(ItemLederEntryG."Location Code",LocationG.Code);
ItemLederEntryG.SETRANGE(ItemLederEntryG."Bin Code",BinG.Code);
IF ItemLederEntryG.FIND('-') THEN
REPEAT
AsOfQtyG := AsOfQtyG + ItemLederEntryG.Quantity;
UNTIL ItemLederEntryG.NEXT=0;

ItemLederEntryG.RESET;
ItemLederEntryG.SETCURRENTKEY("Item No.","Location Code","Bin Code","Posting Date");
ItemLederEntryG.SETRANGE(ItemLederEntryG."Item No.",Item."No.");
ItemLederEntryG.SETRANGE(ItemLederEntryG."Posting Date",StartDateG,EndDateG);
ItemLederEntryG.SETRANGE(ItemLederEntryG."Location Code",LocationG.Code);
ItemLederEntryG.SETRANGE(ItemLederEntryG."Bin Code",BinG.Code);
IF ItemLederEntryG.FIND('-') THEN
REPEAT
IncQtyG := IncQtyG + ItemLederEntryG.Quantity;
UNTIL ItemLederEntryG.NEXT=0;

ItemLederEntryG.RESET;
ItemLederEntryG.SETCURRENTKEY("Item No.","Location Code","Bin Code");
ItemLederEntryG.SETRANGE(ItemLederEntryG."Item No.",Item."No.");
ItemLederEntryG.SETRANGE(ItemLederEntryG."Location Code",LocationG.Code);
ItemLederEntryG.SETRANGE(ItemLederEntryG."Bin Code",BinG.Code);
IF ItemLederEntryG.FIND('-') THEN
REPEAT
RemQtyG := RemQtyG + ItemLederEntryG."Remaining Quantity";
UNTIL ItemLederEntryG.NEXT=0;
END;
END;

Kindly help me in performance of the report.

Thanks in advance

Viji.

suvidha

Hi,
For better Performance SETRANGE Should Ideally be used in same order as the Key defined in SETCURRENTKEY, especially when filtering on Big tables.
So in ur case it is:

ItemLederEntryG.RESET;
ItemLederEntryG.SETCURRENTKEY("Item No.","Location Code","Bin Code","Posting Date");
ItemLederEntryG.SETRANGE(ItemLederEntryG."Item No.",Item."No.");
ItemLederEntryG.SETRANGE(ItemLederEntryG."Posting Date",0D,DateG);
ItemLederEntryG.SETRANGE(ItemLederEntryG."Location Code",LocationG.Code);
ItemLederEntryG.SETRANGE(ItemLederEntryG."Bin Code",BinG.Code);


which needs Rearranging of SETRANGE statements to:

ItemLederEntryG.RESET;
ItemLederEntryG.SETCURRENTKEY("Item No.","Location Code","Bin Code","Posting Date");
ItemLederEntryG.SETRANGE(ItemLederEntryG."Item No.",Item."No.");
ItemLederEntryG.SETRANGE(ItemLederEntryG."Location Code",LocationG.Code);
ItemLederEntryG.SETRANGE(ItemLederEntryG."Bin Code",BinG.Code);
ItemLederEntryG.SETRANGE(ItemLederEntryG."Posting Date",0D,DateG);

Hope it helps:)

Viji_Ganesan

Thanks, will try and update

kine

suvidha wrote:

Hi,
For better Performance SETRANGE Should Ideally be used in same order as the Key defined in SETCURRENTKEY, especially when filtering on Big tables.
So in ur case it is:

ItemLederEntryG.RESET;
ItemLederEntryG.SETCURRENTKEY("Item No.","Location Code","Bin Code","Posting Date");
ItemLederEntryG.SETRANGE(ItemLederEntryG."Item No.",Item."No.");
ItemLederEntryG.SETRANGE(ItemLederEntryG."Posting Date",0D,DateG);
ItemLederEntryG.SETRANGE(ItemLederEntryG."Location Code",LocationG.Code);
ItemLederEntryG.SETRANGE(ItemLederEntryG."Bin Code",BinG.Code);


which needs Rearranging of SETRANGE statements to:

ItemLederEntryG.RESET;
ItemLederEntryG.SETCURRENTKEY("Item No.","Location Code","Bin Code","Posting Date");
ItemLederEntryG.SETRANGE(ItemLederEntryG."Item No.",Item."No.");
ItemLederEntryG.SETRANGE(ItemLederEntryG."Location Code",LocationG.Code);
ItemLederEntryG.SETRANGE(ItemLederEntryG."Bin Code",BinG.Code);
ItemLederEntryG.SETRANGE(ItemLederEntryG."Posting Date",0D,DateG);

Hope it helps:)

Only rumors... but we will see results... :-)