Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

tuning FAQ

Former Member
0 Kudos

hi all

can anybody send me some docs how a select query can be performance tuned uusing indexes.

it would be helpful if examples are shown.

Also can i get some examples of - (when there is a join of 4 to 5 tables and a query needs to be breaked upfor tuning.)

thanks in advance

1 ACCEPTED SOLUTION

Former Member
0 Kudos

Hi,

Check this link

<b>Performance tuning for Data Selection Statement & Others</b>

http://www.sap-img.com/abap/performance-tuning-for-data-selection-statement.htm

http://www.sapdevelopment.co.uk/perform/performhome.htm

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

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

<b>Please Reward Points & Mark Helpful Answers</b>

To mark Helpful Answers ;click radio Button next to the post.

RadioButtons

<b>o</b> Helpful Answer

<b>o</b> Very helpful Answer

<b>o</b> Problem Solved.

Click any of the above button next to the post; as per the anwers

<b>To close the thread; Click Probelm solved Radio Button next to the post , which u feel is best possible answers</b>

5 REPLIES 5

Former Member
0 Kudos

Hi,

Check this link

<b>Performance tuning for Data Selection Statement & Others</b>

http://www.sap-img.com/abap/performance-tuning-for-data-selection-statement.htm

http://www.sapdevelopment.co.uk/perform/performhome.htm

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

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

<b>Please Reward Points & Mark Helpful Answers</b>

To mark Helpful Answers ;click radio Button next to the post.

RadioButtons

<b>o</b> Helpful Answer

<b>o</b> Very helpful Answer

<b>o</b> Problem Solved.

Click any of the above button next to the post; as per the anwers

<b>To close the thread; Click Probelm solved Radio Button next to the post , which u feel is best possible answers</b>

0 Kudos

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 &#8594; Indexes... &#8594; 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 &#8594; Execute action &#8594; Trace off &#8594; 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.

Former Member
0 Kudos

Hi Kunal,

I think for the performance of our program if we select data through single than put it into internal table that is the best practice because at that time our selection fast from database but as per your requirement There is an example :

DATA: BEGIN OF WA,

CARRID TYPE SPFLI-CARRID,

CONNID TYPE SPFLI-CONNID,

FLDATE TYPE SFLIGHT-FLDATE,

BOOKID TYPE SBOOK-BOOKID,

END OF WA,

ITAB LIKE SORTED TABLE OF WA

WITH UNIQUE KEY CARRID CONNID FLDATE BOOKID.

SELECT PCARRID PCONNID FFLDATE BBOOKID

INTO CORRESPONDING FIELDS OF TABLE ITAB

FROM ( ( SPFLI AS P

INNER JOIN SFLIGHT AS F ON PCARRID = FCARRID AND

PCONNID = FCONNID )

INNER JOIN SBOOK AS B ON BCARRID = FCARRID AND

BCONNID = FCONNID AND

BFLDATE = FFLDATE )

WHERE P~CITYFROM = 'FRANKFURT' AND

P~CITYTO = 'NEW YORK' AND

FSEATSMAX > FSEATSOCC.

LOOP AT ITAB INTO WA.

AT NEW FLDATE.

WRITE: / WA-CARRID, WA-CONNID, WA-FLDATE.

ENDAT.

WRITE / WA-BOOKID.

ENDLOOP.

Regards.

Ankur Garg.

former_member184619
Active Contributor
0 Kudos

Hi Kunal,

To get the advantage of indexes in ur Select query

1. Try to use the index fields in your Query.

2. Use them in same order as defined in index.

3. Suppose u have 4 fields in Index and u used 1,2,4 then it will take the index but not use it properly.

For this u can also include the third field in ur clause and pass all the values for the field.

4. In case u r sure that their is a better index than the system is picking u can also specify it in ur query like this:-

SELECT carrid connid cityfrom

FROM spfli INTO (xcarrid, xconnid, xcityfrom)

WHERE carrid = 'LH ' AND cityfrom = 'FRANKFURT'

<b> %_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'.</b> WRITE: / xcarrid, xconnid, xcityfrom.

ENDSELECT.

Hope it helps u.

Regards

-


Sachin Dhingra