topic performance issues in Application Development and Automation Discussions

performance issues

Former Member — Tue, 24 Jul 2007 07:14:36 GMT

Hi,

I have a code to modify for its performance issues.

I found some queries in it containing FOR ALL ENTRIES and INNER JOIN .

Is it ok to blindly change these lines with something or some other reasons are there with the code for performance issues.

If something is to be done with FOR ALL ENTRIES, then please suggest an alternative for the following lines.

SELECT gpart vkont

INTO TABLE t_bpca

FROM fkkvkp

FOR ALL ENTRIES IN t_respart

WHERE gpart = t_respart-partner

AND opbuk NE c_siebel

AND vkpbz = 'AC'.

What is an alternative for INNER JOINS ?

Please help me.

Binay.

Re: performance issues

Former Member — Tue, 24 Jul 2007 07:18:25 GMT

Hi,

FOR ALL ENTRIES is an effective way of doing away with using JOIN on two tables.

You can check the below code -

SELECT BUKRS BELNR GJAHR AUGDT

FROM BSEG

INTO TABLE I_BSEG

WHERE BUKRS = ....

SELECT BUKRS BELNR BLART BLDAT

FROM BKPF

INTO TABLE I_BKPF

FOR ALL ENTRIES IN I_BSEG

WHERE BUKRS = I_BSEG-BUKRS

AND BELNR = I_BSEG-BELNR

AND BLDAT IN SO_BLDAT.

*******************************8

look another example

what is the use of FOR ALL ENTRIES

1. INNER JOIN

DBTAB1 <----

> DBTAB2

It is used to JOIN two DATABASE tables

having some COMMON fields.

2. Whereas

For All Entries,

DBTAB1 <----

> ITAB1

is not at all related to two DATABASE tables.

It is related to INTERNAL table.

3. If we want to fetch data

from some DBTABLE1

but we want to fetch

for only some records

which are contained in some internal table,

then we use for alll entries.

*----

1. simple example of for all entries.

2. NOTE THAT

In for all entries,

it is NOT necessary to use TWO DBTABLES.

(as against JOIN)

3. use this program (just copy paste)

it will fetch data

from T001

FOR ONLY TWO COMPANIES (as mentioned in itab)

Regards

Re: performance issues

Former Member — Tue, 24 Jul 2007 07:37:42 GMT

Hi,

Part1

*************

FOR ALL ENTRIES is used when we have to select values from one database table with respect to some value in an internal table

eg: ITAB1,ITAB2 be the internal tables....you have ITAB1 filled..then you need to get values for ITAB2 based on fields/conditions from database table......you use select from Database table for all enties in ITAB1

Please dont change the existing code because of the following reasons.....

For all entries is used to avoid loops and multiple read statements with respect to an internal table........

eg:step1) Pass value into internal_table1 from a standard database select or otherwise

2)to get records with respect to internal_table1 one can use loop at internal_table1 or read table internal_table1 with index X.

Use for all entries with respect to internal_table1

Part2

*************

Inner join is used when we have to select values from one database table with respect to value of another

eg: Db1/DB2/DB3 be standard tables with identifiable common keys..if you do

select from DB1--> select from DB2 where key = DB1-XXX -

> select from DB3 where key = DB2-XXX .....then inner join is better

Alternative to inner join is by going to each table and doing select single or select *

But if there are more than 2 tables it is advisable to use inner join than the select because performance decreases if select is used

Reward all helpful answers

Regards

Byju

Re: performance issues

Former Member — Wed, 25 Jul 2007 11:02:51 GMT

The differences between inner joins and for all entries was explained by the 2 responses you got already. You can see FOR ALL ENTRIES as an inner join of a DB table with an internal table.

Please read the online documentation for details and check always before the select whether the inner table has entries at all.

Coming back to your performance problem, your coding example is by itself o.k, you must test its performance with the SQL trace (transaction ST05), to see how long it needs and to judge whether it is correctly supported by database indexes.

Just changing coding will not help you.

Siegfried

Re: performance issues

Former Member — Wed, 25 Jul 2007 11:32:39 GMT

Hi,

Use of FOR ALL Entries

Outer join can be created using this addition to the where clause in a select statement. It speeds up the performance tremendously, but the cons of using this variation are listed below

Duplicates are automatically removed from the resulting data set. Hence care should be taken that the unique key of the detail line items should be given in the select statement.

If the table on which the For All Entries IN clause is based is empty, all rows are selected into the destination table. Hence it is advisable to check before-hand that the first table is not empty.

If the table on which the For All Entries IN clause is based is very large, the performance will go down instead of improving. Hence attempt should be made to keep the table size to a moderate level.

Not Recommended

Loop at int_cntry.

Select single * from zfligh into int_fligh

where cntry = int_cntry-cntry.

Append int_fligh.

Endloop.

Recommended

Select * from zfligh appending table int_fligh

For all entries in int_cntry

Where cntry = int_cntry-cntry.

ANOTHER WAY :

Use of Views instead of base tables

Many times ABAP programmers deal with base tables and nested selects. Instead it is always advisable to see whether there is any view provided by SAP on those base tables, so that the data can be filtered out directly, rather than specially coding for it.

Not recommended

Select * from zcntry where cntry like ‘IN%’.

Select single * from zflight where cntry = zcntry-cntry and airln = ‘LF’.

Endselect.

Recommended

Select * from zcnfl where cntry like ‘IN%’ and airln = ‘LF’.

Endselect.

regards

Debjani

Rewards point for all helpful answers