Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Inner Joins vs For all Entries.

lijisusan_mathews
Active Contributor
0 Kudos

I have to code with some tables which contain vary large amount of data. Can you please explain the cases where I should use inner joins over for all entries or vice versa, to get better performance..? Is it true that for all entries does not function properly for more than 60000 records?

Regards

Liji

1 ACCEPTED SOLUTION

former_member194613
Active Contributor
0 Kudos

> The internal table is the result of a procedural approach in retrieval of data.

not really, an internal table actually keeps the data you are working with, very often this can be input data! And the main idea of the FAE is to read additional information for these inputs.

The default recommendation for improvement of nested selects is a join. The join uses in most cases a nested loop on the database, which is more or less the same as the FAE but it is inside the database and therefore faster !

Unfortunately, joins can get confused. And that is the point, where the people with the repeated recommendation 'Use FAE', get lost. I think the simply don't understand much about databases. If this happens, there is usually a simple solution to bring the join back on track.

Joins can offer Sort Merge Joins or Hashed Joins, but these usually the complicated cases, which are slow. But in these cases FAEs should not work at all anymore, they would have to transfer to much data.

Join on two internal tables are not possible, that's what I meant. And FAE can be seen as a join between a database table and an internal table, and some database implement it as a join.

Coming back to the original question, with a huge number of data, IT MUST BE IMPLEMENTED AS A JOIN!!!

Siegfried

8 REPLIES 8

Former Member
0 Kudos

Please see:

[ JOINS vs. FOR ALL ENTRIES - Which Performs Better?|]

(Generally, JOINs are better.)

Rob

Former Member
0 Kudos

Hi,

FOR ALL ENTRIES..should be avoided in case you are expecting a large Volume of Data in the Driver Internal Table, because the Single Database Process might starts eating the System resources more than what is top limit set by your BASIS guy and hence you might end up with a Short Dump....

In this kind of Cases.....prefer using Inner Joins over For All Entries.......

former_member194613
Active Contributor
0 Kudos

unfortunately the FAE camp is not completely wrong.

The problem comes up sometimes, that a join will use a strange order of accessing the tables, and then the join can go wrong. It can become much slower than an FAE. That is the problem case.

However, the join does nested loops and the nested loops use the same table order and indexes as the programmed order of the FAE, then the join should always be faster than the FAE. That is the normal and the good case.

I assume that people who encountered once the problem case, like to stick with the FAE.

In the case of small select it is maybe not really worth to start extensive discussions.

But in the case of extensive accesses, one should always try to use joins! There it is definitely worth to check what the join is doing.

Siegfried

0 Kudos

Hi Siegfried,

I admit this , but: it's rare case ( maybe 1 percent 😞 for a complex join over many tables it maybe possible harder to generate an efficient execution plan - you got the join hints to correct possible errors ; no FAE needed , though.

I would say if one joins over more than 10 tables the data model needs improvement - happens if normalization is taken to it's limits where a practical redundancy of data would performance wise. ( for the datawarehouse guys: Star schemas are an exception here!)

If a join is processed by nested loops (NL) depends on the optimizer's decision- you will find also sort/merge (SM) or hash joins (HA) in join execution plans.

Generating the join order is a complex optimizer calculation that (in general) permutates through all table's combinations (with a little optimization for the permutations): It calculates the costs (based on the statistics) of the possible join operations and orders and chooses the cheapest one at the end.

Do you know how the parameter rsdb/prefer_join influences the SQL statement propagated to the DB?

Especially for internal tables - wich exists only in the memory of the application server?

bye

yk

former_member194613
Active Contributor
0 Kudos

> I admit this , but: it's rare case ( maybe 1 percent 😞 for a complex join over many tables it maybe possible harder to generate an efficient execution plan -

no, that is not my experience, I have seen confused joins more frequently even in rather simple situations.

But you are right, these problems can easily be fixed with a hint and affects usually not all databases.

I don't understand your question about joins and internal tables? Internal tables can not be joined.

Some databases use a join actually a join for the FAE, because they create a temprary table for the driver table. They do it of course to increase performance.

Siegfried

0 Kudos

>

> I don't understand your question about joins and internal tables? Internal tables can not be joined.

>

> Some databases use a join actually a join for the FAE, because they create a temprary table for the driver table. They do it of course to increase performance.

>

> Siegfried

Hi Siegfried,

I knew that - I was wondering HOW this could be implemented - i was argueing it must be materialized somehow.

It seems it's a tradeoff between the time writing/indexing the internal table and using OR's and UNION ALLs.

Go this note for FAE parsing:

https://service.sap.com/sap/support/notes/48230

The following profile parameters are available:

rsdb/prefer_join (as of Release 7.00)

If you set this parameter to "1", the SELECT ... FOR ALL ENTRIES is implemented using a join.

Note that this variant is only supported by DB6 (DB2 UDB) and MS SQL Server database platforms as of Release 7.00.

For Oracle, the parameter rsdb/prefer_join = 1 (TRUE) is supported as of SAP Release 7.10, however, the default setting for Oracle is 0 (FALSE). As of Release 7.11, the Oracle default is set to 1 (TRUE).

It seems it's a tradeoff between the time writing/indexing the internal table and using OR's and UNION ALLs.

Only can speak for Oracle databases - but i think the setup for FAE regarding prefer_join should come via the SAP SQL interface transferring to valid native SQL for the DB in question.

Wich could be a CREATE TABLE type temporary (wich is session aware) ... for materializing a memory structure inside the DB. But there are some implications on getting statistics and indexes for joining the temp tables. For me it sounds - a little bit , hum, strange ...

I'm used to work with database views wich will deliver a object to join with - based on table(s), joins,

virtual columns , aggregations and the like...until I get my subset of data.

Afterall, an internal table collects data that comes from ... physical database tables.

I always should bring the SQL/View to a level where I can postprocess the results according to

the business logic.

The internal table is the result of a procedural approach in retrieval of data. Allthough, at some level you have to go for this approach, it seems to me a lot of ABAPers think there is only this approach.

Mainly I use the procedural approach to control the flow of my SELECT's, feeding the bind variables and so on. Soemtimes I LOOP through a result set to calculate something later - only to pass values to follwing statements. For me the SQL / view is a business entity that delivers back data.

I would rarely use FAE with an internal table...but maybe there is a category of problems that are awaiting for me where I have to...

bye

yk

former_member194613
Active Contributor
0 Kudos

> The internal table is the result of a procedural approach in retrieval of data.

not really, an internal table actually keeps the data you are working with, very often this can be input data! And the main idea of the FAE is to read additional information for these inputs.

The default recommendation for improvement of nested selects is a join. The join uses in most cases a nested loop on the database, which is more or less the same as the FAE but it is inside the database and therefore faster !

Unfortunately, joins can get confused. And that is the point, where the people with the repeated recommendation 'Use FAE', get lost. I think the simply don't understand much about databases. If this happens, there is usually a simple solution to bring the join back on track.

Joins can offer Sort Merge Joins or Hashed Joins, but these usually the complicated cases, which are slow. But in these cases FAEs should not work at all anymore, they would have to transfer to much data.

Join on two internal tables are not possible, that's what I meant. And FAE can be seen as a join between a database table and an internal table, and some database implement it as a join.

Coming back to the original question, with a huge number of data, IT MUST BE IMPLEMENTED AS A JOIN!!!

Siegfried

lijisusan_mathews
Active Contributor
0 Kudos

thank you all.all your posts helped me a lot..