For All Entries - Performance Issue

[former_member235717]

Hi All,

I have a report program which has a performance issue. When the user runs the program online, it is taking more time to execute .

When I check the Runtime Analysis for the report, there are two select statements which are taking consuming the highest time in the report. For the Select statements are using For All Entries. Since the User doesn't input any data and run the program for all the data, it has almost 15000 entries which is causing the "Select .. for all entries" to consume lot of time.

Please let me know if there is any work around for this issue.

Thanks in advance!!

[adrian_mejido]

Hi Swetha,

• You can check the best performance with the SE30 transaction (click on click on  tips and tricks) which helps you to mesaure runtime. Sometimes JOIN is better than FOR ALL ENTRIES.

• There are some information on SCN about this, for example check this one, it might help you:

Best regards

[former_member235717]

Thanks for your quick response.

However I'm trying to get data from an Internal table in for all entries. So in this case I don't think we can go for Inner Join.

SELECT *   FROM ztable
  INTO corresponding fields of TABLE it_tab
FOR ALL ENTRIES IN <internal table>
  WHERE conditions.

[adrian_mejido]

But I think that your <internal table> was selected from a database, wasn't it? In that case, try to use the INNER-JOIN instead of doing two different selects. Furthermore you should check the se30 tips.

Best regards.

[former_member235717]

Right , but the internal table itself is a Inner join of two database tables.

SELECT a~vkorg a~vstel a~vbeln
          b~posnr b~matnr b~arktx
     FROM likp AS a INNER JOIN lips AS b
       ON a~vbeln = b~vbeln
     INTO TABLE it_a
     WHERE vkorg IN s_vkorg
       AND vstel IN s_vstel.
    

SELECT *   FROM ztable
  INTO corresponding fields of TABLE it_b
FOR ALL ENTRIES IN it_a
  WHERE conditions.

Here both the it_a and it_b structures are completely different except for few common fields (only 2 in my case) and also I need to use this two ITABS in the coding.

Please let me know how can I merge all this three into one inner join.

I did check the se30 tips but it didn't help me much in this issue.

[adrian_mejido]

Hi swetha,

You can do a INNER JOIN for more than two tables. For example:

Table 1 :ZTAB1

Table 2 :ZTAB2

Table 3 :ZTAB3

SELECT A~FIELD1 B~FIELD1 B~FIELD2 C~FIELD1       

FROM ZTAB1 AS A

INNER JOIN ZTAB2 AS B ON B~FIELD1 EQ A~FIELD1                     

INNER JOIN ZTAB3 AS C ON C~FIELD1 EQ B~FIELD2         

INTO TABLE LT_DATA        

WHERE A~FIELD3 EQ 'XXXX'

etc....

Best regards

[jino_jose]

Hi

For the select select statement which uses the for all entries are you putting all the key fields in the condition, if not add all the key fields, for all entries performance degrades if all the key fields are not mentioned on the condition. Also please check whether the driver table is  empty or not. in the above scenario if the table it_a is empty system will bring all the records from the ztable.

Thanks,

Jino.

[nabheetscn]

Hi Swetha

What is matching condition for the custom table..? If possible you can combine three of them

Nabheet

[Private_Member_15166]

Hi Swetha,

1). If possible dn't use into corresponding fields because it takes too much time.

2.) Avoid selecting * if all fields are not required.

3.) Instead of using nested Select loops or FOR ALL ENTRIES it is often possible to use subqueries.

Network load is considerably less.

For example:

SELECT * FROM SFLIGHT AS F INTO SFLIGHT_WA

                                    WHERE SEATSOCC < F~SEATSMAX

                                     AND EXISTS ( SELECT * FROM SPFLI

                                    WHERE CARRID = F~CARRID

                                     AND CONNID = F~CONNID

                                     AND CITYFROM = 'FRANKFURT'

                                    AND CITYTO = 'NEW YORK' )

                                    AND FLDATE BETWEEN '19990101' AND '19990331'.

                                   ENDSELECT.

Instead of

SELECT * FROM SPFLI

                                    INTO TABLE T_SPFLI

                                    WHERE CITYFROM = 'FRANKFURT'

                                    AND CITYTO = 'NEW YORK'.

SELECT * FROM SFLIGHT AS F

                                    INTO SFLIGHT_WA

                                    FOR ALL ENTRIES IN T_SPFLI

                                    WHERE SEATSOCC < F~SEATSMAX

                                    AND CARRID = T_SPFLI-CARRID

                                    AND CONNID = T_SPFLI-CONNID

                                    AND FLDATE BETWEEN '19990101' AND '19990331'.

                                   ENDSELECT.

4.) Sort the comparision fields in the driver table.

5.) Delete the adjacent duplicates from the driver table , it will enhance the performance time of the program.

6.) Check if the driver table is not initial.

Regards.

Dhananjay Choubey

[matt]

1). If possible dn't use into corresponding fields because it takes too much time.

This is not correct. See thomas.zloch 's blog here: https://blogs.sap.com/2012/12/10/why-into-corresponding-is-much-better-than-its-reputation/.

The problem with INTO TABLE is that it relies on the field order in your select being the same as your internal table. If a new field is added to a table, your program breaks. This does not happen with INTO CORRESPONDING. Coupled with the fact that there's no performance difference, you should with few exceptions use INTO CORRESPONDING.

[Former Member]

Here are few tips for good performance.

1. Before using for all entries, check the reference table for NOT INITIAL check. ( it_tab[] NOT INITIAL )

2. Sort the reference table. SORT it_tab by key

3. Avoid INTO CORRESPONDING, instead declare your <internal_table> which required fields only

4. Avoid SELECT *, use only required fields in the select query

5. If your Where condition is not the combination of PRIMARY KEY, then go for creating secondary index on custom table

6. Delete adjacent duplicates from reference table

Regards,

Fasi

[Former Member]

Hi Swetha,

Try to write a logic which can create multiple chunks of input data so that those multiple chunks can be processed in parallel.

Parallel processing might help you with this situation.

http://help.sap.com/saphelp_nw70/helpdata/en/fa/096e92543b11d1898e0000e8322d00/content.htm

Parallel Processing - ABAP Development - SCN Wiki

Kindly check above links and let me know if those are helpful.

Thanks

Mohit

[Mathias_Uhlmann]

Hi,

for completeness. The symptom described is explicitly explained in the ABAP help, e.g. https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abenwhere_logexp_itab.htm.

> Before using an internal table itab after FOR ALL ENTRIES, always check that the internal table is not initial. In an initial internal tables, all rows are read from the database regardless of any further conditions specified after WHERE. This is not usually the required behavior.

All other points in "Note" are noteworthy too when using FOR ALL ENTRIES.

Best regards
Mathias.