topic Re: sql statement too slow in Application Development and Automation Discussions

sql statement too slow

Former Member — Wed, 11 May 2011 06:09:15 GMT

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.

Re: sql statement too slow

Former Member — Wed, 11 May 2011 06:19:40 GMT

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.

Re: sql statement too slow

Former Member — Wed, 11 May 2011 06:21:53 GMT

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 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 " 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.

Re: sql statement too slow

paul_bakker2 — Wed, 11 May 2011 06:27:24 GMT

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

Re: sql statement too slow

Former Member — Wed, 11 May 2011 06:42:32 GMT

Hi,

Try to avoid 'CORRESPONDING FIELDS OF' Statement in Select Statement.

Re: sql statement too slow

Former Member — Wed, 11 May 2011 08:01:31 GMT

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.