topic Re: performance multiple joins in Application Development and Automation Discussions

performance multiple joins

Former Member — Sun, 14 Jan 2007 11:37:08 GMT

Hey,

we use multiple joins to extract data from certain tables. But because of the large amount of data and the number of tables the performance is bad. Is it possible to improve the performance. I've read that FOR ALL ENTRIES and database views are used to improve the performance, but does this also work with this kind of join?

select fkkvkp_{gpart fkkvkp}vkont ever_{vertrag ever}abrsperr

ever_{einzdat ever}auszdat

into table lt_data

from fkkvkp

join ever

on fkkvkp_{vkont = ever}vkonto

join eanlh

on eanlh_{anlage = ever}anlage

join eanl

on eanl_{anlage = eanlh}anlage

join evbs

on eanl_{vstelle = evbs}vstelle

join iflot

on evbs_{haus = iflot}tplnr

join iloa

on iflot_{iloan = iloa}iloan

join adrc

on iloa_{adrnr = adrc}addrnumber

where fkkvkp~vkont in so_vkont

and adrc~post_code1 in so_postc

and ever~vertrag in so_vert

and eanlh~tariftyp in so_type

and eanlh~bis >= sy-datum

and eanlh~ab <= sy-datum.

Re: performance multiple joins

Former Member — Sun, 14 Jan 2007 13:23:26 GMT

Hi,

There is a solution to these joins. Fetch data separately into all tables instead of using a join to fetch data. Then, compile data from these different tables into one table by using LOOP and READ statements. We had similar kind of problem in one of the projects. We avoided using 9 joins by using LOOP and READ statements.

Also, FOR ALL ENTRIES gives much better performance than a JOIN.

Reward points if the answer is helpful.

Reagrds,

Mukul

Re: performance multiple joins

Former Member — Sun, 14 Jan 2007 14:44:22 GMT

Hi pieter,

Joins always affect performance. You should always make use of FOR ALL ENTRIES. This is an efficient way of selection, rather than using joins.

In order to make use of FOR ALL ENTRIES, identify al the keys in differnt tables, and check which of these key fields occur in more than one table (which is being used). Then it can be easily merged using the FOR ALL ENTRIES stmt.

PLZ REWARD POINTS

Re: performance multiple joins

Former Member — Mon, 15 Jan 2007 02:38:48 GMT

I've done some work on this in the past. What I found was that in general, JOINs outperform FOR ALL ENTRIES. But this is not always the case. Sometimes the reverse happens and I'm not able to determine precisely why or under what circumstances. If you're really concerned about which is better, you should code both ways and take the best (remembering to allow for buffering).

But the gain is not that much in either case (maybe a factor of two.)

Having said all of the above, the most important thing to take care of is to make sure you are using as many key fields as possible in both JOINs and WHERE clauses. I'm not on R3 right now, but I doubt if post_code1 is a key field of adrc. You might try taking that out of the WHERE and check it after the SELECT.

I discuss some of this in a BLOG:

/people/rob.burbank/blog/2006/11/16/performance--what-will-kill-you-and-what-will-leave-you-with-only-a-flesh-wound

Rob

Re: performance multiple joins

Former Member — Mon, 15 Jan 2007 04:12:51 GMT

Hi,

u do like this.

first compare two table and comment remaining tables and check which table is taking more time and detach that table from the JOIN and use the for all entries.

i have done like this and improved the performance.

Especially for your case it is better to use the JOIN.

Hope this will help you lot

Thanks

Shiva

Re: performance multiple joins

Former Member — Mon, 15 Jan 2007 04:35:54 GMT

Hi,

Its advisable not to use JOINS for more than 2 or 3 tables. 2 tables itself would reduce the performance.

Instead, use for all entries and you can sort the table and use the Read..table to read the entries.

If you are using FOR ALL ENTRIES and need to exclude some entries, then instead of using the Negative stmts like <>, >=, <= , select all entries and then you can delete the entries which do not match your criteria from the internal table.

This also improves performance.

Regards

Subramanian

Re: performance multiple joins

Former Member — Mon, 15 Jan 2007 09:58:59 GMT

Hey,

I tested the "for all entries" statement and it seems that it's no improvement for my case. Maybe a mix of joins and all entries will work, but I hadn't have time to test it yet.

@Mukul R. Kulkarni: The tables you use to select your data in, i suppose they are hashed tables? Otherwise the read will be sequential and the performance will not improve. Correct me if I'm wrong.

regards Pieter

Re: performance multiple joins

former_member183804 — Tue, 16 Jan 2007 11:53:53 GMT

Hello Pieter,

when going for performance optimization it is hard to judge from distance. It depends on technical settings, dbms used and the usage profile. So there are only some general hints:

- The application server uses a own table buffering logic. These bufferng can be enabled by according attributes of db tables. It is one of the most effective way of performance tuning but only if - no joíns/views and no distinct is used.

- Joins heavily depend on the availibility of fitting database indicies. Despite the pure availibility most DBMS have an optimizer to compute a so called query plan. The query plan determine the use and order of the db index. Sometimes the 'optimized' query plan is not that good. So it might be a good idea to check the query plan with the help of TA ST05. In case that the default query plan is not perfect you need to use DB Hints which are specific with the various db vendors, it can not be influenced that detailed by ABAP statements.

Beside this a new balancing between for using AS buffering (FOR ALL ENTRIES) and a limited use of JOIN should also be checked.

Regards

Klaus