topic Re: Improve perfomance code in Application Development and Automation Discussions

Improve perfomance code

Former Member — Thu, 23 May 2013 09:30:48 GMT

Hi at all, someone could you help me to improve the performance of this piece of code??

*----------------------------------------------------*

* Internal table declaration

*----------------------------------------------------*

DATA: i_sqitem TYPE STANDARD TABLE OF

/wst/sqitem_mat_check_result INITIAL SIZE 0,

i_report TYPE STANDARD TABLE OF

/wst/sqitem_mat_check_result INITIAL SIZE 0,

i_items_quot TYPE STANDARD TABLE OF

/wst/sqitem_mat_check_result INITIAL SIZE 0,

li_matnr TYPE TABLE OF matnr WITH HEADER LINE.

RANGES: r_matnr FOR mara-matnr.

FORM get_data .

DATA:

lwa_i_sqitem LIKE LINE OF i_sqitem,

lwa_i_report LIKE LINE OF i_report,

lwa_i_items_quot LIKE LINE OF i_items_quot,

lw_matnr TYPE matnr.

SELECT qnr posnr matfrom matto FROM /wst/sqitem

INTO TABLE i_sqitem WHERE qnr IN s_qnr

AND matfrom NE space. "#EC CI_NOFIRST

LOOP AT i_sqitem INTO lwa_i_sqitem.

"for every quotation item check wether material is included

CALL FUNCTION '/WST/SQP_GET_MATERIALS'

EXPORTING

i_material_from = lwa_i_sqitem-matfrom

i_material_to = lwa_i_sqitem-matto

i_only_complete_matno = 'X'

TABLES

t_materials = li_matnr.

IF NOT li_matnr[] IS INITIAL.

REFRESH: r_matnr.

CLEAR i_items_quot.

LOOP AT li_matnr.

r_matnr-low = li_matnr.

r_matnr-sign = 'I'.

r_matnr-option = 'EQ'.

APPEND r_matnr.

ENDLOOP.

SELECT qnr posnr matfrom matto FROM /wst/sqitem

INTO TABLE i_items_quot WHERE qnr EQ lwa_i_sqitem-qnr

AND matfrom IN r_matnr AND posnr NE lwa_i_sqitem-posnr.

IF i_items_quot IS NOT INITIAL.

APPEND lwa_i_sqitem TO i_report.

LOOP AT i_items_quot INTO lwa_i_items_quot.

APPEND lwa_i_items_quot TO i_report.

ENDLOOP.

ENDIF.

ENDLOOP.

SORT i_report BY qnr posnr.

DELETE ADJACENT DUPLICATES FROM i_report.

ENDFORM. " GET_DATA

Thank you in advance

Re: Improve perfomance code

Former Member — Thu, 23 May 2013 09:43:34 GMT

Hi,

1. Please do not use NE in the where clause. Instead fetch all the entries and delete the entries from internal table with that criteria.

2. Always ensure to pass the key fields to the where clause if possible

3. Instead of using MATFROM In you can use for all entries which is more efficient. Point to note befor e using for all entries is to check whether there are entries in the internal table. Also it will be efficient if you remove duplicates from the internal table used in the query.

4. if possible, try to avoid querying inside a loop. Instead take all the entries in an internal table, use for all entries, loop at the entries and append the relevant entries to the final table.

Thanks and Regards,

Sriranjani Chimakurthy.

Re: Improve perfomance code

RaymondGiuseppi — Thu, 23 May 2013 09:50:37 GMT

Did you execute a performance analyzis via SAT/SE30 and a SQL trace via ST05. If yes, which step duration is longer ?

Ragards,

Raymond

Re: Improve perfomance code

Former Member — Thu, 23 May 2013 10:48:40 GMT

I don´t understand when you say

can you give an example with the code??

Thank you

Re: Improve perfomance code

Former Member — Fri, 24 May 2013 10:51:15 GMT

I have modified the program in this way but seems still slow...

FORM get_data .

DATA:

lwa_i_sqitem LIKE LINE OF i_sqitem,

lwa_i_sqitem1 LIKE LINE OF i_sqitem,

lwa_i_report LIKE LINE OF i_report,

lwa_i_items_quot LIKE LINE OF i_items_quot,

lwa_items_split LIKE LINE OF i_items_split,

lwa_items_split1 LIKE LINE OF i_items_split,

lw_matnr TYPE matnr,

lw_count TYPE i,

lw_ind TYPE sy-tabix.

DATA:

lwa_isqitem TYPE /wst/sqitem_mat_check_result,

lwa_ireport_tmp TYPE /wst/sqitem_mat_check_result.

FIELD-SYMBOLS: <lfs_sqitem> LIKE LINE OF i_sqitem,

<lfs_report_tmp> LIKE LINE OF i_report.

SELECT qnr posnr matfrom matto FROM /wst/sqitem

INTO CORRESPONDING FIELDS OF

TABLE i_sqitem WHERE qnr IN s_qnr.

LOOP AT i_sqitem ASSIGNING <lfs_sqitem>.

IF <lfs_sqitem>-matfrom NE space.

CALL FUNCTION '/WST/SQP_GET_MATERIALS'

EXPORTING

i_material_from = <lfs_sqitem>-matfrom

i_material_to = <lfs_sqitem>-matto

i_only_complete_matno = 'X'

TABLES

t_materials = i_matnr.

IF NOT i_matnr[] IS INITIAL.

REFRESH: i_items_split, i_report_tmp.

CLEAR lwa_isqitem.

MOVE <lfs_sqitem> TO lwa_isqitem.

CLEAR lwa_isqitem-matto.

LOOP AT i_matnr.

lwa_isqitem-matfrom = i_matnr.

APPEND lwa_isqitem TO i_items_split.

ENDLOOP.

SELECT qnr posnr matfrom matto FROM /wst/sqitem

INTO CORRESPONDING FIELDS OF TABLE i_report_tmp

FOR ALL ENTRIES IN i_items_split

WHERE qnr = i_items_split-qnr

AND matfrom = i_items_split-matfrom.

IF sy-subrc = 0.

LOOP AT i_report_tmp ASSIGNING <lfs_report_tmp>.

"if the position are equal means that is the same object

"that i try to find and in this case I don´t have to add

IF <lfs_report_tmp>-posnr NE <lfs_sqitem>-posnr.

APPEND <lfs_report_tmp> TO i_report.

APPEND <lfs_sqitem> TO i_report.

ENDIF.

ENDLOOP.

ENDIF.

ENDLOOP.

SORT i_report BY qnr posnr.

DELETE ADJACENT DUPLICATES FROM i_report COMPARING qnr posnr.

ENDFORM.

Re: Improve perfomance code

ThomasZloch — Fri, 24 May 2013 11:53:58 GMT

Please run a trace as mentioned by Raymond, or ST12 alternatively, to find out which line(s) really cause the slow response time. Anything else is just guessing. There are many "how-to" blogs for these transactions on SCN.

E.g. the problem might well be inside the called function module, also the number of outer loops will have a big impact, since you are doing additional loops and selects inside.

Thomas

Re: Improve perfomance code

Former Member — Fri, 28 Jun 2013 10:10:17 GMT

Hi,

kindly do the following changes,

1) Instead of INTO CORRESPONDING FIELDS OF TABLE use INTO TABLE.

2) Sort i_sqitem by matfrom matto.

3) since you are using select statement into the loop , its effecting the performance of the report

SELECT qnr posnr matfrom matto FROM /wst/sqitem

INTO CORRESPONDING FIELDS OF TABLE i_report_tmp

FOR ALL ENTRIES IN i_items_split

WHERE qnr = i_items_split-qnr

AND matfrom = i_items_split-matfrom.

remove the above select statement from the loop statement and first get all the values in i_items_split and den use

SELECT qnr posnr matfrom matto FROM /wst/sqitem

INTO CORRESPONDING FIELDS OF TABLE i_report_tmp

FOR ALL ENTRIES IN i_items_split

WHERE qnr = i_items_split-qnr

AND matfrom = i_items_split-matfrom.

Re: Improve perfomance code

Former Member — Fri, 30 Aug 2013 07:32:23 GMT

Hi,

Use sort statement every time before retrieving the records from the database and try to avoid using for all entries when the database table buffered swithed on.Use inner joins it will increase the performance.

Regards

khaleel

Re: Improve perfomance code

matt — Fri, 30 Aug 2013 08:28:33 GMT

Ignore that comment. FOR ALL ENTRIES shouldn't be used. Maybe an INNER JOIN.

Generally FOR ALL ENTRIES is less efficient than an INNER JOIN.

Re: Improve perfomance code

matt — Fri, 30 Aug 2013 08:29:36 GMT

Use an INNER JOIN rather than FOR ALL ENTRIES. It is more efficient.

Re: Improve perfomance code

matt — Fri, 30 Aug 2013 08:30:41 GMT

This is not correct. FAE works fine with buffered tables. However, INNER JOINS are preferable as the vast majority of the time they'll be faster.