topic Re: select statements in Application Development and Automation Discussions

select statements

Mohamed_Mukhtar — Tue, 06 May 2008 18:15:59 GMT

is it good to use innerjoin or any other select statement ?

and why?

with regards

points will be rewarded if useful

Re: select statements

Former Member — Tue, 06 May 2008 18:18:28 GMT

Hello,

It's desirable that you use FOR ALL ENTRIES instead of inner join. When you use inner join, all data from the database table are recovered (not only the data that you will use - SELECT field1 field2).

And it's easier to track the data using for all entries instead of inner join.

I suggest you to read this [https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/6050] [original link is broken] [original link is broken] [original link is broken];. This blog have almost all information that you need.

Regards,

Re: select statements

Former Member — Tue, 06 May 2008 18:19:47 GMT

hi depends on the situation we have to use ..

select statetment with inner join

or select statement with for all entries..

if the inner join is between 3 tables it is some what good but not more than 3 tables..for all entries is very best in using because the performance will increase more..you can test the performance with the tcode st05 with these examples..

regards,

venkat

Re: select statements

Former Member — Tue, 06 May 2008 18:28:49 GMT

hi,

"Using several nested INNER JOIN statements can be inefficient and cause time out if the tables become too big in the future."

Joins here (in ABAP) are not those Native SQL Joins. If you are talking about the Core RDBMS, which mean Oracle or SQL Server, then Undoubtedly Joins are the best.

In ABAP, these joins are first split by the ABAP processor and then sent to the database, with the increase in DATA in production system, these joins tend to give way if your database keeps growing larger and larger.

You should rather used "FOR ALL ENTRIES IN" (Tabular conditions), which is a much efficient way as far as performance is concerned.

Check out

http://www.sap-img.com/abap/several-nested-inner-join-statements-can-be-inefficient.htm

Regards,

Santosh

Re: select statements

Former Member — Tue, 06 May 2008 18:31:28 GMT

1. in the innerjoin system will hit the databasa one time.. then fetch the data..for the tables u have joined... when u use the join make sure the query shold not effect the performance.. try to paas all the primary key values.. then link the tables using the complete primary...

2. when u use the FOR ALL ENTRIES.. the system will retrive all the records which matches the FIELDS which u r refering in the Internal tabel... while using the FOR ALL ENTRIES.. creal the duplicate entries.. other wise it select the record more number of time at data base level... but u will get the entries properly...

inner join can select all the common fields from the both table where as for all entries in can select all reocrds from first table then relavant fields from the second table.,

ex:

table 1: records are 1 2 3 4 5 6 7

table 2: records are 1 2 5 6 7

then inner join selects : 1 2 5 6 7

where as for all entries in selects: 1 2 3 4 5 6 7 from table1

and 1 2 5 6 7 from table2

3 major drawbacks of the "for all entries" clause:

1. duplicate rows are automatically removed

2. if the itab used in the clause is empty , all the rows in the source table will be selected .

3. performance degradation when using the clause on big tables.

go through this link

/people/rob.burbank/blog/2007/03/19/joins-vs-for-all-entries--which-performs-better

Re: select statements

Former Member — Tue, 06 May 2008 18:35:06 GMT

Hi,

Try acessing the transaction ST05! It compares Inner Join with FOR ALL ENTRIES, and much more!

Remember: avoid SELECT statements inside LOOPs. For doing so, use FOR ALL ENTRIES IN.

Regards,

Brian Gonsales

Re: select statements

Former Member — Tue, 06 May 2008 18:44:51 GMT

hi..

Our moto should be to hit the data base as less number of times as possible.

And usage of joins depends on the requirement. Instead of using FOR ALL entries we can use inner joins if the data is very less that has to be fetched.

Also, multiple joins is also not gud. because that will again increase the data base load and decrease the performance.

So, depending upon the records we have to use the select statements.

Thanks,

imran.

Re: select statements

Mohamed_Mukhtar — Fri, 25 Jul 2008 03:16:59 GMT

thx