on 01-06-2015 11:04 AM
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
HI
Select with corresponding fields of will degrade the performance, instead use select with for all entries of.....
regards
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
86 | |
10 | |
10 | |
9 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.