Skip to Content
author's profile photo Former Member
Former Member

Abt Performance

Hi,

Iam new to sap...Could any one plz explain me in detail how performance improves when u use table indexes,table buffers,For all entries.Is For all entries and outter join is same.

Thanks.

Add comment
10|10000 characters needed characters exceeded

4 Answers

  • Posted on Sep 27, 2007 at 10:13 PM

    se30

    hints&tricks

    (effort)

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 27, 2007 at 11:07 PM

    Hi,

    The only use of index is faster access of data base tables.

    Always use all the fields for which the index has been created in where clause in Select statements.

    Uses of FOR ALL Entries are :

    Outer join can be created using this addition to the where clause in a select statement. It speeds up the performance tremendously, but the cons of using this variation are listed below

    ->Duplicates are automatically removed from the resulting data set. Hence care should be taken that the unique key of the detail line items should be given in the select statement.

    ->If the table on which the For All Entries IN clause is based is empty, all rows are selected into the destination table. Hence it is advisable to check before-hand that the first table is not empty.

    ->If the table on which the For All Entries IN clause is based is very large, the performance will go down instead of improving. Hence attempt should be made to keep the table size to a moderate level.

    For table buffers, go thru this link..

    http://help.sap.com/saphelp_nw04/helpdata/en/c4/3a6e09505211d189550000e829fbbd/frameset.htm

    Hope it helps...

    Regards,

    KK

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 28, 2007 at 12:38 AM
  • author's profile photo Former Member
    Former Member
    Posted on Sep 28, 2007 at 06:57 AM

    Hi

    <b>Ways of Performance Tuning</b>

    Ways of Performance Tuning

    1. Selection Criteria

    2. Select Statements

    • Select Queries

    • SQL Interface

    • Aggregate Functions

    • For all Entries

    Select Over more than one Internal table

    <b>Selection Criteria</b>

    1. Restrict the data to the selection criteria itself, rather than filtering it out using the ABAP code using CHECK statement.

    2. Select with selection list.

    <b>Points # 1/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

    SELECT CARRID CONNID FLDATE BOOKID FROM SBOOK INTO TABLE T_SBOOK

    WHERE SBOOK_WA-CARRID = 'LH' AND

    SBOOK_WA-CONNID = '0400'.

    <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 PF1 PF2 FF3 FF4 INTO TABLE ITAB

    FROM EKKO AS P INNER JOIN EKAN AS F

    ON PEBELN = FEBELN.

    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.

    <b>

    Select Statements contd.. SQL Interface</b>

    1. Use column updates instead of single-row updates

    to update your database tables.

    2. For all frequently used Select statements, try to use an index.

    3. Using buffered tables improves the performance considerably.

    <b>Point # 1</b>

    SELECT * FROM SFLIGHT INTO SFLIGHT_WA.

    SFLIGHT_WA-SEATSOCC =

    SFLIGHT_WA-SEATSOCC - 1.

    UPDATE SFLIGHT FROM SFLIGHT_WA.

    ENDSELECT.

    The above mentioned code can be more optimized by using the following code

    UPDATE SFLIGHT

    SET SEATSOCC = SEATSOCC - 1.

    <b>Point # 2</b>

    SELECT * FROM SBOOK CLIENT SPECIFIED INTO SBOOK_WA

    WHERE CARRID = 'LH'

    AND CONNID = '0400'.

    ENDSELECT.

    The above mentioned code can be more optimized by using the following code

    SELECT * FROM SBOOK CLIENT SPECIFIED INTO SBOOK_WA

    WHERE MANDT IN ( SELECT MANDT FROM T000 )

    AND CARRID = 'LH'

    AND CONNID = '0400'.

    ENDSELECT.

    <b>Point # 3</b>

    Bypassing the buffer increases the network considerably

    SELECT SINGLE * FROM T100 INTO T100_WA

    BYPASSING BUFFER

    WHERE SPRSL = 'D'

    AND ARBGB = '00'

    AND MSGNR = '999'.

    The above mentioned code can be more optimized by using the following code

    SELECT SINGLE * FROM T100 INTO T100_WA

    WHERE SPRSL = 'D'

    AND ARBGB = '00'

    AND MSGNR = '999'.

    <b>Select Statements contd… Aggregate Functions</b>

    • If you want to find the maximum, minimum, sum and average value or the count of a database column, use a select list with aggregate functions instead of computing the aggregates yourself.

    Some of the Aggregate functions allowed in SAP are MAX, MIN, AVG, SUM, COUNT, COUNT( * )

    Consider the following extract.

    Maxno = 0.

    Select * from zflight where airln = ‘LF’ and cntry = ‘IN’.

    Check zflight-fligh > maxno.

    Maxno = zflight-fligh.

    Endselect.

    The above mentioned code can be much more optimized by using the following code.

    Select max( fligh ) from zflight into maxno where airln = ‘LF’ and cntry = ‘IN’.

    <b>Select Statements contd…For All Entries</b>

    • The for all entries creates a where clause, where all the entries in the driver table are combined with OR. If the number of entries in the driver table is larger than rsdb/max_blocking_factor, several similar SQL statements are executed to limit the length of the WHERE clause.

    The plus

    • Large amount of data

    • Mixing processing and reading of data

    • Fast internal reprocessing of data

    • Fast

    The Minus

    • Difficult to program/understand

    • Memory could be critical (use FREE or PACKAGE size)

    <u>Points to be must considered FOR ALL ENTRIES</u> • Check that data is present in the driver table

    • Sorting the driver table

    • Removing duplicates from the driver table

    Consider the following piece of extract

    Loop at int_cntry.

    Select single * from zfligh into int_fligh

    where cntry = int_cntry-cntry.

    Append int_fligh.

    Endloop.

    The above mentioned can be more optimized by using the following code.

    Sort int_cntry by cntry.

    Delete adjacent duplicates from int_cntry.

    If NOT int_cntry[] is INITIAL.

    Select * from zfligh appending table int_fligh

    For all entries in int_cntry

    Where cntry = int_cntry-cntry.

    Endif.

    <b>

    Select Statements contd… Select Over more than one Internal table</b>

    1. Its better to use a views instead of nested Select statements.

    2. To read data from several logically connected tables use a join instead of nested Select statements. Joins are preferred only if all the primary key are available in WHERE clause for the tables that are joined. If the primary keys are not provided in join the Joining of tables itself takes time.

    3. Instead of using nested Select loops it is often better to use subqueries.

    <b>Point # 1</b>

    SELECT * FROM DD01L INTO DD01L_WA

    WHERE DOMNAME LIKE 'CHAR%'

    AND AS4LOCAL = 'A'.

    SELECT SINGLE * FROM DD01T INTO DD01T_WA

    WHERE DOMNAME = DD01L_WA-DOMNAME

    AND AS4LOCAL = 'A'

    AND AS4VERS = DD01L_WA-AS4VERS

    AND DDLANGUAGE = SY-LANGU.

    ENDSELECT.

    The above code can be more optimized by extracting all the data from view DD01V_WA

    SELECT * FROM DD01V INTO DD01V_WA

    WHERE DOMNAME LIKE 'CHAR%'

    AND DDLANGUAGE = SY-LANGU.

    ENDSELECT

    <b>Point # 2</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 PF1 PF2 FF3 FF4 INTO TABLE ITAB

    FROM EKKO AS P INNER JOIN EKAN AS F

    ON PEBELN = FEBELN.

    <b>Point # 3</b>

    SELECT * FROM SPFLI

    INTO TABLE T_SPFLI

    WHERE CITYFROM = 'FRANKFURT'

    AND CITYTO = 'NEW YORK'.

    SELECT * FROM SFLIGHT AS F

    INTO SFLIGHT_WA

    FOR ALL ENTRIES IN T_SPFLI

    WHERE SEATSOCC < F~SEATSMAX

    AND CARRID = T_SPFLI-CARRID

    AND CONNID = T_SPFLI-CONNID

    AND FLDATE BETWEEN '19990101' AND '19990331'.

    ENDSELECT.

    The above mentioned code can be even more optimized by using subqueries instead of for all entries.

    SELECT * FROM SFLIGHT AS F INTO SFLIGHT_WA

    WHERE SEATSOCC < F~SEATSMAX

    AND EXISTS ( SELECT * FROM SPFLI

    WHERE CARRID = F~CARRID

    AND CONNID = F~CONNID

    AND CITYFROM = 'FRANKFURT'

    AND CITYTO = 'NEW YORK' )

    AND FLDATE BETWEEN '19990101' AND '19990331'.

    ENDSELECT.

    <b>Reward if usefull</b>

    Add comment
    10|10000 characters needed characters exceeded