Testing static FOR EACH versus DatasetModel versus FetchDataRequest Cancel

1. Roger Blanchard

   

   379 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?
   
   
   
   
   
   
   
2. Mike Fechner

   

   319 posts
   Registered:
   14 Sep 2016
   06 Feb 2019 in reply to Roger Blanchard

   Link to this post

   There is a natural performance penalty in using the dataset model classes - as we're executing more ABL code there. I've passed that as an example to Progress Software for where the ABL should perform faster. Client runtime performance parameters such as -q -reusableObjects and -D are helpful.
   
   Regarding the different count results. That seems to be a problem caused by repositioning the table model after fetching the next batch.We already have a ticket for that. You can fix that issue by enforcing a sort:
   
   oFinancialTotals:DeptTotal:SortBy:<some unique filed>:Ascending() .
   
   I'll add this case to the ticket.
3. Roger Blanchard

   

   379 posts
   Registered:
   29 Jun 2018
   06 Feb 2019 in reply to Mike Fechner

   Link to this post

   okay, thanks for the info.
4. Mike Fechner

   

   319 posts
   Registered:
   14 Sep 2016
   12 Feb 2019

   Link to this post

   Regarding the different count results. That seems to be a problem caused by repositioning the table model after fetching the next batch.We already have a ticket for that. You can fix that issue by enforcing a sort:

   
   
   FYI, the fix to SCL-2352 has been released on 10-02-2019
5. Roger Blanchard

   

   379 posts
   Registered:
   29 Jun 2018
   15 Feb 2019 in reply to Mike Fechner

   Link to this post

   Thanks Mike