Native SQL - data select performance issue on database

[Former Member]

Hello

Please help ,am new to Abap.My database administrator is facing challenges to execute his duties .In running t-code RZ20 he is getting this message

"Job ZUPTD_ZDCSRESERVE 11211700 started on 20130422 at 113117 has exceeded maximum runtime 6, Red 23.04.2013"

.We thought the issue might be BASIS of which he is arguing that something must be wrong with our program.

The program is being used to transport asset data from a legacy system to  to SAP.An intermediate table ZDCS_reserve was done to feed asset numbers into sap table zdcs_jobs.The following is the code snippet.I am thinking there might be a problem with the native Sql which was used.

REPORT  ZUPTD_ZDCSRESERVE.


tables: zdcs_reserve,mbew,mseg.
data: wa_reserve type zdcs_reserve,
       it_reserve type table of zdcs_reserve with header line.


refresh it_reserve.

EXEC SQL PERFORMING append_data.
   select * into :wa_reserve

               from zdcs_reserve where MANDT = '000'
ENDEXEC.



loop at it_reserve.

   insert zdcs_reserve from it_reserve.
endloop.



SELECT * FROM zdcs_reserve WHERE cost = '0' or cost EQ ''.


   SELECT * FROM mbew WHERE matnr = zdcs_reserve-MATERIAL_CODE and BWKEY EQ zdcs_reserve-PLANT  ."and LGORT EQ zdcs_reserve-STORE_CODE .
     zdcs_reserve-cost = mbew-VERPR.

   endselect.
   MODIFY zdcs_reserve.

ENDSELECT.

**** changed on 17/08/2011
refresh it_reserve.

select * from  zdcs_reserve into table it_reserve where DOCUMENT_NO EQ '0000' and posted = 'X' or DOCUMENT_NO EQ '' and posted = 'X' order by JOB_NUMBER ascending.
***
loop at it_reserve ." where RESERVATION_CODE NE ''.

   select * from zdcs_reserve where JOB_NUMBER = it_reserve-JOB_NUMBER and material_code eq it_reserve-material_code .endselect.

   select  * from mseg where rsnum eq zdcs_reserve-RESERVATION_CODE and matnr eq zdcs_reserve-material_code.

     if sy-subrc EQ 0.

       zdcs_reserve-DOCUMENT_NO = mseg-mblnr.
       zdcs_reserve-MAT_TAKEN = mseg-menge.

       if  zdcs_reserve-MAT_TAKEN > zdcs_reserve-QUANTITY .

         zdcs_reserve-MAT_BALANCE = '0'.

       else.
         zdcs_reserve-MAT_BALANCE = zdcs_reserve-QUANTITY - zdcs_reserve-MAT_TAKEN.

       endif.

*      updating DCS_reserve table with material quantities balances aswell as the SAP document number
****** posted for the respective reservation numbers
       modify zdcs_reserve.

     endif.
   endselect.

endloop.



EXEC SQL .

   delete from zdcs_reserve where MANDT = '000'

ENDEXEC.



*&---------------------------------------------------------------------*
*&      Form  APPEND_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM APPEND_DATA .

if wa_reserve is not initial.

   append wa_reserve to it_reserve.
endif.
   clear wa_reserve.

ENDFORM.                    "APPEND_DATA

The other one is :

REPORT  ZUPTD_ZDCS_JOBS.

tables: zdcs_jobs.
data: wa_reserve type zdcs_jobs,
       it_reserve type table of zdcs_jobs with header line.



     EXEC SQL PERFORMING append_data.
       select * into :wa_reserve

                   from zdcs_jobs
     ENDEXEC.

     loop at it_reserve.

       insert zdcs_jobs from it_reserve.
       endloop.

FORM APPEND_DATA .

append wa_reserve to it_reserve.

clear wa_reserve.

ENDFORM.

[Former Member]

Rejoice,

     Just did an overview of the code and found that there can be some improvements done in places where you are fetching data from database table. Below points may help you in improving the performance.

     1) Avoid using SELECT * statements unless it is absolute necessary. Using it may result in fetching column data that is not required in the program.

     2) Use of SELECT ENDSELECT is not recommended since each execution hits database and result in poor performance. Instead you can use SELECT INTO TABLE which will certainly improve the performance.

     3) Avoid using SELECT statements inside the loop. This behaves much similar to SELECT ENDSELECT statement.

     You can also search for performance tuning related aspects for select statements in the forum.

Hope this helps,

~Athreya

[Clemenss]

There is no obvious reason at all to use native SQL. Also, you did not mention the data quantity handled - it may be huge.

Sometimes, tables are defined in dictionary for the wrong size category. Then when they get bigger the database gets slow. Here you need a professional to update the database statistics.

And: What are the results of your performance analysis and/or sql trace?

Regards

Clemens