Performance and loop statement

[Former Member]

Hello experts,

Below it's a BW user exit that i am using to extract data from ECC to BW.

An abap expert tells me that using select in loops is not good for performance.

I would like to enhance it but i don't know how?

If someone can give me ideas, that would be great.

DATA: s_data_cats TYPE zoxd780009.
     DATA: wa_hours TYPE catshours.
     LOOP AT c_t_data INTO s_data_cats.
 *     Get WBS Element POSID.
       SELECT SINGLE posid INTO s_data_cats-posid FROM prps
         WHERE pspnr = s_data_cats-rproj.
       IF s_data_cats-refcounter IS NOT INITIAL.
         SELECT SINGLE catshours FROM catsdb INTO wa_hours WHERE counter = s_data_cats-refcounter.
         IF sy-subrc = 0.
           s_data_cats-catshours = s_data_cats-catshours - wa_hours.
         ENDIF.
       ENDIF.
       MODIFY c_t_data FROM s_data_cats .
       CLEAR s_data_cats.
     ENDLOOP.

Amine

[Former Member]

Hi Amine,

u r right. Select statement inside loops is going to reduce the performance. U might want to try the below steps:

1. Populate the data into internal table ITAB2 ( lets say) using select outside the loop ITAB1 ( say )

2. within loop of ITAB1, use Read statement using binary search with condition ( WHERE pspnr = s_data_cats-rproj.  )

It's going to work the same thing as the Select Single is doing above.

3. Do more of processing in internal tables rather than using Select multiple times.

I've highlighted the things you need to take care for performance. U will need to declare internal table structures based on your requirement.

Also perform EPC checks.

Select query to populate data in C_T_DATA.

SELECT posid into table i_posid

          from prps

         for all entries in c_t_data

          where posid = c_t_data-posid.

1. LOOP AT c_t_data INTO s_data_cats where posid = . 
2.  
3. *     Get WBS Element POSID. 
4. read table i_posid into wa1 with key pspnt = s_data_cats-rproj with binary search.
5.  
6.        IF s_data_cats-refcounter IS NOT INITIAL. 
7.  
8.          read table i_catshours into wa with key counter = s_data_cats-refcounter binary search. 

Thanks

Vivek

[adam_krawczyk1]

Hi Amine,

Vivek has already mentioned good points for improvements.

If you have loop and inside single select statements, then if table that you loop over contains 1000 rows, you will query database 1000 times with small queries about single values, or even 2000 times if you have 2 select statements inside loop.

Instead in your example you can ask just twice, using FOR ALL ENTRIES query. This is statement that reduces number of queries to database, already optimized query. Here is the example:

DATA lt_prps_posid TYPE TABLE OF prps-posid.

DATA lt_catsdb_catshours TYPE TABLE OF catsdb-catshours.

     SELECT DISTINCT posid FROM prps

        INTO TABLE lt_prps_posid

          FOR ALL ENTRIES in c_t_data

            WHERE pspnr = c_t_data-rproj.

     SELECT DISTINCT catshours FROM catsdb

        INTO TABLE lt_catsdb_catshours

          FOR ALL ENTRIES IN c_t_data

            WHERE counter IS NOT NULL AND  counter = c_t_data-refcounter.

Then you have all database data inside internal tables. So you construct loop simillar to what you have already, and replace selects with binary reads from internal tables as Vivek proposed. It is important to use binary search for large data, it will optimize performance significantly. Keep it mind, that table should be sorted first if you want to use binary search.

There is one thing that you should be aware of: FOR ALL ENTRIES IN does not offer same logic as separated SELECT SINGLE, where only single value is found and it is much faster than SELECT FROM without SINGLE addition. There is UNIQUE statement in fact, but it is not the same, as results are merged to unique at the end of statement processing, on database side. That is why sometimes, by experience, separated LOOP AT with SELECT SINGLE may be faster, but it depends on data distribution and tables size.

Hope this helps

Regards,

Adam

[edgar_nagasaki]

Hi Amine,

One of most importants when talking about performance improvement is to reduce I/O as much as possible. In your example, where you have single data reading (SELECT) whenever program loops, you would have many single inputs (costly). The best would be to select all needed data at once and then, inside your loop, to work only with internal tables (would replace SELECT by READ TABLE, much less costly). Also about internal tables don't forget to use BINARY SEARCH option while reading it (first SORT table by key you will use when do READ) specialy if this internal table has a reasonable amount of data (what also could increase a lot the overall processing time).

Edgar

[bishwajit_das]

Hi Amine,

I am sending you this code, just copy and try.

DATA: s_data_cats TYPE zoxd780009,

           wa_hours TYPE catshours,

           it_prps TYPE STANDARD TABLE OF prps INITIAL SIZE 0,

           wa_prps TYPE prps,

           it_catsdb TYPE STANDARD TABLE OF catsdb INITIAL SIZE 0,

           wa_catsdb TYPE catsdb.

SELECT * FROM prps INTO it_prps FOR ALL ENTRIES IN c_t_data

WHERE pspnr = c_t_data-rproj.

SELECT * FROM catsdb INTO it_catsdbs FOR ALL ENTRIES IN c_t_data

WHERE counter = c_t_data-refcounter.

LOOP AT c_t_data INTO s_data_cats.   

*     Get WBS Element POSID. 

READ TABLE it_prps into wa_prps with key pspnr = s_data_cats-rproj.

if sy-subrc = 0.

     s_data_cats-posid = wa_prps-posid.

     IF s_data_cats-refcounter IS NOT INITIAL. 

     READ TABLE it_catsdb INTO wa_catsdb WITH KEY counter = s_data_cats-refcounter.

     IF SY_SUBRC = 0.

          wa_hours = wa_catsdb-catshours.

          s_data_cats-catshours = s_data_cats-catshours - wa_hours. 

     ENDIF. 

ENDIF. 

MODIFY c_t_data FROM s_data_cats . 

CLEAR s_data_cats. 

ENDLOOP. 

Wish it will solve the problem increase your performance.

Regards,

Bishwajit.

[adam_krawczyk1]

Hi Amine and Bishwajit,

I see that solution proposed by Bishwajit is close to final and you have full code example provided. I would modify SELECT * to single fields selection (SELECT pspnr) as for example prps table has 136 columns and reading all of them is not needed. Also we should use sorted table, if more than 100 rows will be processed - and I assume as it is the situation as this is BW extractor code.

I extended my example and merged with Amine initial solution and here is final optimized result that I would recommend. It uses standard tables of single columns and then just sorts tables once (that is why I do not use sorted table, where each insert must be in sorted order, but it is small memory optimization, sorted table would give also good results).

DATA lt_prps_posid TYPE TABLE OF prps-posid.

DATA lt_catsdb_catshours TYPE TABLE OF catsdb-catshours.

DATA l_posid TYPE prps-posid.

DATA l_catshours TYPE catsdb-catshours.

  SELECT DISTINCT posid FROM prps

       INTO TABLE lt_prps_posid

            FOR ALL ENTRIES IN c_t_data

                 WHERE pspnr = c_t_data-rproj.

  SELECT DISTINCT catshours FROM catsdb

        INTO TABLE lt_catsdb_catshours

             FOR ALL ENTRIES IN c_t_data

                  WHERE counter IS NOT NULL AND  counter = c_t_data-refcounter.

  SORT lt_prps_posid by table_line.

  SORT lt_catsdb_catshours by table_line.

  LOOP AT c_t_data INTO s_data_cats.

     "     Get WBS Element POSID.

     "SELECT SINGLE posid INTO s_data_cats-posid FROM prps

     " WHERE pspnr = s_data_cats-rproj.

    READ TABLE lt_prps_posid WITH KEY table_line = s_data_cats-rproj

          INTO l_posid binary search.

    IF s_data_cats-refcounter IS NOT INITIAL.

          "SELECT SINGLE catshours FROM catsdb INTO wa_hours

          " WHERE counter = s_data_cats-refcounter.

          READ TABLE lt_catsdb_catshours

               WITH KEY table_line = s_data_cats-refcounter INTO l_catshours binary search.     

         IF sy-subrc = 0.       

              s_data_cats-catshours = s_data_cats-catshours - wa_hours.

         ENDIF.

    ENDIF.

    MODIFY c_t_data FROM s_data_cats .

    CLEAR s_data_cats.

  ENDLOOP.

As you see - data is fetched from database only once. Then code is working on internal tables - operating on memory is much faster. Sorted table give very quick reading by key.

Regards,

Adam

[Former Member]

Hai,

       Fill the required table before the loop starts and use read statement inside the loop.

Thanks & Regards,

Ramu Velaga.

[Former Member]

Thanks to all for your help.

I understand more better the functionning of Abap thanks to your explanations.

Amine