Skip to Content
avatar image
Former Member

Data load takes long time due to select statement in endroutine

Hi All,

We have data load from DSO1 to DSO2 having endroutine .The load takes long time (approx. 1.5 hours) for 23000 records delta load.

In end routine there is a select statement .

We have already created sec. index for ACCTYPE but still it takes long time and I feel that this long time is due to the sum statement which does aggregation of values.

Can you please suggest me whether the long run is due to sum statement ? if so,how to optimize the code in easier way.

SELECT

            ACDOCNUM AS ACDOCNUM

            ACCTYPE AS ACC_TYPE

            POSTINGDATE AS  POSTINGDATE

            PSAWKEY AS MAT_DOC_YEAR

            GLACCT AS GLACCT

            /BIC/ZGKON AS ZGKON

            SUM( DEB_CR_L1)  AS DEB_CR_L1

            LOC_CURC1  AS LOC_CURC1

            SUM( DEB_CR_L1 ) AS DEB_CR_L1

            FROM /BI0/AFIGL_O11400 INTO CORRESPONDING FIELDS OF TABLE

           IT_FIGL_O114

          WHERE ACCTYPE = 'M' GROUP BY ACDOCNUM  ACCTYPE  POSTINGDATE PSM_AWKEY GLACCT

            /BIC/ZGKON LOC_CURRC2.

          SORT IT_FIGL_O14 BY MAT_DOC_YEAR

Also,

there are three calulcations as below in loop statement

-/BIC/ZTOTLK = ( <RESULT_FIELDS>-ISSVALCK +

                                         <RESULT_FIELDS>-RECVATCK ).

regards

Pradeep


Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    avatar image
    Former Member
    Jan 09, 2015 at 04:25 AM

    HI

    Select with corresponding fields of will degrade the performance, instead use select with for all entries of.....

    regards

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 09, 2015 at 04:08 AM

    Hi Pradeep,

    SELECT
      AC_DOC_NR AS AC_DOC_NR
            ACCT_TYPE AS ACC_TYPE
    "<< ADD ALL KEY FIELDS of 0FIGL_O14 if they are not present already >>
    "<<IT_LOOKUP_DSO should have the same set of fields as defined in this SELECT statement>>
            PSTNG_DATE AS PSTNG_DATE
            PSM_AWKEY AS MAT_DOC_YEAR
            GL_ACCOUNT AS GL_ACCOUNT
            /BIC/ZGKONT AS ZGKONT
      DEB_CRE_L2
            LOC_CURRC2 AS LOC_CURR2
            DEB_CRE_LC
            FROM /BI0/AFIGL_O1400 
      INTO CORRESPONDING FIELDS OF TABLE IT_LOOKUP_DSO 
            WHERE
            ACCT_TYPE = 'M'.
    
    LOOP AT IT_LOOKUP_DSO into WA_LOOKUP.
      MOVE-CORRESPONDING WA_LOOKUP to WA_FIGL_O14.
      COLLECT WA_FIGL_O14 INTO IT_FIGL_O14.
    ENDLOOP.
    
    
    SORT IT_FIGL_O14 BY MAT_DOC_YEAR.
    
    
    " Rest of the logic from LOOP AT RESULT_PACKAGE remains unchanged
    

    Regards,

    Suhas

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Pradeep,

      You can filter the data by using For All Entries.

      First, before the Select, add a loop to fill up a table of MAT_DOC_YEAR values.

      LOOP AT RESULT_PACKAGE ASSIGNING <Result_fields>.
           CONCATENATE  <RESULT_FIELDS>-MAT_DOC <RESULT_FIELDS>-FISCYEAR INTO  V_MAT_DOC_YEAR.
           COLLECT V_MAT_DOC_YEAR into IT_MAT_DOC_YEAR. "This table has only one field, of type V_MAT_DOC_YEAR.
      ENDLOOP.
      
      
      IF IT_MAT_DOC_YEAR[] is not initial.
      SELECT
      "<< ADD ALL KEY FIELDS of 0FIGL_O14 if they are not present already >> 
      ...
              FROM /BI0/AFIGL_O1400   
        INTO CORRESPONDING FIELDS OF TABLE IT_LOOKUP_DSO  
        FOR ALL ENTRIES IN IT_MAT_DOC_YEAR
              WHERE 
              MAT_DOC_YEAR = IT_MAT_DOC_YEAR-MAT_DOC_YEAR.
              ACCT_TYPE = 'M'.
      ENDIF.
      

      That will reduce the number of rows.

      Also, in the select statement, take only the key fields (which are essential for For All Entries) and the fields that are required for the rest of the logic. For example, PSTNG_DATE, /BIC/ZGKONT etc are not used. By doing this, the number of columns will also decrease. And this will reduce the memory consumption.

      Regards,

      Suhas

  • Jan 06, 2015 at 11:15 AM

    Hi,

    Is the SELECT statement inside any loop?

    Please post the entire code to analyse further.

    How many entries are there in lookup DSO active table?

    -Sriram

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Sriram,

      Here is the full code. Look DSO active table contains 18 million records.

      Can you please suggest easiest way to optimize. Presenly it takes 1 hours to finish.

          DATA: LIN TYPE I.
          DESCRIBE TABLE IT_FIGL_O14 LINES LIN.
            CLEAR IT_FIGL_O14.

            SELECT
                  AC_DOC_NR AS AC_DOC_NR
                  ACCT_TYPE AS ACC_TYPE
                  PSTNG_DATE AS PSTNG_DATE
                  PSM_AWKEY AS MAT_DOC_YEAR
                  GL_ACCOUNT AS GL_ACCOUNT
                  /BIC/ZGKONT AS ZGKONT
                  SUM( DEB_CRE_L2 )  AS DEB_CRE_L2
                  LOC_CURRC2 AS LOC_CURR2
                  SUM( DEB_CRE_LC ) AS DEB_CRE_LC
                  FROM /BI0/AFIGL_O1400 INTO CORRESPONDING FIELDS OF TABLE
                 IT_FIGL_O14

                WHERE

                  ACCT_TYPE = 'M' GROUP BY
                  AC_DOC_NR ACCT_TYPE PSTNG_DATE PSM_AWKEY GL_ACCOUNT
                  /BIC/ZGKONT LOC_CURRC2.
            SORT IT_FIGL_O14 BY MAT_DOC_YEAR.


          LOOP AT RESULT_PACKAGE ASSIGNING <RESULT_FIELDS>.

            <RESULT_FIELDS>-/BIC/ZTOTLSTCK = ( <RESULT_FIELDS>-ISSVALSTCK +
                                               <RESULT_FIELDS>-RECVALSTCK ).

            <RESULT_FIELDS>-/BIC/ZBLOCSTCK = ( <RESULT_FIELDS>-ISSBLOSTCK +
                                              <RESULT_FIELDS>-RECBLOSTCK ).

            <RESULT_FIELDS>-/BIC/ZTRANSTCK = ( <RESULT_FIELDS>-ISSTRANSST +
                                               <RESULT_FIELDS>-RECTRANSST ).

            CLEAR V_MAT_DOC_YEAR.

            CONCATENATE  <RESULT_FIELDS>-MAT_DOC <RESULT_FIELDS>-FISCYEAR INTO
            V_MAT_DOC_YEAR.

            CLEAR WA_FIGL_O14.
            READ TABLE IT_FIGL_O14 INTO WA_FIGL_O14 WITH TABLE KEY
            MAT_DOC_YEAR = V_MAT_DOC_YEAR
            ACC_TYPE = 'M'.
            IF SY-SUBRC EQ 0.

                <RESULT_FIELDS>-GL_ACCOUNT = WA_FIGL_O14-GL_ACCOUNT.
                <RESULT_FIELDS>-DEB_CRE_LC = WA_FIGL_O14-DEB_CRE_LC.
                <RESULT_FIELDS>-DEB_CRE_L2 = WA_FIGL_O14-DEB_CRE_L2.
                <RESULT_FIELDS>-LOC_CURRC2 = WA_FIGL_O14-LOC_CURR2.
                <RESULT_FIELDS>-AC_DOC_NO = WA_FIGL_O14-AC_DOC_NR.
                <RESULT_FIELDS>-/BIC/ZGLDATE = WA_FIGL_O14-PSTNG_DATE.
            ENDIF.

      End loop.


      Regards

      Pradeep

  • Jan 06, 2015 at 12:12 PM

    Using a GROUP BY (and therefore aggregate functions like SUM) is not good for performance, so please avoid that unless you're on HANA. You can achieve the same result by getting the data into an ITAB and using collect statements.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Suhas,

      Thanks for the reply. Can you please send me the code modification with collect statement.

      Regards

      Pradeep

  • avatar image
    Former Member
    Jan 08, 2015 at 05:47 PM

    Hi Pradeep,

    Suhas is right, group by is not a good performance.

    My advice is create a dso with agregation level, and your routine is something like:

        SELECT

            AC_DOC_NR AS AC_DOC_NR

            ACCT_TYPE AS ACC_TYPE

            PSTNG_DATE AS PSTNG_DATE

            PSM_AWKEY AS MAT_DOC_YEAR

            GL_ACCOUNT AS GL_ACCOUNT

            /BIC/ZGKONT AS ZGKONT

            DEB_CRE_L2  AS DEB_CRE_L2

            LOC_CURRC2 AS LOC_CURR2

            DEB_CRE_LC AS DEB_CRE_LC

        FROM /BI0/AFIGL_NEWDSO INTO TABLE IT_FIGL_O14

        FOR ALL ENTRIES IN RESULT_PACKAGE

        WHERE ACCT_TYPE = 'M'.

      IF sy-subrc EQ 0.

    SORT IT_FIGL_O14 BY MAT_DOC_YEAR.

        ENDIF.

        LOOP..

    READ IT_FIGL_O14 INTO WA_FIGL_O14 WITH KEY mat_doc =<result_fields>-mat_doc

      doc_year =<result_fields>-doc_year

    BINARY SEARCH.

            IF sy-subrc EQ 0.

              "Assign values to RESULT_PACKAGE.

    ENDIF.

        ENDLOOP.

    Regards,

    Add comment
    10|10000 characters needed characters exceeded