topic Program - Indexes in Application Development and Automation Discussions

Program - Indexes

Former Member — Fri, 20 Jun 2008 05:19:20 GMT

Dear friends,

Here the major problem is, even though the reports made use of primary and secondary indexs, they are going to sequential read instead of direct read and the performance is badly affected.

Oracle is used as SAP database.

Kindly suggest.

Praveen Lobo

Re: Program - Indexes

Former Member — Fri, 20 Jun 2008 05:25:12 GMT

Hi,

When no key is used in a query, the system will perform a sequence seeking in the database. When some key is referred, the system will access directly that data, becoming the query easier. To certify a secondary index is being used, it is possible to use the option "Explain SQL", in transaction ST05.

SAP Database is oracle.

If you cannot use the primary index to determine the result set because, for example, none of the fields primary index occur in the WHERE or HAVING clause, the system searches through the entire table (full table scan). For this case, you can create secondary indexes, which can restrict the number of table entries searched to form the result set.

You should only create secondary indexes, for database tables from which you mainly read, since indexes have to be updated each time the database table is changed. secondary indexes should contain columns that you use frequently in a selection, and that are as highly selective as possible to improve performance..

For better performance of ur program do the following.

Practices to avoid

To have a good performance you should avoid:

Nested Select commands (use For All Entries instead);

Nested Loops (use Parallel Cursor or Indexed Loop);

Select * instead of determining the proper fields;

Select Single without complete keys (use Select ... Up To 1 Rows... Endselect instead);

Select and Exit to check existence of a record. Use Select .. up to 1 rows or if all keys are available, Select single .

Absence of indexes or primary key seeks;

Paying no attention when selecting from Cluster Tables;

Move-Corresponding clause (due to field-by-field comparison);

Sort internal_table instead of Sort internal_table by field1 field2.

Generic Tips

Select statement field order: When any Select command is executed, the database server uses some Declare and Prepare statements before concluding the selection. Due to this, it is preferible to describe the selection fields and the where clause fields in the same order they are find in the database table, as it can avoid some Declare and Prepare statements on the database server.

Logical Database: Imagine a scenario where you have a logical database defined with four tables. Now you have a program which has to use only three of that tables. If the three first tables are declared on the tables clause, the database will automatically bring all the key fields for the fourth table. Due to that, sometimes it is better to declare all the tables and use a get command only to one of its key fields.

Direct Access: When no key is used in a query, the system will perform a sequence seeking in the database. When some key is referred, the system will access directly that data, becoming the query easier. To certify a secondary index is being used, it is possible to use the option "Explain SQL", in transaction ST05.

Database Resources: When possible, use database resources such as sum, avg, min and max commands.This will reduce the traveling information volume, as well as internal processing effort.

"OR" Operators: Several or operators can be replaced by one in operator: z1 = a1 and ( z2 = y1 or z2 = y2 or z2 = y3) could be replaced by z1 = a1 and z2 in ( y1, y2 , y3).

Cluster Tables: This kind of table has a particular behavior when being queried with where conditions when the compared fields are not keys; as it has all the non-key fields compacted, comparing them means to have to unpack them, which means extra processing effort. So it is better to make the where comparison using only the key fields, and to delete the unwished ones in a separated delete command.

Types: When not selecting all the fields of a database table, use TYPES to determine which fields to use. This will grant no unwished data will be selected, thus decreasing the communication flow from database to application layer. It follows a sample:

Work Areas: For a better performance it is preferible using internal declared structures in your program named work areas, instead of declaring tables with header lines. This will allow you to use sometimes the same one-record structure for more than one internal table. Use work areas for inserting and updating records. It folows a simple code sample, as in complement to last item's code:

Field-Symbols: Use field-symbols almost exactly the same way than work areas. Use them, however, to read-only record cases. Work areas also can be used to read, but that affects performance considering the values are stored in memmory. Field-symbols, though, work as a pointer to the required record. Therefore it is also why appending records from field-symbols are not allowed. It follows a sample of how is worthy using this resource:

INSERT command

For more than one insertions, prefer using the following sintax :

INSERT db_table FROM TABLE internal_table ACCEPTING DUPLICATE KEYSFOR ALL ENTRIES clause

Take care about duplicated records on the comparison table. They can consume a lot more from your processing. Also, always check if the comparison table is empty, as it is not necessary executing the selection if there is no comparison to do. Finally, always consist the data validation you need for the comparison table regarding your programming context.

READ TABLE

Always check if you have all the needed keys to perform the reading of an internal table. If read data will only be use for reading, use field-symbol, else choose using a work area. When using this command just with purpose of verifying a record's existence, do not forget using the TRANSPORTING NO FIELDS clause.

Re: Program - Indexes

Former Member — Fri, 20 Jun 2008 05:45:31 GMT

Hi,

Indexes speed up data selection from the database. They consist of selected fields of a table, of which a copy is then made in sorted order. If you specify the index fields correctly in a condition in the WHERE or HAVING clause, the system only searches part of the index (index range scan).

The primary index is always created automatically in the R/3 System. It consists of the primary key fields of the database table. This means that for each combination of fields in the index, there is a maximum of one line in the table. This kind of index is also known as UNIQUE.

If you cannot use the primary index to determine the result set because, for example, none of the primary index fields occur in the WHERE or HAVING clause, the system searches through the entire table (full table scan). For this case, you can create secondary indexes, which can restrict the number of table entries searched to form the result set.

You specify the fields of secondary indexes using the ABAP Dictionary . You can also determine whether the index is unique or not. However, you should not create secondary indexes to cover all possible combinations of fields.

Only create one if you select data by fields that are not contained in another index, and the performance is very poor. Furthermore, you should only create secondary indexes for database tables from which you mainly read, since indexes have to be updated each time the database table is changed. As a rule, secondary indexes should not contain more than four fields, and you should not have more than five indexes for a single database table. If a table has more than five indexes, you run the risk of the optimizer choosing the wrong one for a particular operation. For this reason, you should avoid indexes with overlapping contents.

Secondary indexes should contain columns that you use frequently in a selection, and that are as highly selective as possible. The fewer table entries that can be selected by a certain column, the higher that columnu2019s selectivity. Place the most selective fields at the beginning of the index. Your secondary index should be so selective that each index entry corresponds to at most five percent of the table entries. If this is not the case, it is not worth creating the index. You should also avoid creating indexes for fields that are not always filled, where their value is initial for most entries in the table. If all of the columns in the SELECT clause are contained in the index, the system does not have to search the actual table data after reading from the index. If you have a SELECT clause with very few columns, you can improve performance dramatically by including these columns in a secondary index.

Hope this will clarify your doubt.

Plz reward if useful.

Thanks,

Dhanashri.

Re: Program - Indexes

Former Member — Mon, 23 Jun 2008 10:02:14 GMT

Hello,

When i check in ST05, indexes are properly used (in Explain button). But when i check in SM50 during execution all sql statements are read sequentially (even though indexes are used in the statements), cause poor performance.

The below statement uses primary index, but shows sequential read during execution.

SELECT matnr maktx

INTO TABLE it_makt

FROM makt

FOR ALL ENTRIES IN it_main

WHERE matnr EQ it_main-matnr and spras = 'E'.

Kindly suggest.

Praveen Lobo

Re: Program - Indexes

Former Member — Mon, 23 Jun 2008 10:08:07 GMT

Hi,

Does it_main have any value ??? if the table doesnt have any records, the select on makt will go for seq. search.

regards,

madhu

Re: Program - Indexes

Former Member — Mon, 23 Jun 2008 10:10:32 GMT

Hi,

SELECT matnr maktx

INTO TABLE it_makt

FROM makt

FOR ALL ENTRIES IN it_main

WHERE matnr EQ it_main-matnr and spras = 'E'.


In the above statement the program checks for the matnr in it_main where Spars = 'E'.
i.e., It will check for all matnr with Spars = 'E'.
So for those records matching this criteria sequential reading will be there.
For better Performance sort ur internal table by matnr and spars.
i.e., using the primary keys sort ur internal table.

Best Regards,

Jagadish Avidi

Re: Program - Indexes

Former Member — Mon, 23 Jun 2008 10:18:15 GMT

Hello,

The it_main table contain values and it is sorted by matnr.

Even if i goto se16 and fetch data from table MKPF with selection of budat it will go for sequential read, though it is having secondary index on budat.

Regards,

Praveen Lobo