inner join

[Former Member]

Will the sequence of inner join...

eg. joining kna1, knb1, bkpf, bsid

affect performance?

or does it matter when choosing the field to be in the where clause from one of the 4 tables affect performance.. when considering the use of index?

[Former Member]

so am i right in the joining sequence this way:

bsid, bkpf, knb1, kna1...

and how is it different from this (which i think is wrong)...

bkpf, bsid, kna1, knb1...

[Former Member]

Hi Charles,

Your trying to join 4 tables. it is working, but it effects to performance. at a time control can manage 4 data base tables and where condition and primary keys. How tremendous burden on the systems...... that too BKF, BSID these tables have nearly 2 million records in each in PRD. so definately it will effect to performance. nothing for order in the data bse table and main thing is main tain joining condition on proper primary keys and where condition.

Hope this helps you..

Regards,

Kumar.

[Former Member]

Specifying Two or More Database Tables as an Inner Join

In a relational database, you normally need to read data simultaneously from more than one database table into an application program. You can read from more than one table in a single SELECT statement, such that the data in the tables all has to meet the same conditions, using the following join expression:

SELECT...

...

FROM <tab> [INNER] JOIN <dbtab> [AS <alias>] ON <cond> <options>

...

where <dbtab> is a single database table and <tab> is either a table or another join expression. The database tables can be specified statically or dynamically as described above. You may also use aliases. You can enclose each join expression in parentheses. The INNER addition is optional.

A join expression links each line of <tab> with the lines in <dbtab> that meet the condition <cond>. This means that there is always one or more lines from the right-hand table that is linked to each line from the left-hand table by the join. If <dbtab> does not contain any lines that meet the condition <cond>, the line from <tab> is not included in the selection.

The syntax of the <cond> condition is like that of the WHERE clause, although individual comparisons can only be linked using AND. Furthermore, each comparison must contain a column from the right-hand table <dbtab>. It does not matter on which side of the comparison it occurs. For the column names in the comparison, you can use the same names that occur in the SELECT clause, to differentiate columns from different database tables that have the same names.

The comparisons in the condition <cond> can appear in the WHERE clause instead of the ON clause, since both clauses are applied equally to the temporary table containing all of the lines resulting from the join. However, each join must contain at least one comparison in the condition <cond>.

Specifying Two or More Database Tables as a Left Outer Join

In an inner join, a line from the left-hand database table or join is only included in the selection if there is one or more lines in the right-hand database table that meet the ON condition <cond>. The left outer join, on the other hand, reads lines from the left-hand database table or join even if there is no corresponding line in the right-hand table.

SELECT...

...

FROM <tab> LEFT [OUTER] JOIN <dbtab> [AS <alias>] ON <cond>

<options>

...

<tab> and <dbtab> are subject to the same rules and conditions as in an inner join. The OUTER addition is optional. The tables are linked in the same way as the inner join with the one exception that all lines selected from <tab> are included in the final selection. If <dbtab> does not contain any lines that meet the condition <cond>, the system includes a single line in the selection whose columns from <dbtab> are filled with null values.

In the left outer join, more restrictions apply to the condition <cond> than in the inner join. In addition to the above restrictions:

EQ or = is the only permitted relational operator.

There must be at least one comparison between columns from <tab> and <dbtab>.

The WHERE clause may not contain any comparisons with columns from <dbtab>. All comparisons using columns from <dbtab> must appear in the condition <cond>.

Minimize the Number of Data Transfers

In every Open SQL statement, data is transferred between the application server and the database system. Furthermore, the database system has to construct or reopen the appropriate administration data for each database access. You can therefore minimize the load on the network and the database system by minimizing the number of times you access the database.

Multiple Operations Instead of Single Operations

When you change data using INSERT, UPDATE, and DELETE, use internal tables instead of single entries. If you read data using SELECT, it is worth using multiple operations if you want to process the data more than once, other wise, a simple select loop is more efficient.

Avoid Repeated Access

As a rule you should read a given set of data once only in your program, and using a single access. Avoid accessing the same data more than once (for example, SELECT before an UPDATE).

Avoid Nested SELECT Loops

A simple SELECT loop is a single database access whose result is passed to the ABAP program line by line. Nested SELECT loops mean that the number of accesses in the inner loop is multiplied by the number of accesses in the outer loop. You should therefore only use nested SELECT loops if the selection in the outer loop contains very few lines.

However, using combinations of data from different database tables is more the rule than the exception in the relational data model. You can use the following techniques to avoid nested SELECT statements:

ABAP Dictionary Views

You can define joins between database tables statically and systemwide as views in the ABAP Dictionary. ABAP Dictionary views can be used by all ABAP programs. One of their advantages is that fields that are common to both tables (join fields) are only transferred once from the database to the application server.

Views in the ABAP Dictionary are implemented as inner joins. If the inner table contains no lines that correspond to lines in the outer table, no data is transferred. This is not always the desired result. For example, when you read data from a text table, you want to include lines in the selection even if the corresponding text does not exist in the required language. If you want to include all of the data from the outer table, you can program a left outer join in ABAP.

The links between the tables in the view are created and optimized by the database system. Like database tables, you can buffer views on the application server. The same buffering rules apply to views as to tables. In other words, it is most appropriate for views that you use mostly to read data. This reduces the network load and the amount of physical I/O in the database.

Joins in the FROM Clause

You can read data from more than one database table in a single SELECT statement by using inner or left outer joins in the FROM clause.

The disadvantage of using joins is that redundant data is read from the hierarchically-superior table if there is a 1:N relationship between the outer and inner tables. This can considerably increase the amount of data transferred from the database to the application server. Therefore, when you program a join, you should ensure that the SELECT clause contains a list of only the columns that you really need. Furthermore, joins bypass the table buffer and read directly from the database. For this reason, you should use an ABAP Dictionary view instead of a join if you only want to read the data.

The runtime of a join statement is heavily dependent on the database optimizer, especially when it contains more than two database tables. However, joins are nearly always quicker than using nested SELECT statements.

Subqueries in the WHERE and HAVING Clauses

Another way of accessing more than one database table in the same Open SQL statement is to use subqueries in the WHERE or HAVING clause. The data from a subquery is not transferred to the application server. Instead, it is used to evaluate conditions in the database system. This is a simple and effective way of programming complex database operations.

Using Internal Tables

It is also possible to avoid nested SELECT loops by placing the selection from the outer loop in an internal table and then running the inner selection once only using the FOR ALL ENTRIES addition. This technique stems from the time before joins were allowed in the FROM clause. On the other hand, it does prevent redundant data from being transferred from the database.

Using a Cursor to Read Data

A further method is to decouple the INTO clause from the SELECT statement by opening a cursor using OPEN CURSOR and reading data line by line using FETCH NEXT CURSOR. You must open a new cursor for each nested loop. In this case, you must ensure yourself that the correct lines are read from the database tables in the correct order. This usually requires a foreign key relationship between the database tables, and that they are sorted by the foreign key.

Message was edited by:

sunil kumar

[Former Member]

Hi Charles,

Do not join bkpf ,bsid ,kna1 n knb1 in single join... It will kill the system.

Join bkpf and bsid into say it_bstab.

Join kna1 and knb1 into say it_ktab.

Loop at it_bstab.

at new / on change of kunnr,

// read it_ktab to fill final table.

endat / endon.

// further processing

endloop.

Use sy-index in loop to enhance performance.(It is available in sample examples given by SAP)

As per SAP document, following code will enhance the performance

• Entries: 1000 (ITAB1), 300 (ITAB2)

• Line width: 100

• Both tables sorted by unique key K ascending

DATA: I TYPE I.

I = 1.

LOOP AT ITAB1 INTO WA1.

do.

READ TABLE ITAB2 INTO WA2 INDEX I.

IF SY-SUBRC <> 0. EXIT. ENDIF.

IF WA2-K < WA1-K.

ADD 1 TO I.

ELSEIF WA2-K = WA1-K.

" ...

ADD 1 TO I.

EXIT.

ELSE.

EXIT.

endif.

enddo.

if sy-subrc <> 0. exit. endif.

ENDLOOP.

[Former Member]

Join BKPF and BSID first.

Then join KNA1 and KNB1.

Now filter your search in the internal table level...

Remember, working at the application level is anyday more faster than interacting with the database.

KNA tables are master tables with relatively lesser records compared to transaction data from BKPF and BSID...

It doesn't matter what logic tyou adapt to, but reduce the number of database access's

Hope this solution was helpful

PS : Please award points if helpful.

[Former Member]

Joining BKPF and BSID(or bsad)

and

joining KNA1 and KNB1.

wont solve the problem

each of these 2 joins may produce more than 10,000 returns..

which is already a killer for DB...

i tried... the time taken at db... is even more than the joining of 4 tables method...

[Former Member]

will open cursor help in this case?