PageOutline {{{#!comment -------------------- 以上的部份是固定的，不要變動 ----- ---------------------------------------- 在建立連結時，請標明連結的頁面類型，例如：wiki:WikiStart，而不要只用 WikiStart ----- }}}

DatabasePerformance

• 增進資料庫效率

使用 WHERE 語句

• 不推薦

  Select * from zflight. Check / zflight-airln='LF' and zflight-fligh='BW222'. Endselect.

• 推薦

  Select * from zflight where airln='LF' and fligh='222'. Endselect.

使用函數

• 不推薦

  Maxnu=0. Select * from zflight where airln='LF' and cntry='IN'. Check zflight-fligh > maxnu. Maxnu=zflight-fligh. Endselect.

• 推薦

  Select max( fligh ) from zflight into maxnu where airln='LF' and cntry='IN'.

使用 View

• 不推薦

  Select * from zcntry where cntry like 'IN%'. Select single * from zflight where cntry=zcntry-cntry and airln='LF'. Endselect.

• 推薦

  Select * from zcnfl where cntry like 'IN%' and airln='LF'. Endselect.

使用 INTO TABLE 代替 SELECT ENDSELECT

• 不推薦

  Refresh/ int_fligh. Select * from zflight into int_fligh. Append int_fligh. Clear int_fligh. Endselect.

• 推薦

  Refresh/ int_fligh. Select * from zflight into table int_fligh.

使用批次修改代替逐行修改

• 不推薦

  Loop at int_fligh. If int_fligh-flag is initial. Int_fligh-flag='X'. Endif. Modify int_fligh. Endloop.

• 推薦

  Int_fligh-flag='X'. Modify int_fligh transporting flag where flag is initial.

使用 BINARY SEARCH

• 不推薦

  Read table int_fligh with key airln='LF'.

• 推薦

  Read table int_fligh with key airln='LF' binary search.

使用批次增加代替逐行

• 不推薦

  Loop at int_fligh1. Append int_fligh1 to int_fligh2. Endloop.

• 推薦

  Append lines of int_fligh1 to int_fligh2.

使用 table buffering

Use of buffered tables is recommended to improve the performance considerably. The buffer is bypassed while using the following statementsSelect distinct Select … for update Order by, group by, having clause Joins Use the Bypass buffer addition to the select clause in order to explicitly bypass the buffer while selecting the data.

使用FOR ALL Entries

• 不推薦

  Loop at int_cntry. Select single * from zfligh into int_fligh where cntry=int_cntry-cntry. Append int_fligh. Endloop.

• 推薦

  Select * from zfligh appending table int_fligh For all entries in int_cntry Where cntry=int_cntry-cntry.

= 正確地使用 WHERE 使查詢能使用索引= 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.

正確地使用 MOVE

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.

正確地使用 INNER JOIN

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 aairln alnnam bfligh bcntry into table int_airdet From zairln as a inner join zflight as b on aairln=bairln. In order to restrict the data as per the selection criteria, a where clause can be added to the above inner join.

使用 SORT BY 代替 ORDER BY

避免使用SELECT DISTINCT語句

• 使用的 ABAP SORT + DELETE ADJACENT DUPLICATES 代替.

FOR ALL ENTRIES 的表超過1000行了，效率就低了

使用SELECT 字段列表代替 Select *

• 不推薦

  SELECT *
  	FROM DD01L
  	INTO DD01L_WA
  	WHERE DOMNAME LIKE 'CHAR%'
  		AND AS4LOCAL='A'.
  ENDSELECT.
  

• 推薦

  SELECT DOMNAME
  	FROM DD01L
  	INTO DD01L_WA-DOMNAME
  	WHERE DOMNAME LIKE 'CHAR%'
  		AND AS4LOCAL='A'.
  ENDSELECT.
  

使用subquery代替多個 SELECT 語句

• 不推薦

  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.
  

• 推薦

  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.
  

如何快速刪除重複內表的記錄

• 不推薦

  READ TABLE ITAB INDEX 1 INTO PREV_LINE.
  LOOP AT ITAB FROM 2 INTO WA.
  	IF WA=PREV_LINE.
  		DELETE ITAB.
  	ELSE.
  		PREV_LINE=WA.
  	ENDIF.
  ENDLOOP.
  

• 推薦

  DELETE ADJACENT DUPLICATES FROM ITAB
  COMPARING K.
  

如何快速刪除內表一定範圍內的記錄

• 不推薦

  DO 101 TIMES.
  	DELETE ITAB INDEX 450.
  ENDDO.
  

• 推薦

  DELETE ITAB FROM 450 TO 550.
  

使用collect語句代替人工合計計算結果

• 不推薦

  LOOP AT ITAB1 INTO WA1.
  	READ TABLE ITAB2 INTO WA2 WITH KEY K=WA1-K BINARY SEARCH.
  	IF SY-SUBRC=0.
  		ADD/ WA1-VAL1 TO WA2-VAL1,
  		WA1-VAL2 TO WA2-VAL2.
  		MODIFY ITAB2 FROM WA2 INDEX SY-TABIX TRANSPORTING VAL1 VAL2.
  	ELSE.
  		INSERT WA1 INTO ITAB2 INDEX SY-TABIX.
  	ENDIF.
  ENDLOOP.
  

• 推薦

  LOOP AT ITAB1 INTO WA.
  	COLLECT WA INTO ITAB2.
  ENDLOOP.
  SORT ITAB2 BY K.
  

使用顯示的work area 代替默認的work area

• 不推薦

  ITAB=WA.
  APPEND ITAB.
  

• 推薦

  APPEND WA TO ITAB.
  

使用LOOP ...ASSIGNING代替loop ... modify.

• 不推薦（整條記錄更新）

  LOOP AT ITAB INTO WA.
  	I=SY-TABIX MOD 2.
  	IF I=0.
  		WA-FLAG='X'.
  		MODIFY ITAB FROM WA.
  	ENDIF.
  ENDLOOP.
  

• 推薦（只會更新需要修改內容）

  LOOP AT ITAB ASSIGNING <WA>.
  	I=SY-TABIX MOD 2.
  	IF I=0.
  		<WA>-FLAG='X'.
  	ENDIF.
  ENDLOOP.
  

{{{#!comment -------------------- 以下的部份是固定的，不要變動 ----- }}}

• wiki:tagDataIndex