topic Re: Performance issue in select and loop in Application Development and Automation Discussions

Performance issue in select and loop

former_member300655 — Wed, 04 Sep 2013 15:31:54 GMT

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

Re: Performance issue in select and loop

Former Member — Wed, 04 Sep 2013 17:17:16 GMT

This message was moderated.

Re: Performance issue in select and loop

Former Member — Wed, 04 Sep 2013 17:20:02 GMT

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

Re: Performance issue in select and loop

Former Member — Wed, 04 Sep 2013 20:05:24 GMT

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

Re: Performance issue in select and loop

paul_bakker2 — Thu, 05 Sep 2013 02:04:06 GMT

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

Re: Performance issue in select and loop

former_member184455 — Thu, 05 Sep 2013 06:50:46 GMT

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

Best Regards, Randolf

Re: Performance issue in select and loop

matt — Thu, 05 Sep 2013 08:33:11 GMT

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

Re: Performance issue in select and loop

matt — Thu, 05 Sep 2013 08:33:43 GMT

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

Re: Performance issue in select and loop

Former Member — Thu, 05 Sep 2013 11:38:52 GMT

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 ).

Re: Performance issue in select and loop

mayur_priyan — Fri, 13 Sep 2013 14:37:54 GMT

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.

Re: Performance issue in select and loop

Former Member — Tue, 17 Sep 2013 06:12:09 GMT

Hi,

you can try the following:

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

Regards,

Klaus