Performance issue in select and loop

[former_member300655]

I need to adjust the performance of my code, the execution like it is now takes very long to run.

The "bw_table"  contains around 7 fields and 40'000 posts.

And the "result_package" contains 60 fields and appr 2'000'000 posts

Is there any better ways to adjust this code below?

SELECT * FROM bw_table

INTO TABLE it_bw_table

FOR ALL ENTRIES IN Result_package

WHERE cond1 = cond2

LOOP AT Result_package ASSIGNING <result>

LOOP AT it_bw_table INTO ls_bw_table

WHERE cond1 = <result>-cond1

AND cond2 = <result>-cond2

AND cond3 = <result>-cond3

IF statement

ELSEIF statement

ENDIF

ENDLOOP

ENDLOOP

[Former Member]

This message was moderated.

[Former Member]

i) do you need all the fields of bw_table? if not, then fetch selected fields only to avoid '*' from select query.

ii) Avoid nested loop as well. one way to do it....consolidate data from both internal tables into another table and then use Loop with Read to this table.

Rgds

[Former Member]

LOOP AT Result_package ASSIGNING <result>

SORT it_bw_table BY cond1 cond2 cond3.

READ TABLE it_bw_table WITH KEY cond1 = <result>-cond1

                                cond2 = <result>-cond2

                                cond3 = <result>-cond3 BINARY SEARCH TRANSPORTING NO FIELDS.

IF sy-subrc IS INITIAL.

lv_tabix = sy-tabix.

ELSE.

CONTINUE.

ENDIF.

LOOP AT it_bw_table INTO ls_bw_table FROM lv_tabix.

IF ls_bw_table-cond1 NE <result>-cond1

OR ls_bw_table-cond2 NE <result>-cond2

OR ls_bw_table-cond3 NE <result>-cond3.

EXIT.

ENDIF.

IF statement

ELSEIF statement

ENDIF

ENDLOOP

ENDLOOP

[matt]

Don't use BINARY SEARCH. SORTED/HASHED tables have been around for 15 years...

[paul_bakker2]

Hi,

No doubt you will get a LOT of replies to this question, but let me ask you: have you done a simple performance analysis, to see if the main issue is in the SELECT, the nested LOOP, or in the STATEMENT part of your code?

I'd advise you to identify where the problem is first, then focus on that.

If the problem is in the SELECT statement, I would just remove the FOR ALL ENTRIES line.

It's counter-productive to use FOR ALL ENTRIES if the driver table is large (especially if the DB table is small)

cheers

Paul

[former_member184455]

Hi,

If possible use a JOIN between bw_table and result_package table.

If it's not possible, assure that the values of the fields in FAE-table used in WHERE-clause of SELECT are unique.

Use a SORTED TABLE for it_bw_table to optimize access in LOOP with WHERE-clause.

Example:

DATA it_bw_table type SORTED TABLE of ltype_bw_table
with non-unique key cond1 cond2 cond3.

LOOP AT Result_package ASSIGNING <result>.
COLLECT <result>-condx into condx_table.   "assumption: <result>-condx is non-numeric
ENDLOOP.

SELECT * FROM bw_table
INTO TABLE it_bw_table
FOR ALL ENTRIES IN condx_table
WHERE cond1 = condx_table-table_line.

LOOP AT Result_package ASSIGNING <result>

  LOOP AT it_bw_table INTO ls_bw_table
     WHERE cond1 = <result>-cond1
       AND cond2 = <result>-cond2
       AND cond3 = <result>-cond3

  IF statement

  ELSEIF statement

  ENDIF

  ENDLOOP

ENDLOOP

Best Regards, Randolf

[matt]

It's amazing how many people are still using BINARY SEARCH. When were sorted tables introduced? Only 15 years ago...

[Former Member]

This is a very compact and recent comparison between different tables types.

People are afraid of trying. It's the bad and old "why change if everything is working?" (working slower in this case ).

[mayur_priyan]

Hi,

The performance issue which you are facing is mostly due to the nested loop within the program.

Try using Parallel cursor method instead, which I think might improve your program's performance.

[Former Member]

Hi,

you can try the following:

1. Make sure that internal table RSULT_PACKAGE is sorted by cond1, cond2, cond3.
2. Only one single LOOP at internal table RESULT_PACKAGE.
3. In this LOOP only on change of conditions SELECT matching entries from BW_TABLE into internal table.
4. Make sure that access to table BW_TABLE has a fitting primary or secondary index.

Regards,

Klaus