topic Re: Performance Issue in Application Development and Automation Discussions

Performance Issue

Former Member — Tue, 24 Oct 2006 05:03:11 GMT

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.

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.

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.

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.

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.

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

Re: Performance Issue

Former Member — Tue, 24 Oct 2006 06:04:35 GMT

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

Re: Performance Issue

Former Member — Tue, 24 Oct 2006 06:15:02 GMT

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

Re: Performance Issue

Former Member — Tue, 24 Oct 2006 06:30:29 GMT

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^

Re: Performance Issue

Former Member — Tue, 24 Oct 2006 06:33:54 GMT

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

Re: Performance Issue

Former Member — Tue, 24 Oct 2006 06:57:38 GMT

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.

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

Re: Performance Issue

Former Member — Tue, 24 Oct 2006 07:04:10 GMT

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

Re: Performance Issue

Former Member — Tue, 24 Oct 2006 07:48:13 GMT

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

Re: Performance Issue

Former Member — Tue, 24 Oct 2006 07:59:16 GMT

Hi,

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

2) Use read with Binary search.

Re: Performance Issue

Former Member — Tue, 24 Oct 2006 08:13:32 GMT

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.

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

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

Re: Performance Issue

Former Member — Tue, 24 Oct 2006 11:32:24 GMT

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

Re: Performance Issue

Former Member — Tue, 24 Oct 2006 13:48:07 GMT

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

Re: Performance Issue

Former Member — Tue, 24 Oct 2006 13:56:43 GMT

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.