Which hast the better performance: Join or (nested) selects

[Former Member]

Hi,

I'm an ABAP-beginner and I diskussed a few minutes

ago with my boss about the performance from

table-Joins and table selects.

So it would be very interessting for me, whats your

opinion!

[S0025444845]

Hi,

join has better performance than nested selects.

regards,

sudha

[Former Member]

Hey,

thanks for the fast response!

Can you itemize a short reason for the better

performance of a join!? Thank you very much!

[Former Member]

Hi,

I am sending u some of the performance isuues that are to be kept in mind while coding.

1.Donot use Select *...... instead use Select <required list>......

2.Donot fetch data from CLUSTER tables.

3.Donot use Nested Select statements as. U have used nested select which reduces performance to a greater extent.

Instead use views/join .

Also keep in mind that not use join condition for more for more than three tables unless otherwise required.

So split select statements into three or four and use Select ......for all entries....

4.Extract the data from the database atonce consolidated upfront into table.

i.e. use INTO TABLE <ITAB> clause instead of using

Select----

---

End Select.

5.Never use order by clause in Select ..... statement. instead use SORT<itab>.

6.When ever u need to calculate max,min,avg,sum,count use AGGREGATE FUNCTIONS and GROUP BY clause insted of calculating by userself..

7.Donot use the same table once for Validation and another time for data extraction.select data only once.

8.When the intention is for validation use Select single ....../Select.......up to one rows ......statements.

9.If possible always use array operations to update the database tables.

10.Order of the fields in the where clause select statement must be in the same order in the index of table.

11.Never release the object unless throughly checked by st05/se30/slin.

12.Avoid using identical select statements.

Regards,

Omkar.

[Former Member]

If the number of tables is less, You can go for joins. Or Go for nested select ,

[Former Member]

hi

good

When multiple SAP tables are logically joined, it is always advisable to use inner join to read the data from them. This certainly reduces the load on the network.

Let us take an example of 2 tables, zairln and zflight. The table zairln has the field airln, which is the airline code and the field lnnam, which is the name of the airline. The table zflight has the field airln, the airline code and other fields which hold the details of the flights that an airline operates.

Since these 2 tables a re logically joined by the airln field, it is advisable to use the inner join.

Select a_{airln a}lnnam b_{fligh b}cntry into table int_airdet

From zairln as a inner join zflight as b on a_{airln = b}airln.

In order to restrict the data as per the selection criteria, a where clause can be added to the above inner join.

thanks

mrutyun^