05-11-2011 7:09 AM
For some reason I have to change code from this:
LOOP AT GT_SKA1.
SELECT A~BELNR A~GJAHR A~BUZEI A~BUKRS A~HKONT A~SHKZG A~KOSTL B~HWAER A~DMBTR B~BKTXT A~BLDAT A~BUDAT B~CPUDT B~CPUTM B~USNAM
FROM BSIS AS A INNER JOIN BKPF AS B ON A~BUKRS = B~BUKRS AND
A~GJAHR = B~GJAHR AND
A~BELNR = B~BELNR
APPENDING CORRESPONDING FIELDS OF TABLE GT_GLACC
WHERE A~HKONT = GT_SKA1-SAKNR AND
A~BUKRS IN S_WERKS AND
A~BLDAT IN S_BLDAT AND
A~BUDAT IN S_BUDAT AND
B~CPUDT IN S_CPUDT AND
B~USNAM IN S_USNAM AND
B~BLART NE 'WA'.
ENDLOOP.
to this:
SELECT A~BELNR A~GJAHR A~BUZEI A~BUKRS A~HKONT A~SHKZG A~KOSTL B~HWAER A~DMBTR B~BKTXT A~BLDAT A~BUDAT B~CPUDT B~CPUTM B~USNAM
FROM BSIS AS A INNER JOIN BKPF AS B ON A~BUKRS = B~BUKRS AND
A~GJAHR = B~GJAHR AND
A~BELNR = B~BELNR
APPENDING CORRESPONDING FIELDS OF TABLE GT_GLACC
WHERE A~HKONT IN S_SAKNR AND
A~BUKRS IN S_WERKS AND
B~CPUDT IN S_CPUDT AND
B~USNAM IN S_USNAM AND
B~BLART NE 'WA'.
SELECT A~BELNR A~GJAHR A~BUZEI A~BUKRS A~HKONT A~SHKZG A~KOSTL B~HWAER A~DMBTR B~BKTXT A~BLDAT A~BUDAT B~CPUDT B~CPUTM B~USNAM
FROM BSIS AS A INNER JOIN BKPF AS B ON A~BUKRS = B~BUKRS AND
A~GJAHR = B~GJAHR AND
A~BELNR = B~BELNR
APPENDING CORRESPONDING FIELDS OF TABLE GT_GLAC2
WHERE A~HKONT IN S_SAKNR AND
A~BUKRS IN S_WERKS AND
A~BUDAT IN S_CPUDT AND
B~USNAM IN S_USNAM AND
B~BLART NE 'WA'.
*delete entries which posting date greater than higher limit of selected period
LOOP AT GT_GLACC.
IF GT_GLACC-BUDAT > S_CPUDT-HIGH.
DELETE GT_GLACC.
ENDIF.
ENDLOOP.
*delete entries which entry date greater than lower limit of selected period
LOOP AT GT_GLAC2.
IF GT_GLAC2-CPUDT < S_CPUDT-LOW.
MOVE-CORRESPONDING GT_GLAC2 TO GT_GLACC.
APPEND GT_GLACC.
ENDIF.
ENDLOOP.
However, the resulted code is very slow. The original one only runs around 10 minutes while the resulted one need to be run for 10 hours.
Actually, the sql statement did not change much and I only run two times for it in the new code design.
I want to ask why is it so slow and any solution for this.
Thanks, Experts.
05-11-2011 7:19 AM
Hi,
Check your loop .. endloop statements.
You can write as :
sort GT_GLACC by BUDAT descending.
Delete GT_GLACC where BUDAT > S_CPUDT-HIGH.
sort GT_GLACC2 by CPUDT .
LOOP AT GT_GLAC2 where CPUDT < S_CPUDT-LOW..
MOVE-CORRESPONDING GT_GLAC2 TO GT_GLACC.
APPEND GT_GLACC.
ENDLOOP.
Regards,
Srini.
05-11-2011 7:19 AM
Hi,
Check your loop .. endloop statements.
You can write as :
sort GT_GLACC by BUDAT descending.
Delete GT_GLACC where BUDAT > S_CPUDT-HIGH.
sort GT_GLACC2 by CPUDT .
LOOP AT GT_GLAC2 where CPUDT < S_CPUDT-LOW..
MOVE-CORRESPONDING GT_GLAC2 TO GT_GLACC.
APPEND GT_GLACC.
ENDLOOP.
Regards,
Srini.
05-11-2011 7:21 AM
Hi,
Because you are retriving data from database tables comparing with single record. check below code which statement is in red color.
LOOP AT GT_SKA1.
SELECT ABELNR AGJAHR ABUZEI ABUKRS AHKONT ASHKZG AKOSTL BHWAER ADMBTR BBKTXT ABLDAT ABUDAT BCPUDT BCPUTM B~USNAM
FROM BSIS AS A INNER JOIN BKPF AS B ON ABUKRS = BBUKRS AND
AGJAHR = BGJAHR AND
ABELNR = BBELNR
APPENDING CORRESPONDING FIELDS OF TABLE GT_GLACC
WHERE A~HKONT = GT_SKA1-SAKNR AND " Because of this line the code performance is good
A~BUKRS IN S_WERKS AND
A~BLDAT IN S_BLDAT AND
A~BUDAT IN S_BUDAT AND
B~CPUDT IN S_CPUDT AND
B~USNAM IN S_USNAM AND
B~BLART NE 'WA'.
ENDLOOP.
Ram.
05-11-2011 7:27 AM
Hi,
If you are selecting a lot of data into table GT_GLACC, I would say the problem is with the DELETE command.
It is very time consuming to DELETE entries from a large table in ABAP.
To avoid using DELETE, try this instead:
*delete entries which posting date greater than higher limit of selected period
*create a filtered table of the items we wish to keep
LOOP AT GT_GLACC.
IF GT_GLACC-BUDAT <= S_CPUDT-HIGH.
APPEND GT_GLACC to GT_GLACC_KEEP.
ENDIF.
ENDLOOP.
*overwrite the original table with the filtered table
MOVE GT_GLACC_KEEP[] to GT_GLACC[].
cheers
Paul
05-11-2011 7:42 AM
Hi,
Try to avoid 'CORRESPONDING FIELDS OF' Statement in Select Statement.
05-11-2011 9:01 AM
Problem solved.
Now I instead of looping the table gt_ska1 to run sql statement,
I loop through the table and insert all values into a select option.
Then rewriting the sql statement like this:
SELECT A~BELNR A~GJAHR A~BUZEI A~BUKRS A~HKONT A~SHKZG A~KOSTL B~HWAER A~DMBTR B~BKTXT A~BLDAT A~BUDAT B~CPUDT B~CPUTM B~USNAM
FROM BSIS AS A INNER JOIN BKPF AS B ON A~BUKRS = B~BUKRS AND
A~GJAHR = B~GJAHR AND
A~BELNR = B~BELNR
APPENDING CORRESPONDING FIELDS OF TABLE GT_GLACC
WHERE A~HKONT IN S_SAKNR AND
A~BUKRS IN S_WERKS AND
B~CPUDT IN S_CPUDT AND
B~USNAM IN S_USNAM AND
B~BLART NE 'WA'.
Thanks for your help.