indexes, sorted table, subqueries and other performance tips.

[aris_hidalgo]

Hello experts,

I was just given a report by my boss that needs to be optimized. As I browsed through the report, I noticed that some of the comparisons in the select statements uses fields that are not primary keys. So my teammate said that I put indexes and instead of sorting the internal table after all of the select statements, I wonder if a sorted table is much better. Also, I am curious if using subqueries is better and faster that say, FOR ALL ENTRIES. I am currently new to ABAP so if you guys can post examples on how to put an index, how to make my standard itab to a sorted table, using subqueries instead of for all entries, etc. Thanks a lot guys and take care!

[Former Member]

Hi,

You can try creating INDEXES on your table in SE11 to enhance your select query. In SE11, choose menu Goto->Indexes and create Indexes for your table.

Also using FOR ALL ENTRIES is much more performance effective.

See http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb366d358411d1829f0000e829fbfe/content.htm on creating Sorted internal tables.

Regards,

Wenceslaus.

[Former Member]

hi Viray,

As an ABAP developer, use the following tools:

1. SQL Trace (transaction ST05) to analyze the SQL statements

2.Runtime Analysis (transaction SE30) to analyze the ABAP coding

The 5 golden performance rules

1. Keep the result set small!

E.g. use WHERE conditions

2. Minimize the amount of data transferred!

E.g. use field lists and aggregate functions

3.Minimize the number of transfers!

E.g. use joins and subqueries

4. Minimize the search overhead!

The data base should use an appropriate index

5. Reduce the database load!

All users share one data base

[Former Member]

Hi,

Indexes on the Database tables - Needs to be created carefully, we should not create a index on every field that we have in the where clause. Other we will have so many indexes as that of the columns in the table.

You should declared SORTED tables, rather than SORTING the tables using the SORT statement. It will help if you can declare your SORTED tables with keys, so that you can use BINARY SEARCH while reading or looping through the table.

I would still prefer, FOR ALL ENTRIES, compared to Subqueries - however the results might vary depending on the amount of data.

TYPES ITAB TYPE SORTED TABLE OF LINE WITH UNIQUE KEY COLUMN1.

Regards,

Ravi

Note : Please mark the helpful answers

[aris_hidalgo]

Hi gurus,

Can you please provide me on how to use sorted and hashed tables?Thanks you very much guys!

[Former Member]

Hi,

Please look at the following links.

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb35de358411d1829f0000e829fbfe/content.htm

Ths will give you all the details regarding the tables.

Regards,

Ravi

[Former Member]

Hi,

Here is a good link for subqueries:

http://help.sap.com/saphelp_erp2004/helpdata/en/dc/dc7614099b11d295320000e8353423/frameset.htm

- to combine two queries.

Standard tables are managed system-internally by a logical index. New rows are either attached to the table or added at certain positions. The table key or the index identify individual rows.

Sorted tables are managed by a logical index (like standard tables). The entries are listed in ascending order according to table key.

Sorted Internal Tables

Sorted tables are always saved correctly sorted by key. They also have a linear key, and, like standard tables, you can access them using either the table index or the key. When you use the key, the response time is in logarithmic relationship to the number of table entries, since the system uses a binary search. The key of a sorted table can be either unique, or non-unique, and you must specify either UNIQUE or NON-UNIQUE in the table definition. Standard tables and sorted tables both belong to the generic group index tables.

This table type is particularly suitable if you want the table to be sorted while you are still adding entries to it. You fill the table using the (INSERT) statement, according to the sort sequence defined in the table key. Table entries that do not fit are recognised before they are inserted. The response time for access using the key is in logarithmic relation to the number of

table entries, since the system automatically uses a binary search. Sorted tables are appropriate for partially sequential processing in a LOOP, as long as the WHERE condition contains the beginning of the table key.

Hope it helps you.

Regards,

Anjali

[Former Member]

hi..

Indexes

Indexes - Form and Use

Indexes help to speed up selection from the database. They consist of a sorted copy of certain database table fields.

The primary index is always created automatically in the SAP System. It consists of the primary key fields of the database table, and there is at most one record in the table matching each possible combination of these fields. This kind of index is called a UNIQUE index.

If you cannot use the primary index to determine a selection result (for example, WHERE condition may not contain any primary index fields), the system searches the whole table. To prevent this, and determine the selection result by searching through a restricted number of database records, you can create a secondary index.

However, you should not define an index for all possible fields in the WHERE condition.

Creating an index

You can create an index in Transaction SE11 by choosing Change → Indexes... → Create. To make the index unique, select UNIQUE. To specify the fields that will comprise the index, choose "Choose fields". You then need to save and activate the index.

When to create an index

It is worth creating an index when:

You want to select table entries based on fields that are not contained in an index, and the response times are very slow.

The EXPLAIN function in the SQL trace shows which index the system is using. You can generate a list of the database queries involved in an action by entering Transaction ST05 and choosing Trace on → Execute action → Trace off → List trace. If you execute the EXPLAIN SQL function on a EXEC, REEXEC, OPEN, REOPEN or PREPARE statement, the system returns a list containing the index used in the database query.

The field or fields of the new secondary index are so selective that each index entry corresponds to at most 5% of the total number of table entries. Otherwise, it is not worth creating the index.

The database table is accessed mainly for reading entries.

Using an index consisting of several fields

Even if an index consists of several fields, you can still use it when only a few of the fields actually appear in the WHERE clause. The sequence in which the fields are specified in the index is important. You can only use a field in the index if all of the preceding fields in the index definition are included in the WHERE condition.

An index can only support search criteria which describe the search value positively, such as EQ or LIKE. The response time of conditions including NEQ is not improved by an index.

Optimal number of fields for an index

An index should only consist of a few fields; as a rule, no more than four. This is because the index has to be updated each time you change its fields in a database operation.

Fields to include in an index

Include fields that are often selected and have a high selectivity. In other words, you need to check the proportion of the table entries that can be selected with this field. The smaller the proportion, the more selective the field. You should place the most selective fields at the beginning of the index.

If all of the fields in a SELECT statement are contained in the index, the system does not access the data a second time following the index access. If there are only a few fields in the SELECT statmeent, you can improve performance significantly by including all of these fields in the index.

You should not include a field in an index if its value is initial for most of the table entries.

Optimal number of indexes for a table

You should not create more than five indexes for any one table because:

Whenever you change table fields that occur in the index, the index itself is also updated.

The amount of data increases.

The optimizer has too many chances to make mistakes by using the 'wrong' index.

If you are using more than one index for a database table, ensure that they do not overlap.

Avoiding OR conditions

The optimizer generally stops if the WHERE condition contains an OR expression. In other words, it does not evaluate the fields in the OR expression with reference to the index.

An exception to this are OR statements standing on their own. Try to reformulate conditions containing an OR expression for one of the indexed fields. For example, replace:

SELECT * FROM SPFLI

WHERE CARRID = 'LH'

AND (CITYFROM = 'FRANKFURT' OR CITYFROM = 'NEW YORK').

with:

SELECT * FROM SPFLI

WHERE (CARRID = 'LH' AND CITYFROM = 'FRANKFURT')

OR (CARRID = 'LH' AND CITYFROM = 'NEW YORK').

Problems with IS NULL

The value NULL is not stored in the index structure of some database systems. The consequence of this is that the index is not used for that field.

regards,

Ananaya.S

[Former Member]

hai viray,

u can refer the examples given in this link.

http://www.sap-img.com/abap/what-is-use-of-using-hashed-table.htm

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb366d358411d1829f0000e829fbfe/content.htm

hope this is helpful.

regards,

praba.

[Former Member]

Hello Viralab,

Following two sites contains the links for the SAP performance tuning points. Very good one and helpful -

http://www.thespot4sap.com/Articles/SAPABAPPerformanceTuning_Introduction.asp

http://www.thespot4sap.com/Articles/SAPABAPPerformanceTuning_PerformanceAnalysisTools.asp

PS: If the answer solves your query, plz reward points.

Regards

[Former Member]

You can almost always improve the performance of a select that doesn't use an index by digging into the code and finding a way to use an existing index. Could you post the code that is slow?

Rob