Improve perfomance code

[Former Member]

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.

     ENDIF.

   ENDLOOP.

   SORT i_report BY qnr posnr.

   DELETE ADJACENT DUPLICATES FROM i_report.

ENDFORM.                    " GET_DATA

Thank you in advance

[Former Member]

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.

[Former Member]

I don´t understand when you say

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

can you give an example with the code??

Thank you

[matt]

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.

[RaymondGiuseppi]

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

Ragards,

Raymond

[Former Member]

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.

       ENDIF.

     ENDIF.

   ENDLOOP.

   SORT i_report BY qnr posnr.

   DELETE ADJACENT DUPLICATES FROM i_report COMPARING qnr posnr.

ENDFORM.               

[ThomasZloch]

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

[Former Member]

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.

[Former Member]

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

[matt]

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

[matt]

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.