Performance doubt

[Former Member]

hi all,

please let me know, what i should consider to write a good select statement. Also how do I know how much time my each select statement took. I know there is option to which says how time each statement took.

Can I use this on Idoc processing programs as well..

Thank you

madhu

[Former Member]

Check these some points -

Select Statements Select Queries

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 .

Point # 1

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.

Point # 2

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'.

Point # 3

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.

Point # 4

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.

Point # 5

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.

Also to see the runtime for SELECT statements you can use SQL Trace.

Check these threads for more details -

Hope this helps.

ashish

[Former Member]

check if your table has an index and try to follow the same structure in the Where condition.

Also, you can test in SE38, Environment, examples, performance examples.

[Former Member]

Hi Madhu,

few things which i can tell are

1. Try to maintain all the key fields in where clause.

2. Use For all entries instead of joins or nested queries.

3. fetch data only for required fields instead putting select *.

4. try to use the secondary index, make sure to keep the secondar index fields as the first fields in where clause then maintain the order as per the fields order in table.

<b>Reward for helpful answers</b>

Satish

[Former Member]

You can use transaction ST05 to examine the performance of a SELECT and also use the EXPLAIN function within ST05 to compare different SELECTs.

Rob