Testing static FOR EACH versus DatasetModel versus FetchDataRequest
 
Forums / SmartComponent Library - Developer Forum / Testing static FOR EACH versus DatasetModel versus FetchDataRequest

Testing static FOR EACH versus DatasetModel versus FetchDataRequest

5 posts, 0 answered
  1. Roger Blanchard
    Roger Blanchard avatar
    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?






  2. Mike Fechner
    Mike Fechner avatar
    342 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
    Roger Blanchard avatar
    413 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
    Mike Fechner avatar
    342 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
    Roger Blanchard avatar
    413 posts
    Registered:
    29 Jun 2018
    15 Feb 2019 in reply to Mike Fechner
    Link to this post
    Thanks Mike
5 posts, 0 answered