ABAP 優化文件【來自網路】加個人體會 - 3

Proper structure of Where Clause：關鍵字排序的問題

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.

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. One more tip is that if a table begins with MANDT, while an index does not, there is a high possibility that the optimizer might not use that index.

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.

Proper use of Move statement：

Instead of using the move-corresponding clause it is advisable to use the move statement instead. Attempt should be made to move entire internal table headers in a single shot, rather than moving the fields one by one.

 

個人體會：可能有效率，但是太麻煩了。

 

Proper use of Inner Join

When multiple SAP tables are logically joined, it is always advisable to use inner join to read the data from them. This certainly reduces the load on the network. 

Let us take an example of 2 tables, zairln and zflight. The table zairln has the field airln, which is the airline code and the field lnnam, which is the name of the airline. The table zflight has the field airln, the airline code and other fields which hold the details of the flights that an airline operates.

Since these 2 tables a re logically joined by the airln field, it is advisable to use the inner join.

             Select a~airln a~lnnam b~fligh b~cntry into table int_airdet

            From zairln as a inner join zflight as b on a~airln = b~airln.

 In order to restrict the data as per the selection criteria, a where clause can be added to the above inner join.

個人體會：他不說，我也是主動這麼乾地，為什麼呢，方便呀

 

Use of ABAP Sort instead of Order By

The order by clause is executed on the database server, while the sort statement is executed on the application server. Thus instead of giving the order by in the select clause statement, it is better to collect the records in an internal table and then use the sort command to sort the resulting data set.

個人體會：一個是伺服器做，一個是程式段做，本地做不影響系統效能吧

 

Tools provided for Performance Analysis

Following are the different tools provided by SAP for performance analysis of an ABAP object

1. Run time analysis transaction SE30

This transaction gives all the analysis of an ABAP program with respect to the database and the non-database processing. 

2. SQL Trace transaction ST05

The trace list has many lines that are not related to the SELECT statement in the ABAP program. This is because the execution of any ABAP program requires additional administrative SQL calls. To restrict the list output, use the filter introducing the trace list.

The trace list contains different SQL statements simultaneously related to the one SELECT statement in the ABAP program. This is because the R/3 Database Interface - a sophisticated component of the R/3 Application Server - maps every Open SQL statement to one or a series of physical database calls and brings it to execution. This mapping, crucial to R/3s performance, depends on the particular call and database system. For example, the SELECT-ENDSELECT loop on the SPFLI table in our test program is mapped to a sequence PREPARE-OPEN-FETCH of physical calls in an Oracle environment.

The WHERE clause in the trace list's SQL statement is different from the WHERE clause in the ABAP statement. This is because in an R/3 system, a client is a self-contained unit with separate master records and its own set of table data (in commercial, organizational, and technical terms). With ABAP, every Open SQL statement automatically executes within the correct client environment. For this reason, a condition with the actual client code is added to every WHERE clause if a client field is a component of the searched table.

To see a statement's execution plan, just position the cursor on the PREPARE statement and choose Explain SQL. A detailed explanation of the execution plan depends on the database system in use.

個人體會：SE30 ,我喜歡

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/554557/viewspace-470537/，如需轉載，請註明出處，否則將追究法律責任。