Performance Issue

[Former Member]

Hi,

Can anyone let me know where exactly the performance is getting slow in the below code. My final internal table here is <b>i_iflot</b>. It contains around 30000 Records to be displayed in the output. For less number of records the performance is good. But to display 30000 records its taking much time to process. Pls let me know where exactly i can change the code to improve performance. While in Debug mode i could not find any delay in executing the below statements.

SORT : i_iflot,

i_iflotx,

i_zcs_instal,

i_zcs_nct,

i_jest,

i_t25a3,

i_zcs_nc1.

*Populating Export table

LOOP AT i_iflot INTO w_iflot.

SELECT objnr stat inact FROM jest INTO TABLE i_inst_jest

WHERE objnr = w_iflot-objnr AND

stat = 'I0076' AND

inact = ' '.

IF sy-subrc NE 0.

SELECT objnr stat inact FROM jest INTO TABLE i_inst_jest

WHERE objnr = w_iflot-objnr AND

stat = 'E0004'.

IF sy-subrc EQ 0.

SELECT objnr stat inact FROM jest INTO TABLE i_inst_jest

WHERE objnr = w_iflot-objnr AND

stat = 'I0320' AND

inact = ' '.

ENDIF.

ENDIF.

IF sy-subrc <> 0.

installation_list-installation_number = w_iflot-tplnr.

installation_list-installation_old_name = w_iflot-old_name.

installation_list-installation_long_text = 'Text'.

IF module_number = 0.

i_advance_list2-superior_fun_location = w_iflot-tplma.

ENDIF.

IF module_number = 0.

i_advance_list2-installation_number = w_iflot-tplnr.

ENDIF.

CLEAR w_iflotx.

READ TABLE i_iflotx

INTO w_iflotx

WITH KEY tplnr = w_iflot-tplnr

BINARY SEARCH.

IF sy-subrc = 0.

installation_list-installation_name = w_iflotx-pltxt.

IF module_number = 0.

i_advance_list2-installation_name = w_iflotx-pltxt.

ENDIF.

ENDIF.

CLEAR w_zcs_instal.

READ TABLE i_zcs_instal

INTO w_zcs_instal

WITH KEY tplnr = w_iflot-tplnr

BINARY SEARCH.

IF sy-subrc = 0.

installation_list-lloyds_number = w_zcs_instal-zzlyod.

installation_list-inst_app_category = w_zcs_instal-zzinst_appl.

• To get Installation Application category text

IF installation_list-inst_app_category = 'M'.

MOVE 'Ship Power' TO installation_list-inst_app_category.

ENDIF.

IF installation_list-inst_app_category = 'P'.

MOVE 'Power Plant' TO installation_list-inst_app_category.

ENDIF.

IF installation_list-inst_app_category = 'O'.

MOVE 'Others' TO installation_list-inst_app_category.

ENDIF.

installation_list-project_name = w_zcs_instal-zzshipyard_name.

installation_list-project_number = w_zcs_instal-zzinst_yardn.

installation_list-country_of_operation = w_zcs_instal-landx.

• To display Sales area text

CLEAR w_zcs_nc1.

READ TABLE i_zcs_nc1

INTO w_zcs_nc1

WITH KEY zznc = w_zcs_instal-zznc

BINARY SEARCH.

IF sy-subrc = 0.

CLEAR w_t25a3.

READ TABLE i_t25a3

INTO w_t25a3

WITH KEY ww009 = w_zcs_nc1-zzsanew

BINARY SEARCH.

ENDIF.

IF sy-subrc = 0.

installation_list-sales_ares = w_t25a3-bezek.

ENDIF.

IF module_number = 0.

i_advance_list2-lloyds_number = w_zcs_instal-zzlyod.

i_advance_list2-project_name = w_zcs_instal-zzshipyard_name.

i_advance_list2-country_of_operation = w_zcs_instal-landx.

ENDIF.

ENDIF.

CLEAR w_zcs_nct.

IF installation_list-lloyds_number = '0000000000'.

REPLACE ALL OCCURRENCES OF '0' IN installation_list-lloyds_number WITH space.

ENDIF.

CLEAR w_jest.

READ TABLE i_jest

INTO w_jest

WITH KEY objnr = w_iflot-objnr

stat+0(1) = 'E'

inact = ' '.

IF sy-subrc = 0.

installation_list-operating_status = w_jest-stat.

IF installation_list-operating_status = 'E0001'.

installation_list-operating_status = 'Operating'.

ENDIF.

IF installation_list-operating_status = 'E0002'.

installation_list-operating_status = 'Not Operating'.

ENDIF.

IF installation_list-operating_status = 'E0003'.

installation_list-operating_status = 'Pending'.

ENDIF.

IF installation_list-operating_status = 'E0004'.

installation_list-operating_status = 'Scrapped'.

ENDIF.

ENDIF.

IF module_number = 0.

i_advance_list2-status = w_jest-stat.

ENDIF.

READ TABLE i_zcs_nct

INTO w_zcs_nct

WITH KEY zznc = w_zcs_instal-zznc

BINARY SEARCH.

IF sy-subrc = 0.

installation_list-resp_network_company = w_zcs_nct-zznct.

IF module_number = 0.

i_advance_list2-responsible_unit = w_zcs_nct-zznct.

ENDIF.

ENDIF.

CLEAR w_t005t.

IF module_number = 2.

APPEND installation_list.

SORT installation_list BY installation_name.

ELSE.

APPEND i_advance_list2.

ENDIF.

CLEAR installation_list.

CLEAR advance_list.

REFRESH i_cust_final.

ENDIF.

ENDLOOP.

Regards,

Madhu

[Former Member]

The performance issue is because, you are looping at one table and trying to fire queries to JEST table 3 times based on some coditions and return code.So if your internal table you are looping has 10000 lines, you fire 30000 times query.

Instead I wud suggest, define three tables and use the key word "for all entries in <internal table>" based on the three conditions.

Then the total number of hits to database will be only three. And you program runs really fast. You lose only some few extra KB of memory and will save some MB of buffer on database server and network traffic.

regards,

Sandeep Josyula

*Reward if helpful

[Former Member]

Try not use select inside a loop,instead of that use for all enttries.

Try this out and let me know if u still face the problem.

Regards

[Former Member]

hi

good

use CORRESPONDING FIELDS OF statement in every select statement.

-

---

SELECT objnr stat inact FROM jest INTO TABLE i_inst_jest

WHERE objnr = w_iflot-objnr AND

stat = 'I0076' AND

inact = ' '.

IF sy-subrc NE 0.

SELECT objnr stat inact FROM jest INTO TABLE i_inst_jest

WHERE objnr = w_iflot-objnr AND

stat = 'E0004'.

IF sy-subrc EQ 0.

SELECT objnr stat inact FROM jest INTO TABLE i_inst_jest

WHERE objnr = w_iflot-objnr AND

stat = 'I0320' AND

-

---

in these three select statement you r selecting the same field but the conditions are different so try to make it one select statement so that it ll increase your performance.

thanks

mrutyun^

[Former Member]

Dear Tripathy

FYI -

Using the key : into corresponding fields reduces the performance of the select query

The second idea is absolutely fine.. (Building dynamic select query )and efficient. Thanks for the idea !

Please check the former and come back in case ....

Regards,

Sandeep Josyula

*Award for helpful answers

[Former Member]

Hi,

How to reduce the below select statements into one select statement. Also based on the subrc checks i am writing the select statements.

LOOP AT i_iflot INTO w_iflot.

SELECT objnr stat inact FROM jest INTO TABLE i_inst_jest

WHERE objnr = w_iflot-objnr AND

stat = 'I0076' AND

inact = ' '.

IF sy-subrc NE 0.

SELECT objnr stat inact FROM jest INTO TABLE i_inst_jest

WHERE objnr = w_iflot-objnr AND

stat = 'E0004'.

IF sy-subrc EQ 0.

SELECT objnr stat inact FROM jest INTO TABLE i_inst_jest

WHERE objnr = w_iflot-objnr AND

stat = 'I0320' AND

inact = ' '.

ENDIF.

ENDIF.

IF sy-subrc <> 0.

installation_list-installation_number = w_iflot-tplnr.

installation_list-installation_old_name = w_iflot-old_name.

installation_list-installation_long_text = 'Text'.

Regards,

Madhu

[Former Member]

SELECT objnr stat inact FROM jest INTO TABLE i_inst_jest

WHERE objnr = w_iflot-objnr AND

stat = 'I0076' AND

inact = ' '.

IF sy-subrc NE 0.

SELECT objnr stat inact FROM jest INTO TABLE i_inst_jest

WHERE objnr = w_iflot-objnr AND

stat = 'E0004'.

IF sy-subrc EQ 0.

SELECT objnr stat inact FROM jest INTO TABLE i_inst_jest

WHERE objnr = w_iflot-objnr AND

stat = 'I0320' AND

inact = ' '.

ENDIF.

ENDIF.

<b>

The above can be replaced by the below query before the loop.</b>

SELECT objnr stat inact from jest into table i_inst_jest

for all enteries in i_iflot

where objnr = i_iflot-objnr and

stat in ('I0320','E0004','I0076').

sort i_inst_jest.

loop at i_iflot into w_iflot.

read table i_inst_jest with key objnr = w_iflot-objnr

stat = 'I0076'

inact = ' '.

if sy-subrc ne 0.

read table i_inst_jest with key objnr = w_iflot-objnr

stat = 'E0004'.

if sy-subrc = 0.

read table i_inst_jest with key objnr = w_iflot-objnr

stat = 'I0320'

inact = ' '.

endif.

endif.

if sy-subrc <> 0.

installation_list-installation_number = w_iflot-tplnr.

installation_list-installation_old_name = w_iflot-old_name.

installation_list-installation_long_text = 'Text'.

endif.

Endloop.

Message was edited by: Anurag Bankley

[Former Member]

Hi,

Still the performance is very slow. Its not even picking the records to the output. If the internal table contains more than 10000 records its not giving any result. Its taking much time to execute the statements.In debug mode i could not find any delay in executing..

Any help on this..

Regards,

Madhu

[Former Member]

Do you have duplicate records for the same objnr in iflotx ?

If so, sort the same and delete duplicates and than use for all entries.

[Former Member]

Hi

You can make it in one shot...

if not i_iflot[] is initial.

SELECT objnr stat inact FROM jest INTO TABLE i_inst_jest

FOR all entries in i_iflot

WHERE objnr = i_iflot-objnr AND

stat in ('I0076','E0004','I0320')AND

inact = ' '.

endif.

Later you can loop on i_)inst_jest for further processing.

Regards,

Raj

Message was edited by: Rajasekhar Dinavahi

[Former Member]

use se30 to analyse the problem in your code .

*copy/write your program name and press "execute"

after its finish .

• go back and press "analyse"

• press in the toolbar on hit list

• look for the part of the program that

is "net" time is over 10% .

• put your curser on the line and press

"display source code"

• after you fix it , run again , see if the time

that your program run is faster .

• also in the main window of se30 you have under

utilities "tips and tricks" .

[Former Member]

Hi,

1) Avoid select inside a Loop, try select for all entries.

2) Use read with Binary search.

[Former Member]

Although you have used binary searches almost everywhere, there is one place you haven't. Assuming the table is sorted correctly, try this:

READ TABLE i_jest
  INTO w_jest
  WITH KEY objnr     = w_iflot-objnr
           stat+0(1) = 'E'
           inact     = ' '
  <b>BINARY SEARCH</b>.

Rob