Roger Blanchard
413 posts
Registered:
29 Jun 2018
03 Feb 2019
Link to this post
I was testing some of our backend report logic and trying to determine the best way to move this to use the SCL if at all.
With a STATIC FOR EACH below the FOR EACH will take 724ms and return 53,141 records.
FOR EACH DeptTotal WHERE DeptTotal.SaleDate GE dtStartDate
AND DeptTotal.SaleDate LE dtEndDate
AND DeptTotal.Period EQ cPeriod
AND DeptTotal.StoreId EQ cStoreId
AND DeptTotal.CashierNum EQ 0 NO-LOCK:
iCnt = iCnt + 1.
END.
If I use a DatsetModel class and use the code below (iBatchSize = 0) it takes 2782ms and returns 53.141 records.
oFinancialTotals:DeptTotal:Filter:Reset().
oFinancialTotals:BatchSize = iBatchSize.
oFinancialTotals:DeptTotal:Batching = TRUE.
oFinancialTotals:DeptTotal:Filter:SaleDate:GE(dtStartDate).
oFinancialTotals:DeptTotal:Filter:SaleDate:LE(dtEndDate).
oFinancialTotals:DeptTotal:Filter:Period:EQ(cPeriod).
oFinancialTotals:DeptTotal:Filter:StoreId:EQ(cStoreId).
oFinancialTotals:DeptTotal:Filter:CashierNum:EQ(0).
oFinancialTotals:DeptTotal:Filter:Run().
DO WHILE oFinancialTotals:DeptTotal:Available:
iCnt = iCnt + 1.
oFinancialTotals:DeptTotal:GetNext().
END.
If I use a FetchDataRequest and use the code below (iBatchSize=0) it takes 3368ms and returns 53,141 records.
oRequest = NEW FetchDataRequest ("eDeptTotal",
SUBSTITUTE ("FOR EACH eDeptTotal WHERE eDeptTotal.SaleDate GE &1
AND eDeptTotal.SaleDate LE &2
AND eDeptTotal.Period EQ &3
AND eDeptTotal.StoreId EQ &4
AND eDeptTotal.CashierNum EQ 0", dtStartDate, dtEndDate, QUOTER (cPeriod), QUOTER (cStoreID) ),
iBatchSize) .
REPEAT:
/* Perform request for a batch */
FrameworkSettings:ServiceAdapter:RetrieveData ("",
"Osprey.Host.FinancialTotals.FinancialTotalsBusinessEntity",
oRequest,
OUTPUT DATASET dsFinancialTotals).
/* Process batch data */
FOR EACH eDeptTotal:
iCnt = iCnt + 1.
END.
IF oRequest:NextContext > "" THEN
oRequest:Context = oRequest:NextContext .
ELSE
LEAVE.
END.
If I then use a BatchSize of1000 the results change;
The DatasetModelClass only returns 50,475 records and it takes 6170ms. Is there anything else I need to do to get all the records? Is my code not correct?
The FetchDataRequest returns all 53,141 records and it takes 3273ms.
It appears the STATIC FOR EACH gives the best performance with the FetchDataRequest the best technique for the SCL.
I guess I am wondering what is the recommended approach for extracting data that wil be used for reporting?