Select statement

[Former Member]

Hi All,

I have to extract data from more than 8 DB tables. Advised to use Inner Join for performance but I want to use For All Entries ?

Test client contains less data so unable to find ot any.

Plz guide me what to use and why.

Thanks

[Former Member]

Hi,

while using inner join the programming side only is dificul;t..once u do it successfully it is more useful..'

But if u want to use for all entries, use like,

Syntax ... FOR ALL ENTRIES IN itab WHERE ... col operator itab-comp ... Effect If the addition FOR ALL ENTRIES is specified before the language element WHERE, then the components comp of the internal table itab can be used as operands when comparing with relational operators. The internal table itab must have a structured line type and the component comp must be compatible with the column col. The logical expression sql_cond of the WHERE condition can comprise various logical expressions by using AND and OR. However, if FOR ALL ENTRIES is specified, there must be at least one Comparison with a column of the internal table itab, which can be specified either statistically or dynamically (Release 6.40 and higher). In a statement with a SELECTstatement with FOR ALL ENTRIES, the addition ORDER BY can only be used with the addition PRIMARY KEY. The whole logical expression sql_cond is evaluated for each individual line of the internal table itab. The resulting set of the SELECT statement is the union of the resulting sets from the individual evaluations. Duplicate lines are automatically removed from the resulting set. If the internal table itab is empty, the whole WHERE statement is ignored and all lines in the database are put in the resulting set. Notes In Release 6.10 and higher, the same internal table can be specified after FOR ALL ENTRIES and after INTO. The addition FOR ALL ENTRIES is only possible before WHERE conditions of the SELECT statement. Example Exporting all flight data for a specified departure city. The relevant airlines and flight numbers are first put in an internal table entry_tab, which is evaluated in the WHERE condition of the subsquent SELECT statement.

PARAMETERS p_city TYPE spfli-cityfrom. 

TYPES: BEGIN OF entry_tab_type, 
         carrid TYPE spfli-carrid, 
         connid TYPE spfli-connid, 
       END OF entry_tab_type. 

DATA: entry_tab   TYPE TABLE OF entry_tab_type, 
      sflight_tab TYPE SORTED TABLE OF sflight 
                       WITH UNIQUE KEY carrid connid fldate. 

SELECT carrid connid 
       FROM spfli 
       INTO CORRESPONDING FIELDS OF TABLE entry_tab 
       WHERE cityfrom = p_city. 

SELECT carrid connid fldate 
       FROM sflight 
       INTO CORRESPONDING FIELDS OF TABLE sflight_tab 
       FOR ALL ENTRIES IN entry_tab 
       WHERE carrid = entry_tab-carrid AND 
             connid = entry_tab-connid. 

Regards,

Renjith Michael.

[Former Member]

Hi,

As per my understanding, try to use INNER JOIN since u have to get data from 8 DB Tables..

But i thnk, u better join max 4 tables in one select statement because if u try to join 8 that may cause probs due to primary key mismatches..

So join 4 tables max and in the next select try to join next 4 tables and use for all entries to get those specific data tht u feteched from first select stmt....

Also using FOR ALL ENTRIES alone u cant bring increase the performance..if u r not using JOIN then u may have to use 8 select statements in which u have to incorporate for all entries in 7 of it....

My kind advice is better go ahead with INNER JOIN

Rewards if found useful...

Regards,

ABAPer 007

[Former Member]

Hi,

yes I know all u mentioned but my problem is little bit different. Why I can't use for all entries as it's easier to code n also all the db tables hit once only. I want to know which is better performance wise? Why inner join is better?

Thanks.

[Former Member]

Performance is not a question of personal preferences or taste.

With 8 tables you should use a combination of inner joins and for all entries.

The problem of joins is whether the database can find out which is the best table to start with or whether it can not.

If you have where-conditions which refer only to one table and which workj efficiently on this table, then the join will start with that

table. If all other tables have on-condition which exploit the fully primary key then you can join all 8 tables and the solution will

be much more efficient than a FOR ALL ENTRIES solution.

The problem comes in, if your where condition relates to several tables and it is not so clear, if one is the best starting point.

And or if the on conditions contain fields which in one or several secondary key. So the order of execution is very unclear.

Then a join with 8 tables will not work. You should check whether less tables can be joined efficiently. And add the rest by FOR ALL ENTRIES.

With FOR ALL ENTRIES you have also to bring together the internal tables. Make sure that you use binary search.

Siegfried

[Former Member]

Usually the join is better it goes once to the database for all table togegther. The FOR ALL ENTRIES, once per table!

The join brings on records back which you need, with the FOR ALL ENTRIES you get also some intermediate records which you might not need at the end.

The FOR ALL ENTRIES work in blocks of 5 or up 30 records from the driver table and with all togehter.

The problem with the join is the order of execution, with many tables it can be unclear in which order they must be processed. The join does not follow to order which you use in the coding it tries to find the optimal solution by itself. This is usually optimal for a smaller numer of tables.

Overall join constructions can differ so much that you can not expect one unique optimal solution.

Siegfried

[ThomasZloch]

Hi Siegfried,

Rob Burbank created a nice blog about this topic

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

I think your thoughts here in this thread go even deeper, maybe it would be a good idea to write a new version with added content, because this discussion comes up regularly and most of the time people are being misled by simple (and wrong) statements like "use for all entries, it performs better".

If I had the time and your knowledge, I'd do it.

Cheers

Thomas

[Former Member]

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.

===================================================================================