2, 避免使用过多的LOOP 和SELECT .... END SELECT. 避免使用嵌套的LOOP 和SELECT .... END SELECT.
3, 尽可能多地使用表的KEY FIELD作为Where分句的条件选项。比如SELECT * FROM BSEG WHERE BUKRS = '1000' AND BELNR = '0100000007' AND GJAHR = '2006' AND BUZEI = '003'. 这里的四个字段BUKRS,BELNR,GJAHR,BUZEI 都是BSEG表的KEY字段.
5, 使用SQL语句里面的JOIN时候, 应该避免JOIN的表不要超过3个, 否则严重影响效率. 如果真的要JOIN表3个以上的话, 正确的方法不是用视图VIEW, 而是使用SELECT ... INTO TABLE ... FOR ALL ENTRIES IN 以及 READ TABLE WITH KEY BINARY SEARCH.例如我们要提高读取BSEG表的性能,首先我们会根据GJAHR主键从BKPF表取出部分数据到内表itab,然后使用FOR ALL ENTRIES IN itab WHERE BSEG~BELNR = itab~BELNR 这样的的方法取得符合itab里所有条件的BSEG数据.注意使用FOR ALL ENTRIES要先CHECK作为条件的内表itab是否为空,还有作为WHERE的条件在这个内表里面是否会有空值存在,否则无效.
6, 注意使用CORRESPONDING FIELDS OF 和 MOVE-CORRESPONDING 时候会进行字段比较, 带来CPU的开销大.
SELECT * FROM SBOOK INTO SBOOK_WA UP TO 10 ROWS.
SELECT SINGLE AIRPFROM AIRPTO INTO (AP1, AP2)
FROM SPFLI
WHERE CARRID = SBOOK_WA-CARRID
AND CONNID = SBOOK_WA-CONNID.
SELECT SINGLE NAME INTO NAME1 FROM SAIRPORT
WHERE ID = AP1.
SELECT SINGLE NAME INTO NAME2 FROM SAIRPORT
WHERE ID = AP2.
ENDSELECT.
代码2:
SELECT * FROM SBOOK INTO SBOOK_WA UP TO 10 ROWS.
SUPPLY CARRID = SBOOK_WA-CARRID
CONNID = SBOOK_WA-CONNID
TO CONTEXT TRAV1.
DEMAND AIRPFROM = AP1
AIRPTO = AP2
NAME_FROM = NAME1
NAME_TO = NAME2
FROM CONTEXT TRAV1.
ENDSELECT.
4, 内存使用紧张的情况下, 使用FREE语句, 以及SQL语句的PACKAGE SIZE n 选项.
SELECT vbeln erdat
FROM vbak
INTO TABLE li_vbak PACKAGE SIZE 50.
1、使用where语句
不推荐
Select* from zflight.
Check : zflight-airln = ‘LF’ and zflight-fligh = ‘BW222’.
Endselect.
推荐
Select* from zflight where airln = ‘LF’ and fligh = ‘222’.
Endselect.
2、使用聚合函数
不推荐
Maxnu = 0.
Select* from zflight where airln = ‘LF’ and cntry = ‘IN’.
Check zflight-fligh >maxnu.
Maxnu = zflight-fligh.
Endselect.
推荐
Select max( fligh ) from zflight intomaxnu where airln = ‘LF’ and cntry = ‘IN’.
3、使用视图代替基本表查询
不推荐
Select* from zcntry where cntry like ‘IN%’.
Selectsingle * from zflight where cntry = zcntry-cntry and airln = ‘LF’.
Endselect.
推荐
Select* from zcnfl where cntry like ‘IN%’ and airln = ‘LF’.
Endselect.
4、使用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.
5、使用批量修改内表代替逐行修改
不推荐
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.
当使用不带表头的内表时,需要从工作区删除,例如
MODIFY IT_TEST1 FROM WA_TEST1 TRANSPORTING BOX WHERE BOX IS INITIAL.
7、两个内表添加使用批量增加代替逐行
不推荐
Loop at int_fligh1.
Append int_fligh1 to int_fligh2.
Endloop.
推荐
Append lines of int_fligh1 to int_fligh2.
8、使用table buffering
Use of buffered tables is recommended to improve the performance considerably. The buffer is bypassed while using the following statementsSelectdistinct
Select… for update
Order by, group by, having clause
Joins
Use the Bypass buffer addition to theselectclause in order to explicitly bypass the buffer whileselecting the data.
9、 使用FOR ALL Entries
不推荐
Loop at int_cntry.Selectsingle * from zfligh into int_flighwhere 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.
10、正确地使用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.
11、正确地使用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.
12、正确地使用inner joinLet 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.
Selecta~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 theselection criteria, a where clause can be added to the above inner join.