Performance Issues

[Former Member]

Hello all,

I have couple of questions regarding performance tuning of applications.

1. Which of the following is better from performance point of view.

a) SELECT SINGLE ... inside a LOOP or

b) SELECT XXX. YYYY INTO TABLE ZZZ FROM table... and then READ TABLE zzz ... inside a LOOP.

2. I am planning to discuss with basis guys over creating a new index with all the fields used in the WHERE clause. Will creating a new index be productive????

And one more question. When I did ST05 on my program I got a trace list. I displayed the trace list by SQL statements. But it is not clear which SELECT on which TABLE is taking more time. I will appreciate if someone throw little insight on working with trace list.

Thanks,

Somen

[thomas_jung]

It really depends I think. In your first case option b. is probably better, especially if you are going to end up getting just about all items in the main select that you would have gotten in the select single by the end of processing. As long as it doesn't force you to have to read a bunch of extra data, one big select is more efficent than many single selects. Now on your read table, you can get even better response if you sort by your reading keys and do a <i>read binary search</i>.

Item 2: This really depends. It depends upon the table layout, the number of fields, the size of the table, the amount of updating done to the table. You really need to analyse each situation. I have seen good indexes have a great positive effect. I have also seen the opposite.

Finally from the SQL trace, I like to compress my trace to see the big hitters. In 640 use menu option Trace List->Summarize Trace by SQL Statement (or Shift+F8). This should give you a nice ranked list and point out any problems.

[juan_suros]

1. I sometimes find another approach is better. Inside a loop, Use <b>READ TABLE itab</b> first and then <b>SELECT SINGLE zzz APPENDING itab</b> each time you don't find the record in the itab. Using this strategy, multiple calls for the same record are very fast and itab remains only as big as it has to be.

2. Spend some time in SE11 making sure you understand which indexes are already defined for your table(s) of interest. Sometimes you can expand the WHERE clause using the IN and/or EXISTS operators to use existing indexes efficiently. I like to define RANGES objects with two or three values for use in WHERE clauses. Like so:

RANGES: r_key2 FOR table-key2.

r_key2-sign = 'I'.
r_key2-option = 'EQ'.
r_key2-low = 'value1'. APPEND r_key2.
r_key2-low = 'value2'. APPEND r_key2.
r_key2-low = 'value3'. APPEND r_key2.

SELECT * FROM table
WHERE key1 EQ ' '
  AND key2 IN r_key2
  AND field IN S_SelectOption.
* Do stuff here
ENDSELECT.

In this example, I happen to know that the first key field is always empty for my data of interest and the second key field could be any of three values. Because it uses the primary key index of the table to speed up the query, this code could be much faster than the standard approach:

SELECT * FROM table WHERE field IN S_SelectOption.
* Do stuff here
ENDSELECT.

These performance improvements can come without any index changes at all. Experiment a bit, it's worth it.

[Former Member]

Here I am pasting some part of the code where the execution time is more.

The SELECT using JOIN

<b>select a_{vbeln c}lifsk c_{kunnr e}kunnr c_{vsbed c}audat

c_{bstnk c}submi c_{waerk c}knumv

a_{posnr a}matnr a_{kwmeng a}vrkme

a~kdmat

d_{edatu a}vstel a_{mfrgr a}abgru a_{pstyv a}vkaus

a~posex

into table idocs

from vbap as a join vbuk as b on

( a_{vbeln = b}vbeln )

join vbak as c on

( a_{vbeln = c}vbeln )

join vbep as d on

( a_{vbeln = d}vbeln and

a_{posnr = d}posnr )

join vbpa as e on

( a_{vbeln = e}vbeln )

where a~vbeln in so_vbeln

and c~submi in so_submi

and c~audat in so_audat

and c~erdat in so_erdat

and c~vbtyp = 'H'

and c~bstnk in so_bstnk

and c~lifsk in so_lifsk

and c~auart in so_auart

and c~kunnr in so_kunnr

and e~posnr = '000000'

and e~kunnr in so_ship2

and e~parvw = 'WE'

and a~matnr in so_matnr

and a~route in so_route

and d~etenr = '0001'. " Always pick first schedule line entry</b>

And other part of the code where I am facing problem is here.

<b>

loop at isel into ssel.

at new vbeln.

clear: header, headerx, new_kunnr, new_ship2, logsw, condup,

head_err, con1_err, con2_err.

clear int_num_assign. "DM01

refresh: ireturn, iitem, iitemx, isched, ischedx, icond, icondx.

headerx-updateflag = 'U'.

• If a header condition is being added, first see if it already

• exists.

if not check_condh is initial.

• See if the condition exists on the header

• Get VBAK condition number. We can't join because KONV is a

• pooled table.

• select single knumv waerk into (knumv, waerk)

• from vbak

• where vbeln = ssel-vbeln.

• See if the condition exists at the header in KONV.

select single kschl into kschl

from konv

where knumv = ssel-knumv

and kposn = '000000'

and kschl = zmass_order-kschlh.

• If found, do not update.

if sy-subrc = 0.

head_err = 'X'.

else.

• See if any items are delivered.

select single vbeln into lips-vbeln

from lips

where vgbel = ssel-vbeln.

if sy-subrc = 0.

• See if all items are billed

select single c~fksta into fksta

from lips as a join vbap as b on

( a_{vgbel = b}vbeln and

a_{vgpos = b}posnr )

join vbup as c on

( a_{vbeln = c}vbeln and

a_{posnr = c}posnr )

where b~vbeln = ssel-vbeln

and c~fksta <> 'C'.

if sy-subrc ne 0.

head_err = 'Y'.

endif.

endif.

endif.

• If both tests passed.

if head_err is initial.

kbetr_out = zmass_order-kbetrh.

read table it685a with key kschl = zmass_order-kschlh.

if it685a-krech ca 'AHI'.

• multiply kbetr_out by 10.

else.

icond-currency = waerk.

endif.

icond-itm_number = '000000'.

icond-cond_type = zmass_order-kschlh.

icond-cond_value = kbetr_out.

icond-applicatio = 'V'.

icondx-itm_number = '000000'.

icondx-cond_type = zmass_order-kschlh.

icondx-cond_value = 'X'.

icondx-currency = 'X'.

icondx-updateflag = 'I'.

append: icond, icondx.

endif.

endif.

endat.

clear: ireturn, iitem, iitemx, isched, ischedx, icond, icondx,

refresh_vas.

• Set fields for update

if check_kunnr = 'X'.

new_kunnr = ssel-kunnr.

endif.

if check_ship2 = 'X'.

new_ship2 = ssel-ship2.

endif.

if ssel-revas = 'X'.

refresh_vas = 'X'.

endif.

• Check change to delivery block.

if check_lifsk = 'X'.

header-dlv_block = ssel-lifsk.

headerx-dlv_block = 'X'.

endif.

• Check change to pricing date

if check_prsdt = 'X'.

header-price_date = ssel-prsdt.

headerx-price_date = 'X'.

endif.

• Check change to shipping condition

if check_vsbed = 'X'.

header-ship_cond = ssel-vsbed.

headerx-ship_cond = 'X'.

endif.

• Check change to document date

if check_audat = 'X'.

header-doc_date = ssel-audat.

headerx-doc_date = 'X'.

endif.

• Check change to purchase order

if check_bstnk = 'X'.

header-purch_no_c = ssel-bstnk.

headerx-purch_no_c = 'X'.

endif.

if p_item eq 'X'.

• Check change to material

if check_matnr = 'X'.

*>> Begin Insert "DM02

• Get the Material on the SO Item being changed

• If it is the same material as the one entered on the change

• screen the item will be deleted and re-inserted. This is done

• because it is assumed that the material master data has been

• updated and that it needs to be refreshed on the order. The

• only way to do this is to add the material as a new item

clear old_matnr.

select single matnr into old_matnr

from vbap

where vbeln = ssel-vbeln

and posnr = ssel-posnr.

*<< End Insert "DM02

• Check to see if either the original material or the new one is a

• BOM header. If so, delete the original and add a new one. This

• will prevent errors during the BOM explosion if the BOM has more

• than 10 items.

clear izsd17.

read table izsd17 with key pstyv = ssel-pstyv.

• Check for the new material in MAST (BOM header)

clear mast.

• select single matnr into mast-matnr "DM01

select single matnr stlan "DM01

into (mast-matnr, mast-stlan) "DM01

from mast

where matnr = ssel-matnr.

• if izsd17-mtype ne 'S' or not mast-matnr is initial. "DM01

if izsd17-mtype ne 'S' or "DM01

mast-stlan = '5' or mast-stlan = '3' "DM01

or ssel-matnr = old_matnr. "DM02

iitem-itm_number = ssel-posnr.

iitem-material = ssel-matnr.

iitemx-itm_number = ssel-posnr.

iitemx-updateflag = 'D'.

append: iitemx, iitem.

• clear: iitem-itm_number, iitemx-itm_number. "DM01

iitem-itm_number = ssel-posnr + 1. "DM01

iitem-material = ssel-matnr.

iitemx-itm_number = iitem-itm_number. "DM01

iitemx-updateflag = 'I'.

iitemx-material = 'X'.

isched-itm_number = iitem-itm_number. "DM01

isched-sched_line = '0001'.

isched-req_qty = ssel-kwmeng.

ischedx-itm_number = isched-itm_number. "DM01

ischedx-sched_line = '0001'.

ischedx-req_qty = 'X'.

ischedx-updateflag = 'I'.

int_num_assign = 'X'. "DM01

else.

iitem-itm_number = ssel-posnr.

iitem-material = ssel-matnr.

iitemx-itm_number = ssel-posnr.

iitemx-updateflag = 'U'.

iitemx-material = 'X'.

endif.

endif.

• Check change to item category

if check_pstyv = 'X'.

if iitemx-updateflag = 'I'." SM01+

iitem-item_categ = ssel-pstyv.

iitemx-item_categ = 'X'.

else." SM01+

clear izsd17.

read table izsd17 with key pstyv = ssel-pstyv.

• Check for the new material in MAST (BOM header)

clear mast.

• select single matnr into mast-matnr "DM01

select single matnr stlan "DM01

into (mast-matnr, mast-stlan) "DM01

from mast

where matnr = ssel-matnr.

• if izsd17-mtype ne 'S' or not mast-matnr is initial. "DM01

if izsd17-mtype ne 'S' or "DM01

mast-stlan = '5' or mast-stlan = '3'. "DM01

• Delete Item

iitem-itm_number = ssel-posnr.

iitemx-itm_number = ssel-posnr.

iitemx-updateflag = 'D'.

append: iitem, iitemx.

• Insert Item back with updated Item Category

iitem-itm_number = ssel-posnr + 1. "DM01

iitem-material = ssel-matnr.

iitem-cust_mat35 = ssel-kdmat. "DM01

iitem-item_categ = ssel-pstyv.

iitemx-itm_number = iitem-itm_number. "DM01

iitemx-updateflag = 'I'.

iitemx-item_categ = 'X'.

iitemx-material = 'X'.

iitemx-cust_mat35 = 'X'. "DM01

isched-itm_number = iitem-itm_number. "DM01

isched-sched_line = '0001'.

isched-req_qty = ssel-kwmeng.

ischedx-itm_number = isched-itm_number. "DM01

ischedx-sched_line = isched-sched_line.

ischedx-req_qty = 'X'.

ischedx-updateflag = 'I'.

int_num_assign = 'X'. "DM01

else.

iitem-itm_number = ssel-posnr.

iitem-item_categ = ssel-pstyv.

iitemx-itm_number = ssel-posnr.

if iitemx-updateflag is initial.

iitemx-updateflag = 'U'.

endif.

iitemx-item_categ = 'X'.

endif.

endif." SM01+

endif.

• Check change to item delivery date.

if check_edatu = 'X'.

if ischedx-updateflag <> 'I'." SM01+

isched-itm_number = ssel-posnr.

ischedx-itm_number = ssel-posnr.

endif." SM01+

isched-sched_line = '0001'.

isched-req_date = ssel-edatu.

ischedx-sched_line = '0001'.

ischedx-req_date = 'X'.

if ischedx-updateflag is initial.

ischedx-updateflag = 'U'.

endif.

endif.

• Check change to ship point

if check_vstel = 'X'.

if iitemx-updateflag <> 'I'." SM01+

iitem-itm_number = ssel-posnr.

iitemx-itm_number = ssel-posnr.

endif." SM01+

iitem-ship_point = ssel-vstel.

if iitemx-updateflag is initial.

iitemx-updateflag = 'U'.

endif.

iitemx-ship_point = 'X'.

endif.

• Check change to rejection reason

if check_abgru = 'X'.

if iitemx-updateflag <> 'I'." SM01+

iitem-itm_number = ssel-posnr.

iitemx-itm_number = ssel-posnr.

endif." SM01+

iitem-reason_rej = ssel-abgru.

if iitemx-updateflag is initial.

iitemx-updateflag = 'U'.

endif.

iitemx-reason_rej = 'X'.

endif.

• Check change to item usage

if check_vkaus = 'X'.

if iitemx-updateflag <> 'I'." SM01+

iitem-itm_number = ssel-posnr.

iitemx-itm_number = ssel-posnr.

endif." SM01+

iitem-usage_ind = ssel-vkaus.

if iitemx-updateflag is initial.

iitemx-updateflag = 'U'.

endif.

iitemx-usage_ind = 'X'.

endif.

• Check change to item order reason

if check_posex = 'X'.

if iitemx-updateflag <> 'I'." SM01+

iitem-itm_number = ssel-posnr.

iitemx-itm_number = ssel-posnr.

endif." SM01+

iitem-po_itm_no = ssel-posex.

if iitemx-updateflag is initial.

iitemx-updateflag = 'U'.

endif.

iitemx-po_itm_no = 'X'.

endif.

• Check change to qty.

if check_qty = 'X'.

if ischedx-updateflag <> 'I'." SM01+

isched-itm_number = ssel-posnr.

ischedx-itm_number = ssel-posnr.

endif." SM01+

isched-sched_line = '0001'.

isched-req_qty = ssel-kwmeng.

ischedx-sched_line = '0001'.

ischedx-req_qty = 'X'.

if ischedx-updateflag is initial.

ischedx-updateflag = 'U'.

endif.

endif.

• Delete the item if marked.

if ssel-delet = 'X'.

iitem-itm_number = ssel-posnr.

iitemx-updateflag = 'D'.

iitemx-itm_number = ssel-posnr.

endif.

• Determine item billing status.

if not check_cond1 is initial or not check_cond2 is initial.

clear fksta.

• Look for any delivery of the item that has not been completely

• billed. There may be multiple deliveries, so we're looking for

• anything that's not a 'C'.

• See if the item has been delivered.

select single vbeln into lips-vbeln

from lips

where vgbel = ssel-vbeln

and vgpos = ssel-posnr.

• If delivered, check billing status.

if sy-subrc = 0.

select single c~fksta into fksta

from lips as a join vbap as b on

( a_{vgbel = b}vbeln and

a_{vgpos = b}posnr )

join vbup as c on

( a_{vbeln = c}vbeln and

a_{posnr = c}posnr )

where b~vbeln = ssel-vbeln

and b~posnr = ssel-posnr

and c~fksta <> 'C'.

if sy-subrc ne 0.

condup = 'X'.

endif.

else.

• The document has no deliveries, so set the billing status to

• incomplete.

fksta = 'A'.

endif.

endif.

• Add condition types if specified. For condition types with KRECH =

• A, H, or I, the condition type is a percentage. The BAPI translates

• these values incorrectly, so multiply the input by 10 to correct it.

if not check_cond1 is initial and not fksta is initial.

• See if the condition exists on the item

• Get VBAK condition number. We can't join because KONV is a

• pooled table.

• select single knumv waerk into (knumv, waerk)

• from vbak

• where vbeln = ssel-vbeln.

• See if the condition exists at the header in KONV.

select single kschl into kschl

from konv

where knumv = ssel-knumv

and kposn = ssel-posnr

and kschl = zmass_order-kschl1.

• If found, do not update.

if sy-subrc = 0.

con1_err = 'X'.

else.

kbetr_out = zmass_order-kbetr1.

read table it685a with key kschl = zmass_order-kschl1.

if it685a-krech ca 'AHI'.

• multiply kbetr_out by 10.

else.

icond-currency = ssel-waerk.

endif.

icond-itm_number = ssel-posnr.

icond-cond_type = zmass_order-kschl1.

icond-cond_value = kbetr_out.

icondx-itm_number = ssel-posnr.

icondx-cond_type = zmass_order-kschl1.

icondx-cond_value = 'X'.

icondx-currency = 'X'.

icondx-updateflag = 'I'.

append: icond, icondx.

endif.

endif.

if not check_cond2 is initial and not fksta is initial.

• See if the condition exists on the item

• Get VBAK condition number. We can't join because KONV is a

• pooled table.

• select single knumv waerk into (knumv, waerk)

• from vbak

• where vbeln = ssel-vbeln.

• See if the condition exists at the header in KONV.

select single kschl into kschl

from konv

where knumv = ssel-knumv

and kposn = ssel-posnr

and kschl = zmass_order-kschl2.

• If found, do not update.

if sy-subrc = 0.

con2_err = 'X'.

else.

kbetr_out = zmass_order-kbetr2.

read table it685a with key kschl = zmass_order-kschl2.

if it685a-krech ca 'AHI'.

• multiply kbetr_out by 10.

else.

icond-currency = ssel-waerk.

endif.

icond-itm_number = ssel-posnr.

icond-cond_type = zmass_order-kschl2.

icond-cond_value = kbetr_out.

icondx-itm_number = ssel-posnr.

icondx-cond_type = zmass_order-kschl2.

icondx-cond_value = 'X'.

icondx-currency = 'X'.

icondx-updateflag = 'I'.

append: icond, icondx.

endif.

endif.

endif.

• If the header rows have data for item and schedule line, append

• to the tables.

if not iitem is initial.

append: iitem.

endif.

if not iitemx is initial.

append: iitemx.

endif.

if not isched is initial.

append: isched, ischedx.

endif.

• At the last line for the sales document, call the BAPI.

at end of vbeln.

call function 'BAPI_SALESORDER_CHANGE'

exporting

salesdocument = ssel-vbeln

order_header_in = header

order_header_inx = headerx

logic_switch = logsw

INT_NUMBER_ASSIGNMENT = int_num_assign "DM01

tables

return = ireturn

order_item_in = iitem

order_item_inx = iitemx

schedule_lines = isched

schedule_linesx = ischedx

conditions_in = icond

conditions_inx = icondx.

call function 'BAPI_TRANSACTION_COMMIT'.

• Update the log from the BAPI call.

perform c010_update_log_from_bapi tables ireturn

using ssel-vbeln.

if condup = 'X'.

ilog-status = 'Warning'.

ilog-msg =

'Not all conditions were updated due to completed billings.'.

append ilog.

endif.

if head_err = 'X'.

ilog-status = 'Warning'.

ilog-msg =

'Header pricing condition not updated as it already exists.'.

append ilog.

endif.

if head_err = 'Y'.

ilog-status = 'Warning'.

ilog-msg =

'Header pricing condition not updated as all items are billed.'.

append ilog.

endif.

if con1_err = 'X'.

ilog-status = 'Warning'.

ilog-msg =

'Item pricing condition 1 not updated as it already exists.'.

append ilog.

endif.

if con2_err = 'X'.

ilog-status = 'Warning'.

ilog-msg =

'Item pricing condition 2 not updated as it already exists.'.

append ilog.

endif.

call function 'DEQUEUE_ALL'.

endat.

endloop.

endform. " b020_udpate_documents</b>

When I did an SQL trace on this application the most time taking operations are

1. Dynpro entry

2. LDB processing

3. PAI module (2nd part of the code is part of PAI module)

Can any one please explain me what Dynpro entry and LDB processing is??? It would be great if you point me to some documentation over this where I can find details.

Thanks,

Somen

[Former Member]

The above two parts are eating 90% of the execution time. Please suggest what changes can be still to those two parts.

Thanks,

Somen

[Former Member]

Hi,

SELECT XXX. YYYY INTO TABLE ZZZ FROM table... and then READ TABLE zzz ... inside a LOOP.

U can use this select query, this will reduce execution time, Using Select inside a loop is not recommended, Because it will hit the database as many times the loop gets executed.

Hope I have cleared ur first question.

Thanks & Regards,

Judith

[Former Member]

Hi Naren,

I suggest you to change the order of the tables in the join condition.

vbak join vbap

on ( vbeln ... )

join vbep

join vbpa

join vbuk

Regards,

Erwan.