ABAP Performance

[Former Member]

Hello All,

Apart from :

1. SELECTING only the required fields from Table

2. SELECTING the records and placing them into INTERNAL table and avoiding SELECT .....ENDSELECT loop.

3. Avoiding a SELECT with in a loop by using the READ statement

4. Using JOINS where ever appropriate (INNER JOIN / OUTER JOIN) and carefully

5. Using FAE (For All Entries) where ever necessary with all the necessary checks

6. Using ABAP HINTS - to force the db optimizer to follow the specified index

7. Check for the Primary INDEXEs or the Secondary INDEXes created on the table and forcing the optimizer to follow that path

8. Avoiding NE option in the WHERE clause, selecting all the records and then having it deleted from the Internal table

Do we have any other options by which we can Optimize our reports.

Ur views on this this will be very helpful......

- Sravan

[Former Member]

Make sure that you are using the index / key, by using the 'where' on fields in the key that you do not have values to check against. For example, if there are no indexes on KNB1, just the prime key; KUNNR, BUKRS. Then your access for all customers within a company, would become.

SELECT * FROM KNB1

       WHERE KUNNR > SPACE

       AND BUKRS = your_extract_company.

Make sure that there are no duplicates in your FAE or 'IN' tables.

For some dbs you need to extract the fields of the index you want to use to get the index used.

Aranging the where fields in the order of the index.

Sometimes it is better not to use the 'order by' clause, but to sort in your program. It takes the load off the DB-sever and places it on the application server.

Oh! The obvious of course. Make sure you are using the right keys to access the table. Once had a program for a weekly report that took over 9 days to run (it never finished normally). Rewrote the access on the tables, using the right key, finished in under 2 Hours.

Think about organising how you access reference tables, for things like customer name, if you only access KNA1 on change of customer number, you reduce the number of db accesses. But also, if you sort the data into customer number order, you reduce the number of accesses again.

[ulrich_koch]

In general it is not a good idea to supply "dummy conditions" like "KUNNR > SPACE". This will only trick the database into usage of a certain index which is not selective for the given WHERE condition. Better let the optimizer decide which index to use. In the case above:

- either add an index with column BUKRS if you really got a lot of different values (> 20) for BUKRS, or

- just let the database decide what to do: it is probably better to have a full table scan but to use an index that doesn't help. Because then the database has to read all the index blocks which will point to the same data blocks again and again and it will read a lot of datablocks twice or even more often.

A BAD INDEX IS WORSE THEN A FULL TABLE SCAN.  

[former_member641343]

In addition to the above, look at the performance of certain commands on your DB.

For example, HINTS can only be used with an underlying Oracle DB.  

For All Entries produces UNION JOINS for each line of your lookup table when expanded into native SQL on a SQL2K DB (not good for performance at all!)

Frankly, I don't believe in hard and fast rules, sometimes something that looks ugly works really well and sometimes really well-structured and seemly feasible code runs terribly.

Encourage developers to use SE30, ST05 etc. as well as your 'tips and tricks' sheet for performance testing and you should not produce badly performing code.

Regards

Rosie Brent

[Former Member]

When I look at secondary indizes in ABAP (Release 4.6c) I find that around half of all secondary Indizes of the standard SAP tables (I looked up tables dd12l and dd17s) have the client field (mandt) as ist first entry, others don't have the client field at all.

I wonder what could be the arguments using the client field in a secondary index or not.

[Former Member]

Sorry! I made the mistake to count tables which don't have the client field at all.

I made a new count. I found 3393 secondary indizes on standard SAP tables (with client field as part of the table content).

1020 of these don't have the client field in the secondary index.

The question stays the same:

what is the advantage respective disadvantage of using the client field in a secondary index?

Kind regards Axel Kiltz

[ulrich_koch]

Hello Axel,

if you select data with a certain WHERE condition that suggest the usage of a secondary index AND you specify an ORDER BY clause which specifies the same order that the index is ordered by, then the ordering comes for free if the secondary index contains the client field as first column. The client columns is (almost) always automatically added as first column in the ORDER BY clause. So, if the index doesn't contain the client column, the database doesn't "know" that there is no need for an extra sort.

Best regards

Ulrich

[Former Member]

All of these topics are good, but don't forget to design the program. When processing large tables it is not a good practice to make one trip to the database. For example MARC with 7 million records. Write a perfect select statement, using any key or index and it will run nicely if no one else is on the system. Add 2500 users and that perfectly written select statement is going to chug along. Better to take the time to process the 7 million records in a loop where one plant is processed at a time. This shares the system with other processes. Use ST05 to see the overall cost of the statement is about the same; STAT will show how long it takes. Remember in a test environment with little activity, it may take longer to process the loop. However, in a heavily used production system it will be faster.