performace

[Former Member]

hi

i want to extract data from the table.

table is such that it has 3 primary keys and remaining are

non primary keys

now i want to extract one primary key and remaining non primary keys

how can i code select query which should be highly effective in performance point of view

just answer me friends.......................

[Former Member]

Hi

<b>Select Statements Select Queries</b>

1. Avoid nested selects

2. Select all the records in a single shot using into table clause of select statement rather than to use Append statements.

3. When a base table has multiple indices, the where clause should be in the order of the index, either a primary or a secondary index.

4. For testing existence , use Select.. Up to 1 rows statement instead of a Select-Endselect-loop with an Exit.

5. Use Select Single if all primary key fields are supplied in the Where condition .

<b>Point # 1</b>

SELECT * FROM EKKO INTO EKKO_WA.

SELECT * FROM EKAN INTO EKAN_WA

WHERE EBELN = EKKO_WA-EBELN.

ENDSELECT.

ENDSELECT.

The above code can be much more optimized by the code written below.

SELECT P_{F1 P}F2 F_{F3 F}F4 INTO TABLE ITAB

FROM EKKO AS P INNER JOIN EKAN AS F

ON P_{EBELN = F}EBELN.

Note: 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. One should therefore use nested SELECT loops only if the selection in the outer loop contains very few lines or the outer loop is a SELECT SINGLE statement.

<b>Point # 2</b>

SELECT * FROM SBOOK INTO SBOOK_WA.

CHECK: SBOOK_WA-CARRID = 'LH' AND

SBOOK_WA-CONNID = '0400'.

ENDSELECT.

The above code can be much more optimized by the code written below which avoids CHECK, selects with selection list and puts the data in one shot using into table

SELECT CARRID CONNID FLDATE BOOKID FROM SBOOK INTO TABLE T_SBOOK

WHERE SBOOK_WA-CARRID = 'LH' AND

SBOOK_WA-CONNID = '0400'.

<b>Point # 3</b>

To choose an index, the optimizer checks the field names specified in the where clause and then uses an index that has the same order of the fields . In certain scenarios, it is advisable to check whether a new index can speed up the performance of a program. This will come handy in programs that access data from the finance tables.

<b>Point # 4</b>

SELECT * FROM SBOOK INTO SBOOK_WA

UP TO 1 ROWS

WHERE CARRID = 'LH'.

ENDSELECT.

The above code is more optimized as compared to the code mentioned below for testing existence of a record.

SELECT * FROM SBOOK INTO SBOOK_WA

WHERE CARRID = 'LH'.

EXIT.

ENDSELECT.

<b>Point # 5</b>

If all primary key fields are supplied in the Where condition you can even use Select Single.

Select Single requires one communication with the database system, whereas Select-Endselect needs two.

[Former Member]

> table is such that it has 3 primary keys and remaining are

> non primary keys

a table has exactly one primary key, that is the index, which can have several key fields!

> now i want to extract one primary key and remaining non primary keys

you want too select records, and you specify values for certain fields in the WHERE condition.

If you know only one primary key field, then it should be the first one of the primary key and it should be selective (many different values).

Otherwise you should check for secondary keys, whih can be other combintions of table fields, i.e. the ones of your WHERE condition.

Check also the size of your table, how many records in total, the selectivity of your fields, and the condition in the WHERE condition (equal, in, <, >, FOR ALL ENTRIES etc).

Siegfried

[Former Member]

Hi,

Suppose if you want really improve the performance u need to find first any secondary indexs avialable for non-key fields, if not you have to better to create seconday index for non -key field, but i am not sure to give permission to create secodary index.

Normally key-fields are used to unique identification.

standard or z tables create the index by default for key-fields.

for better perfomance of select statements we have to use the key fileds in where condition.

Suppose there is a situation example you need to get all material information from MARA table by using material group is MATKL, MBRSH . but these fields are non-key fields, when we use in select statement performance wise it causes worst.

in this situation you have to create secondary index, but remember one thing dont create secondary index unless it highly priority to create. its take same memory capcity of what exactly table having, lets suppose mara occupies memory like 10 MB, when u create secondary index it will also require 10 MB memory.

for this also you need to take permission from basis people.

Steps to create Secondary index.

-goto SE11 select the table name and dispay it.

- press the tab '<b>INDEX'</b> then you will get one popup screen here you have to give name of the index and press ok it ask work bench request under that save that.

-it opend the index maintain screen and here you need to fill some attributes like 'short description', non-unique or unique index.

-enter the field names which you want to create index and press enter and save it.

-<b>activate it</b>.

<b>Reward with points if useful.</b>

Regards,

Vijay

[Former Member]

Hi Kiran,

First you check for index, if its available use the same. If its not available and you have the permission create the Index.

If nothing works out then select form database table using one primary key.

After that delete the records form internal table based on your non primary key conditions.

And select all the records in single shot. For Ex

Select * from PA0001 into table i_pa0001 where ………

After selection delete the records based on your conditions.

Ex: delete itab where paydt NE sy-datum.