Large JOIN

[justin_thomas]

Hi all,

Does anyone have any experience, advice or comments about large joins - say between 8 - 15 tables?

Thanks

Justin

[Sandeep_Panghal]

Joins on more than 4 tables mostly result in poor perfromance.

[Former Member]

Hi Sandeep,

There are certain circumstances where if you choose for FOR ALL ENTRIES instead join also there have been lot of Performance issues.

It depends on the requirement like the tables involved & the amount of data they have...

Regards

Abhii

[Sandeep_Panghal]

Hi Abhi,

I am not saying that for all entries is better than larger joins either.. it depends on so many factors

What one should do is try to use open cursor method for the same.

Regards,

Sandeep

[david_lees]

Hi Justin,

I think the general advice is to not do it ...

a) performance reasons, complex SQL

b) analysis/debugging is made all the more tricky if your select doesn't return the expected results and you are trying to understand why

I have seen some reports that consist of mostly a large SELECT of x number of joins and then cases whereby lazy developers have come along afterwards and added more tables into the existing SELECT making it a nightmare to analyse!!

Cheers, David

[Former Member]

Hi,

Its possible to have large joins but that is very bad from performance point of view.

Its only advisable to use 3 or 4 joins at a time.

You can instead use for all entries and joins combination to join all tables.

Regards,

Subhashini

[justin_thomas]

Thanks guys - looks like a good debate starting! - I have seen lots of conflicting comments on whether FAE or Joins are better, I don't want to start that again - but I'm interested to know if anyone has had experience (good or bad) in large joins: theoretically if the join is properly constructed, care is taken to ensure the right indices are invoked, and cursors are emplyed, there should be nothing wrong with it? Normally we believe that joins are best practice over sub-selects and other technicques, especially with very large tables (millions of records)?

Cheers

J

[Former Member]

Remember that table views are implemented using JOINs and that there are standard SAP views that include more than ten tables. So anyone who says don't JOIN more than four tables should be able to explain this.

Rob

[Former Member]

I agree with Rob.

Regards

Abhii

[Sandeep_Panghal]

You can have upto 24 joins , i agree but my statment was "Joins on more than 4 tables mostly result in poor perfromance."

What if you join tables like BKPF,BSEG,BSIS,BSID,BSIK in one select ???

[SuhaSaha]

>

> What if you join tables like BKPF,BSEG,BSIS,BSID,BSIK in one select ???

Hello Sandeep,

FYI you cannot use BSEG in a join. Why?? Discover yourself.

@Rob: Just for curiosity can you please give the name of the SAP std. join?

BR,

Suhas

[Former Member]

No join possible on cluster tables.

Regards

Abhii

[Sandeep_Panghal]

That was just an example...

[Former Member]

>

> "Joins on more than 4 tables mostly result in poor perfromance."

What evidence do you have to back that up? And what would be the alternatives that would give better performance?

@Suhas - this question comes up from time to time. I wrote a quick program that searched the data dictionary for views and sorted it by the number of tables in each. The program is gone, so you are invited to re-create it if you'd like

Rob

[Former Member]

>

> You can have upto 24 joins , i agree but my statment was "Joins on more than 4 tables mostly result in poor perfromance."
> What if you join tables like BKPF,BSEG,BSIS,BSID,BSIK in one select ???

OK - I tried what you obviously didn't. I created a LEFT OUTER JOIN on seven tables: bkpf, bsis, bsas, bsid, bsad, bsik and bsak. Then I did FOR ALL ENTRIES with the same tables and SELECT criteria.

The JOIN ran 3.5 to 4.5 times faster than FOR ALL ENTRIES.

Hope this finally puts this to rest!

Rob

[Former Member]

>

> Hope this finally puts this to rest!

Apparently it didn't!

If you check view V_TCJ41, you'll see that it has 38 tables (in ECC 6).

Rob

[former_member194416]

Hi Rob,

38 table is something but view you mentioned is a maintenance view. Anyway you are right. Performance will never be directly related with number of tables but it will be related with the logical connection between those tables so it is better to try and test different options for each different case.

Edited by: Gungor Ozcelebi on Dec 1, 2009 1:53 PM

[roger_sainsbury]

Hi Justin,

I don't think using a large number of joins will necessarily cause poor performance. As I understand it, poor performance could arise due to the complexity of the resulting SQL - it just makes it more difficult for the database optimiser to choose the best access path. So you may end up with a full table scan, or just a poor choice of access path. But that certainly won't happen necessarily - you would just need to try it to find out. Remember the results may vary considerably according to what selection criteria are being applied.

Roger

[justin_thomas]

Many thanks guys, some really useful points and examples here.