SELECT OPTIMIZATION URGENT

Former Member — Wed, 03 Oct 2007 08:04:24 GMT

I have to select data from this table FMIOI...

but it take a lotta time. DO U KNOW HOW TO OPTIMIZE IT???

THIS IS URGENT!!!

HERE'S THE EXAMPLE.

select

REFBT

RFORG

RFPOS

RFKNT

RFETE

RFTYP

RFSYS

STUNR

GJAHR PERIO FIKRS BUKRS FONDS

FISTL FIPEX WRTTP RLDNR BTART FKBTR

REFBN

from FMIOI

into table IT_FMIOI

for all entries in ALV3_DATA

where

( FONDS in S04 ) and

( FIPEX = ALV3_DATA-S15 ) and

( FISTL = ALV3_DATA-S13 ) and

( GJAHR = Input_Data-S06 ) and

( PERIO = Input_Data-S08 ) and

( FIKRS = Input_Data-S02 ) and

( BUKRS = Input_Data-S00 )

Re: SELECT OPTIMIZATION URGENT

Former Member — Wed, 03 Oct 2007 08:08:27 GMT

select

REFBT

RFORG

RFPOS

RFKNT

RFETE

RFTYP

RFSYS

STUNR

GJAHR PERIO FIKRS BUKRS FONDS

FISTL FIPEX WRTTP RLDNR BTART FKBTR

<b>REFBN into corresponding fields of table it_fmi01 from fmio1</b>

for all entries in ALV3_DATA

where

( FONDS in S04 ) and

( FIPEX = ALV3_DATA-S15 ) and

( FISTL = ALV3_DATA-S13 ) and

( GJAHR = Input_Data-S06 ) and

( PERIO = Input_Data-S08 ) and

( FIKRS = Input_Data-S02 ) and

( BUKRS = Input_Data-S00 )

Re: SELECT OPTIMIZATION URGENT

Former Member — Wed, 03 Oct 2007 08:11:04 GMT

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.

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.

topic Re: SELECT OPTIMIZATION URGENT in Application Development and Automation Discussions

SELECT OPTIMIZATION URGENT

Re: SELECT OPTIMIZATION URGENT

Re: SELECT OPTIMIZATION URGENT