Skip to Content
author's profile photo Former Member
Former Member

sql statement too slow

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.

Add a comment
10|10000 characters needed characters exceeded

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on May 11, 2011 at 06: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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 11, 2011 at 06: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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 11, 2011 at 06: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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 11, 2011 at 06:42 AM

    Hi,

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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 11, 2011 at 08: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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.