Data load takes long time due to select statement in endroutine

[Former Member]

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

[srikanth_rapur2 · Accepted solution]

HI

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

regards

[former_member185132]

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

[Former Member]

Hi Suhas,

Thanks for the reply.As mentioned in row 4 ""<< ADD ALL KEY FIELDS of 0FIGL_O14 if they are not present already >>  "

Is it mandatory to add all key fields such as company code? when we use collect statement.

Can you please say the impact if don't add all key fields.

Regards

Pradeep

[former_member185132]

Hi Pradeep,

My mistake, all the key fields are required only if you are using For All Entries in your select statement. As you aren't, you can avoid adding the key fields if they are not necessary.

If you choose to use For All Entries, then add all the key fields.

Regards,

Suhas

[Former Member]

Hi Suhas,

Thanks for the reply.I have tested the code in development.Data load duration is getting reduced after adding collect statement.But,the CPU memory consumption is taking 99 % which basis team is complaining.

The memory was high even before introducing collect statement.Can you please help if we can modify the code again.

Please note that only this particular data load was taking 99 % and no other loads were running in parallel.

Regards

Pradeep

[former_member185132]

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

[fcorodriguezl]

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,

[former_member185132]

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.

[Former Member]

Hi Suhas,

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

Regards

Pradeep

[sriramvijay_ravidoss]

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

[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