Indexes in table join

[Former Member]

Hi Experts,

I have three tables and I am using a join condition to link the three tables. Just need to know how can I improve the performance of the select query by using indexes. Can a secondary index be used in join table where in the where clause i have attributes from three different tables. If yes how do I do it.

Please suggest solution with examples.

Help will be much appreciated.

Regards.

[Former Member]

Hi Sarma,

Extracting data from three tables using a join condition would decrease the performance. Instead, it is better to extract the data from the first table, then use 'For all entries' to get the data from the second table and finally use the same option 'For all entries' to get the data from the third table. Besides this, you can use secondary indexes for all the three tables (each index for each table) which will help improve the performance of all the three select statements. For creating secondary indexes, go to transaction SE11, enter the table name and enter in the change mode and then click on the 'Indexes' button.Say yes to create a new one by giving the Index Id, Short text and finally give the field names (the field names which you are using in you select query) and save and activate.

For example, your select statements should look similar like the below: Here I am using only two selects and two tables. In your case, use one more select query to extract data from your third table.

First Select Query:

select bukrs lifnr into table i_bsak from bsak where bukrs in s_bukrs.

Second Select Query:

select zbukr chect lifnr into table i_payr from payr for all entries in i_bsak where zbukr = i_bsak-bukrs.

Hope this helps.

Thanks,

Srinivasa

[Former Member]

Hi

joining 3 tables may refer in bad performance

because if the number of table or 2 then you can go for joins

if the number of tables are more than 2 then use for all entries

because if you use more than 3 tables in the data base connection will be there for that 3 tables up to displaying the data

if you use for all entries then no need of joining the tables

finally

for all entries have more performance than joins

<b>Primary and secondary indexes</b>

Index: Technical key of a database table.

Primary index: The primary index contains the key fields of the table and a pointer to the non-key fields of the table. The primary index is created automatically when the table is created in the database.

Secondary index: Additional indexes could be created considering the most frequently accessed dimensions of the table.

<b>Structure of an Index</b>

An index can be used to speed up the selection of data records from a table.

An index can be considered to be a copy of a database table reduced to certain fields. The data is stored in sorted form in this copy. This sorting permits fast access to the records of the table (for example using a binary search). Not all of the fields of the table are contained in the index. The index also contains a pointer from the index entry to the corresponding table entry to permit all the field contents to be read.

When creating indexes, please note that:

An index can only be used up to the last specified field in the selection! The fields which are specified in the WHERE clause for a large number of selections should be in the first position.

Only those fields whose values significantly restrict the amount of data are meaningful in an index.

When you change a data record of a table, you must adjust the index sorting. Tables whose contents are frequently changed therefore should not have too many indexes.

Make sure that the indexes on a table are as disjunctive as possible.

(That is they should contain as few fields in common as possible. If two indexes on a table have a large number of common fields, this could make it more difficult for the optimizer to choose the most selective index.)

<b>Accessing tables using Indexes</b>

The database optimizer decides which index on the table should be used by the database to access data records.

You must distinguish between the primary index and secondary indexes of a table. The primary index contains the key fields of the table. The primary index is automatically created in the database when the table is activated. If a large table is frequently accessed such that it is not possible to apply primary index sorting, you should create secondary indexes for the table.

The indexes on a table have a three-character index ID. '0' is reserved for the primary index. Customers can create their own indexes on SAP tables; their IDs must begin with Y or Z.

If the index fields have key function, i.e. they already uniquely identify each record of the table, an index can be called a unique index. This ensures that there are no duplicate index fields in the database.

When you define a secondary index in the ABAP Dictionary, you can specify whether it should be created on the database when it is activated. Some indexes only result in a gain in performance for certain database systems. You can therefore specify a list of database systems when you define an index. The index is then only created on the specified database systems when activated

<b>Reward if usefull</b>

[Former Member]

Hi Sarma,

Joining 3 Tables will lead to bad performance. Its better to use for all entries.

eg :

Select flds from table1 into itab1 where <condn>.

select felds from table2 into itab2 for all entries in itab1 where <condn>.

select fields from table3 into itab3 for all entries in itab2 where <condn>.

Reward If Useful.

Regards,

Chitra

[Former Member]

Thanks for all the comments.

Please let me know how will the secondary indexes be used with for all enteries. Do I need to create a secondary index for every 'where clause' . i.e 3 secondary indexes for 3 select queries. Will this improve the performance?

for eg

select a from db1 into itab1 where x = 'val'.

select b from db2 into itab2 for all enteries in itab1 where a = 'val2'.

select c from db3 into itab3 for all enteries in itab2 where b = 'val3'.

so in db1 i will be creating a secondary index for the attribute x.

similarly in db2 i will be creating a secondary index for the attribute a.

and in db3 i will be creating a secondary index for the attribute b.

Please let me know if this is the right approach.

Regards.

[Former Member]

Hi Sarma,

Yes, you need to create three secondary indexes for those three tables.Definitely there will be improvement in the performance. Yes, this is the right approach.

So, for table1/db1, create a secondary index including the fields what you mention after the 'select'. Like that, create the other two indexes for the remaining two tables.First of all, check whether there are any secondary indexes or not for those three tables having the same fields in the index what you mention after the 'select' statement in your query.

Hope this helps.

Thanks,

Srinivasa

[Former Member]

Hi,

Performance issues

1. Try to join those tables with the key-fields, use all key-fields, if that is a composite key.

2. In any case, If you are not using the key-fields in the join condition, try to create a secondary index from SE11 for that field. And, It automatically, takes that secondary index, while executing that join condition.

Try this,

KC

[Former Member]

JOINS generally give better performance than FOR ALL ENTRIES. Please see:

<a href="/people/rob.burbank/blog/2007/03/19/joins-vs-for-all-entries--which-performs-better">JOINS vs. FOR ALL ENTRIES - Which Performs Better?</a>

Rob

[Former Member]

Let me second Rob Burbank on the issues of JOINS vs FOR ALL ENTRIES.

It is commonly said on the forums that FOR ALL ENTRIES is better without consideration of specifics, but this is not universally true.

Each approach has its appropriate uses, but it is certainly not true that FOR ALL ENTRIES is universally better, especially if you understand what goes on under the covers with large tables in such a case.

The best way to determine what is better for you is to use the performance tools provided with the SAP environment.

As others have noted, what is most important is the use of indexes in your WHERE clauses and JOINS.

FOR ALL ENTRIES works best when the number of entries in the specified internal table is small. Otherwise, there will be too many database requests, and this cancels out the advantages, since blocking could become inefficient (as multiple requests for data might be made by FOR ALL ENTRIES without consideration of blocking) and there might be a high amount of unnecessary network traffic between the application server and the database server (again, this is because the number of requests generated by FOR ALL ENTRIES may be numerouspossibly thousandsof individal SQL requests if the internal table is large).

First address the use of key fields and indexes. Then use performance tools (such as System>Utilities>Runtime analysis) to evaluate whether FOR ALL ENTRIES or JOIN are better <u>in your specific situation</u>.

Good luck

Brian