本文章的内容根据SAP系统所带的教程整理而来,查看该性能教程的步骤为:SE38事物代码àEnvironment菜单àExamplesàPerformance Examples
一、 SQL Interface
1. Select ... Where vs. Select + Check
用Select … Where语句效率比Select + Check语句要高,例如:
SELECT * FROM SBOOK INTO SBOOK_WA
WHERE CARRID = 'LH' AND
CONNID = '0400'.
ENDSELECT.
SELECT * FROM SBOOK INTO SBOOK_WA.
CHECK: SBOOK_WA-CARRID = 'LH' AND
SBOOK_WA-CONNID = '0400'.
ENDSELECT.
2. Test Existence
检查满足某个(些)条件的记录是否存在时,使用Select Single效率最高;例如:
SELECT SINGLE * FROM SBOOK
WHERE CARRID = 'LH'.
SELECT * FROM SBOOK INTO SBOOK_WA
WHERE CARRID = 'LH'.
EXIT.
ENDSELECT.
3. Select aggregates
需要对数据库某列求最大,最小,和,平均值或者记录数量时,请使用聚集函数来代替Select … Where + Check的方法,这样效率高而且网络流量小。
DATA: MAX_MSGNR type t100-msgnr.
SELECT MAX( MSGNR ) FROM T100 INTO max_msgnr
WHERE SPRSL = 'D' AND
ARBGB = '00'.
4. Select with select list
取数时请使用Select + 指定的列名称 into,而不要使用Select * into。显示的指定列名称只取出需要的列,不会像Select *会取出相应表的所有的列。
SELECT DOMNAME FROM DD01L
INTO DD01L_WA-DOMNAME
WHERE DOMNAME LIKE 'CHAR%'
AND AS4LOCAL = 'A'.
ENDSELECT.
SELECT * FROM DD01L INTO DD01L_WA
WHERE DOMNAME LIKE 'CHAR%'
AND AS4LOCAL = 'A'.
ENDSELECT.
5. Column Update
尽可能的使用字段(column updates)更新来代替行记录(single-row updates)更新数据库表,这样可以减少网络负载。
UPDATE SFLIGHT
SET SEATSOCC = SEATSOCC - 1.
6. Index and Buffer Support
6.1. Select with index support
Select语句在Where条件中尽量使用索引字段
6.2. Select with buffer support
对于最常用的只读的数据读取,使用SAP Buffering,不要使用BYPASSING BUFFER,例如:
SELECT SINGLE * FROM T100 INTO T100_WA
BYPASSING BUFFER
WHERE SPRSL = 'D'
AND ARBGB = '00'
AND MSGNR = '999'.
7. Array Operations (internal tables)
7.1. Select ... Into Table t
使用Into Table版本的Select 语句要比使用 Append 的方式速度更快,例如:
SELECT * FROM T006 INTO TABLE X006.
DATA T006_WA TYPE T006.
CLEAR X006.
SELECT * FROM T006 INTO T006_WA.
APPEND T006_WA TO X006.
ENDSELECT.
7.2. Array Insert VS Single-row Insert
向数据库中插入数据时,使用内表代替单行操作,减少应用服务与数据库的交互次数,能够有效地减少系统负荷。
INSERT CUSTOMERS FROM TABLE TAB.
LOOP AT TAB INTO TAB_WA.
INSERT INTO CUSTOMERS VALUES TAB_WA.
ENDLOOP.
7.3. Select-Endselect vs. Array-Select
对于只需要使用一次的数据,使用Select-Endselect-loop来代替Select Into
Table。内表需要更多的内存空间。例如:
SELECT * FROM T006 INTO X006_WA.
ENDSELECT.
SELECT * FROM T006
INTO TABLE X006.
LOOP AT X006 INTO X006_WA.
ENDLOOP.
注:个人认为还是取出数据到内表的方式要好,牺牲存储空间,换取时间上的性能。
二、Context
1. Supply/Demand vs. SELECT
三、Internal Tables
1. Using explicit work areas(显示的使用工作区)
显示的指定工作区可以避免不必要的Move操作。见下列内表操作的语句:
APPEND wa TO itab.
INSERT wa INTO itab.
COLLECT wa INTO itab.
MODIFY itab FROM wa.
READ TABLE itab INTO wa.
LOOP AT itab INTO wa.
ITAB = WA.
APPEND ITAB.
2. Linear search vs. binary search
如果内表的数据超过20条,由于线性检索会遍历整个内表,这将会非常耗时。将内表排序并使用Binary Search,或者使用SORTED TABLE类型的内表。如果内表有n条记录,线性查找的次数为O( n ),Binary Search的查找次数为O( log2( n ) ).
READ TABLE ITAB INTO WA WITH KEY K = 'X'
BINARY SEARCH.
READ TABLE ITAB INTO WA
WITH KEY K = 'X'.
3. Dynamic vs. static key access
动态键值的读取比静态键值的读取要慢,
READ TABLE ITAB INTO WA
WITH KEY K = 'X'.
READ TABLE ITAB INTO WA
WITH KEY (NAME) = 'X'.
4. Secondary indices
If you need to access an internal table with different keys repeatedly, keep your own secondary indices. With a secondary index, you can replace a linear search with a binary search plus an index access.
READ TABLE SEC_IDX INTO SEC_IDX_WA
WITH KEY DATE = SY-DATUM
BINARY SEARCH.
IF SY-SUBRC = 0.
READ TABLE ITAB INTO WA
INDEX SEC_IDX_WA-INDX.
" ...
ENDIF.
READ TABLE ITAB INTO WA
WITH KEY DATE = SY-DATUM.
IF SY-SUBRC = 0.
" ...
ENDIF.
5. Key access to multiple lines
LOOP ... WHERE比 LOOP/CHECK更快,因为LOOP ... WHERE只处理满足特定条件的数据。如果LOOP ... WHERE和FROM i1 and/or TO i2条件一起使用,性能会更好。
LOOP AT ITAB INTO WA WHERE K = 'X'.
" ...
ENDLOOP.
LOOP AT ITAB INTO WA.
CHECK WA-K = 'X'.
" ...
ENDLOOP.
6. Sorted and Hashed Tables
6.1. Single Read: Sorted vs. hashed tables
数据在SORTED TABLE类型的内表中按照Binary Search方式组织,检索数据的时间维度为(O (log n))。
数据在HASDED TABLE类型内表中按照hash-algorithm组织,检索数据的时间维度为(O (1))。
HASHED TABLE为单条记录的存取进行了优化,它没有索引(index),而SORTED TABLE优化为loop操作的部分顺序数据的存取。
DO 250 TIMES.
N = 4 * SY-INDEX.
READ TABLE HTAB INTO WA WITH TABLE KEY K = N.
IF SY-SUBRC = 0.
" ...
ENDIF.
ENDDO.
DO 250 TIMES.
N = 4 * SY-INDEX.
READ TABLE STAB INTO WA WITH KEY K = N.
IF SY-SUBRC = 0.
" ...
ENDIF.
ENDDO.
注:根据实测,Hashed Table的Read Table操作比Sorted Table + Binary Search大约快1倍。
6.2. Part. seq. access: Hashed vs. sorted
Hashed tables优化为单条记录的存取,数据在内表中没有特定的顺序,内表没有索引(