Help to improve code and performance

[Former Member]

HI Experts ,

I facing an perfomance probelm With my program and i want your help to give me

Ideas how to do that in better way .

have table with records in DB which i need to modify from internal table via job.

1. if we start from scratch (fill the db table in the first time ) all the records should be valid (column valid mark as abap_true)

2.next run if user new data (Roles ) will arrive to the table the records should be valid automatically

3. if some records are not occur in the next run of the job the record in the table should be not valid (One role for user or if user user is not exist in itab table job all the roles should

be marked as invalid )

How can I improve my code the KPI is for 100000 users .

I give Simple example

in the first run i have users in the itab ( Job that update the db)

users roles valid_flag

user1 rol1 X

user1 rol2 X

user1 rol3 X

user2 rol1 X

user2 rol2 X

user2 rol3 X

This what I have in the internal table and what should be in the db table also .

Assume that the job in the next run I have in the itab that update the table

user1 rol1 X

user1 rol2 X

user1 rol4 X

user2 rol1 X

user2 rol5 X

user2 rol6 X

user3 rol1 X

user3 rol5 X

user3 rol3 X

so after the following run the DB table should look like .

users roles valid_flag

user1 rol1 X

user1 rol2 X

user1 rol3

user1 rol4 X

user2 rol1 X

user2 rol2

user2 rol3

user2 rol5 X

user2 rol6 X

user3 rol1 X

user3 rol5 X

user3 rol3 X

The old records are not deleted there are just mark as not valid and if we have new records

they are inserted to the table with valid flag .

Following post I share my code its working but since i new to abap i think that i can use your advice

maybe to do it in simple way ,keep in mind that its not that simple since there is some edge case in this issue .

Regards

James

[Former Member]

HI ,

This is my code i do it in chunk for 100 users since i dont want to upload the memory all the table content of the table

DO.
  APPEND LINES OF it_unq_bnames
  FROM lv_from_index TO lv_to_index TO lt_chunk_table.
  IF lt_chunk_table IS INITIAL .
    EXIT.
  ENDIF.
*---------
  SELECT *
  FROM ztst_rs_data
  INTO TABLE lt_copy_db
  FOR ALL ENTRIES IN lt_chunk_table
  WHERE bname = lt_chunk_table-bname.

  IF sy-subrc = 0."The entry already exist in the DB
*    Check if there is no new user in the table .
    LOOP AT lt_chunk_table INTO ls_chunk_table.
      READ TABLE lt_copy_db ASSIGNING <fs_copy_db> WITH KEY bname = ls_chunk_table-bname.
      IF sy-subrc NE 0.
        LOOP AT it_rs_final_usrs_data ASSIGNING <fs_rs_finl>
                 WHERE bname = ls_chunk_table-bname.

          MOVE-CORRESPONDING <fs_rs_finl> TO ls_modif_rs.
          APPEND ls_modif_rs TO lt_modif_rs.
        ENDLOOP.
      ENDIF.
    ENDLOOP.
*   Update the old records


    LOOP AT lt_chunk_table INTO ls_chunk_table.
      LOOP AT it_rs_final_usrs_data ASSIGNING <fs_rs_finl> WHERE bname = ls_chunk_table-bname.
        READ TABLE lt_copy_db ASSIGNING <fs_copy_db> WITH KEY
           bname = <fs_rs_finl>-bname
           extid = <fs_rs_finl>-extid
           agr_name = <fs_rs_finl>-agr_name
           consumer_type = <fs_rs_finl>-consumer_type.

        IF sy-subrc NE 0.
          MOVE-CORRESPONDING <fs_rs_finl> TO ls_modif_rs.
*        ls_modif_rs-valid = abap_false.
          APPEND ls_modif_rs TO lt_modif_rs.
        ENDIF.
      ENDLOOP.
    ENDLOOP.

  ELSE.
    APPEND LINES OF lt_chunk_table TO lt_modif_rs."New users
  ENDIF.

  MODIFY zt_rs_data FROM TABLE lt_modif_rs.
*---------Update indexs
  lv_from_index = lv_to_index + 1.
  lv_to_index = lv_to_index + lc_size.
  CLEAR lt_chunk_table.
ENDDO.

Edited by: James Herb on Jan 14, 2010 10:46 AM

[Former Member]

Hi James,

There are two areas where most of the time is lost.

1. Loop inside Loop

2. Read Table.

1. Loop inside Loop: That we have to analyze in our own scenario and we can be the best judge whether it is required or not.

2. Read Table;

In your case, use Read Table BINARY SEARCH. (It will DRASTICALLY Improve the performance).

Before Read Table, Make sure the internal table is sorted by USER and ROLE. (as per your example)

regards,

amit m.

[Former Member]

HI Amit ,

Yes i know that this not good but there is a way to avoid that in my case ?

Regards

James

Edited by: James Herb on Jan 14, 2010 12:55 PM

[Former Member]

Hi again,

There is one point where it can be improved (There are other points also, which I am analyzing in the mean time).

See the changes below with @@@@@

*"@@@@@@  First Sort immediately after your select.
SORT lt_copy_db by bname   extid  agr_name  consumer_type


F sy-subrc = 0."The entry already exist in the DB
*    Check if there is no new user in the table .
    LOOP AT lt_chunk_table INTO ls_chunk_table.
      READ TABLE lt_copy_db ASSIGNING <fs_copy_db> WITH KEY bname = ls_chunk_table-bname.
*"@@@@@@  add the suffix binary search
    BINARY SEARCH.

      IF sy-subrc NE 0.
        LOOP AT it_rs_final_usrs_data ASSIGNING <fs_rs_finl>
                 WHERE bname = ls_chunk_table-bname.
 
          MOVE-CORRESPONDING <fs_rs_finl> TO ls_modif_rs.
          APPEND ls_modif_rs TO lt_modif_rs.
        ENDLOOP.
      ENDIF.
    ENDLOOP.
*   Update the old records
 

I am quite sure this will improve the performance. Can you please check if you find feasible.

Regards,

Amit Mittal.

[Former Member]

HI Amit ,

Thanks a lot for your help expert .

This improve my performance but not dramatically

From your point of view did you do that different (all the code) ? for this kind of problem ?

Regards

James

Edited by: James Herb on Jan 14, 2010 1:56 PM

[Former Member]

Hi again,

Similarly, use the same concept in the 2nd part of the logic.

See @@@@

 LOOP AT lt_chunk_table INTO ls_chunk_table.
      LOOP AT it_rs_final_usrs_data ASSIGNING <fs_rs_finl> WHERE bname = ls_chunk_table-bname.
        READ TABLE lt_copy_db ASSIGNING <fs_copy_db> WITH KEY
           bname = <fs_rs_finl>-bname
           extid = <fs_rs_finl>-extid
           agr_name = <fs_rs_finl>-agr_name
           consumer_type = <fs_rs_finl>-consumer_type
*"@@@@@@@@@@ 
     BINARY SEARCH.
 
        IF sy-subrc NE 0.
          MOVE-CORRESPONDING <fs_rs_finl> TO ls_modif_rs.
*        ls_modif_rs-valid = abap_false.
          APPEND ls_modif_rs TO lt_modif_rs.
        ENDIF.
      ENDLOOP.
    ENDLOOP.

regards,

amit m.

[Former Member]

HI Amit ,

This improvement is little since the table which i copy from DB is

table with 100 records at a time ,

What do you think about all the logic,

Did You handle differently ?

Thanks & Regards

James

[Former Member]

Hi again,

I have not used your code. BUT, I had also exactly same issue where the number of records was HUGE.

I was also using read table, and loop inside loop.

1. I applied the logic of binary search, and BINGO... it reduced the performance DRAMATICALLY.

You will also notice it when the number of records is high.

The the logic without binary and with binary and you will know the difference.

To check, virtually double..triple... add as many records, to your internal table using abap code, (you can add same duplicate records), and then you can TEST with both the logic. (without going to QA or PRD server). Then you will definitely appreciate the performance.

2. Other over all logic seems to be fine. But I am still analyzing it.

regards,

amit m.

[guilherme_frisoni]

Hi James,

couldn't you simply update all records from your table with valid_flag = space and then modify.

Something like:

LOOP AT lt_value into ls_values. " Here you can make an auxiliar table deleting duplicate users to improve performance.
  UPDATE db_table SET valid_flag = space
    WHERE user = ls_values-user.
ENDLOOP.

MODIFY db_table FROM TABLE lt_value.

Another point, where do you get the next execution? Is in another DB Table? If it is, you can try to join this tables to get updated results.

Regards,

Frisoni

[Former Member]

HI Amit ,

Thanks ,

I wait for your conclusion .

Regards

James

[Former Member]

HI Frisoni

I wish that it was that simple , but its not believe me....

I have just one table that need to be update via Job .

Regards

James

[Former Member]

HI Amit ,

I found bug in my code when i try to enter new users to the table ,

I try to handle it now , what happen is when i add new users i add him duplicate .

Regards

James

Edited by: James Herb on Jan 14, 2010 3:07 PM

[Former Member]

hai

i am also anbeginer i f u want try to create by means of line type and row type and give that name inthe coding

[Former Member]

use always subroutines