在ABAP开发中,
标准表(Standard Table)
能搞定绝大多数情况下的数据操作。当数据量累积到一定量级后,使用标准表明显感觉卡顿的时候,可以考虑使用
排序表(Sorted Table)
或者
哈希表(Hashed Table)
。
一、排序表
排序表将内容按照NON-UNIQUE KEY
字段升序排序。插入数据时,系统会自动将数据放在指定位置。因为数据已经排序的原因,系统通过二分法(Binary search)
能快速地查找到数据。
*
因为数据自动排序的缘故,对排序表插入数据时,要使用insert
,避免使用append
。
二、哈希表
哈希表的原理是将数据的UNIQUE KEY
字段通过算法生成哈希值,将这个唯一的哈希值保存在一块内存中,系统可以通过哈希值快速定位到数据。因为不需要遍历表,哈希表在处理海量数据时性能突出。
哈希表使用要求:
- 需指定
UNIQUE KEY
字段。 - 表中的数据不可重复,既不能存在两条数据的
UNIQUE KEY
字段值相同。
三、效率对比
1.声明表
声明数据库表
@EndUserText.label : 'KDLK Flights'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zkdlk_flights {
key client : abap.clnt not null;
key carrier_id : /dmo/carrier_id not null;
key connection_id : /dmo/connection_id not null;
key flight_date : /dmo/flight_date not null;
@Semantics.amount.currencyCode : 'zkdlk_flights.currency_code'
price : /dmo/flight_price;
currency_code : /dmo/currency_code;
plane_type_id : /dmo/plane_type_id;
seats_max : /dmo/plane_seats_max;
seats_occupied : /dmo/plane_seats_occupied;
}
2.填充数据
从/DMO/FLIGHT
表中复制一部分数据,并生成新的数据,填充到zkdlk_flights
表中。
创建一个类,在main方法中填入以下代码,运行。插入了642000
条数据。
DATA FLIGHTS TYPE TABLE OF /DMO/FLIGHT.
DATA INSERT_TAB TYPE TABLE OF /DMO/FLIGHT.
DELETE FROM ZKDLK_FLIGHTS.
SELECT FROM /DMO/FLIGHT
FIELDS *
ORDER BY CARRIER_ID, CONNECTION_ID
INTO TABLE @FLIGHTS.
LOOP AT FLIGHTS INTO DATA(FIRST_DATE).
IF SY-TABIX MOD 2 = 0.
CONTINUE.
ENDIF.
DO 2000 TIMES.
APPEND FIRST_DATE TO INSERT_TAB.
FIRST_DATE-FLIGHT_DATE += 1.
ENDDO.
ENDLOOP.
SELECT FROM /DMO/FLIGHT AS MAIN
FIELDS CARRIER_ID, CONNECTION_ID, FLIGHT_DATE, PRICE, CURRENCY_CODE, PLANE_TYPE_ID
WHERE CONNECTION_ID = ( SELECT MAX( CONNECTION_ID ) FROM /DMO/FLIGHT WHERE CARRIER_ID = MAIN~CARRIER_ID )
AND FLIGHT_DATE = ( SELECT MIN( FLIGHT_DATE ) FROM /DMO/FLIGHT WHERE CARRIER_ID = MAIN~CARRIER_ID AND CONNECTION_ID = MAIN~CONNECTION_ID )
GROUP BY CARRIER_ID, CONNECTION_ID, FLIGHT_DATE, PRICE, CURRENCY_CODE, PLANE_TYPE_ID
ORDER BY CARRIER_ID, CONNECTION_ID
INTO TABLE @DATA(MAX).
LOOP AT MAX INTO DATA(LINE).
DO 50 TIMES.
LINE-CONNECTION_ID += 1.
LINE-PLANE_TYPE_ID = SWITCH #( CONV I( LINE-CONNECTION_ID ) MOD 2 WHEN 0 THEN 'A330' WHEN 1 THEN 'A350' ).
FIRST_DATE = CORRESPONDING #( LINE ).
DATA(REPETITIONS) = COND I( WHEN LINE-CARRIER_ID = 'LH' AND LINE-CONNECTION_ID = '0405' THEN 4000 ELSE 2000 ).
DO REPETITIONS TIMES.
FIRST_DATE-SEATS_MAX = 220.
APPEND FIRST_DATE TO INSERT_TAB.
FIRST_DATE-FLIGHT_DATE += 1.
ENDDO.
ENDDO.
ENDLOOP.
SORT INSERT_TAB BY CARRIER_ID CONNECTION_ID FLIGHT_DATE.
DELETE ADJACENT DUPLICATES FROM INSERT_TAB COMPARING CARRIER_ID CONNECTION_ID FLIGHT_DATE.
INSERT ZKDLK_FLIGHTS FROM TABLE @INSERT_TAB.
OUT->WRITE( SY-DBCNT ).
3.读取数据
将准备好的数据分别用三种内表读取,并在内表中查找指定的数据。
创建类,在Local Types
中填入以下代码。
CLASS LCL_FLIGHTS DEFINITION.
PUBLIC SECTION.
METHODS CONSTRUCTOR.
METHODS ACCESS_STANDARD.
METHODS ACCESS_SORTED.
METHODS ACCESS_HASHED.
PROTECTED SECTION.
PRIVATE SECTION.
DATA STANDARD_TABLE TYPE STANDARD TABLE OF ZKDLK_FLIGHTS WITH NON-UNIQUE KEY CARRIER_ID CONNECTION_ID FLIGHT_DATE.
DATA SORTED_TABLE TYPE SORTED TABLE OF ZKDLK_FLIGHTS WITH NON-UNIQUE KEY CARRIER_ID CONNECTION_ID FLIGHT_DATE.
DATA HASHED_TABLE TYPE HASHED TABLE OF ZKDLK_FLIGHTS WITH UNIQUE KEY CARRIER_ID CONNECTION_ID FLIGHT_DATE.
DATA KEY_CARRIER_ID TYPE /DMO/CARRIER_ID.
DATA KEY_CONNECTION_ID TYPE /DMO/CONNECTION_ID.
DATA KEY_FLIGHT_DATE TYPE /DMO/FLIGHT_DATE.
METHODS SET_LINE_TO_READ.
ENDCLASS.
CLASS LCL_FLIGHTS IMPLEMENTATION.
METHOD CONSTRUCTOR.
SELECT FROM ZKDLK_FLIGHTS FIELDS * INTO TABLE @STANDARD_TABLE.
SELECT FROM ZKDLK_FLIGHTS FIELDS * INTO TABLE @SORTED_TABLE.
SELECT FROM ZKDLK_FLIGHTS FIELDS * INTO TABLE @HASHED_TABLE.
SET_LINE_TO_READ( ).
ENDMETHOD.
METHOD SET_LINE_TO_READ.
DATA(LINE) = STANDARD_TABLE[ CONV I( LINES( STANDARD_TABLE ) * '0.8' ) ].
ME->KEY_CARRIER_ID = LINE-CARRIER_ID.
ME->KEY_CONNECTION_ID = LINE-CONNECTION_ID.
ME->KEY_FLIGHT_DATE = LINE-FLIGHT_DATE.
ENDMETHOD.
METHOD ACCESS_STANDARD.
DATA(RESULT) = STANDARD_TABLE[ CARRIER_ID = ME->KEY_CARRIER_ID CONNECTION_ID = ME->KEY_CONNECTION_ID FLIGHT_DATE = ME->KEY_FLIGHT_DATE ].
ENDMETHOD.
METHOD ACCESS_SORTED.
DATA(RESULT) = SORTED_TABLE[ CARRIER_ID = ME->KEY_CARRIER_ID CONNECTION_ID = ME->KEY_CONNECTION_ID FLIGHT_DATE = ME->KEY_FLIGHT_DATE ].
ENDMETHOD.
METHOD ACCESS_HASHED.
DATA(RESULT) = HASHED_TABLE[ CARRIER_ID = ME->KEY_CARRIER_ID CONNECTION_ID = ME->KEY_CONNECTION_ID FLIGHT_DATE = ME->KEY_FLIGHT_DATE ].
ENDMETHOD.
ENDCLASS.
在main中填入以下代码,激活,运行。
DATA(FLIGHTS) = NEW LCL_FLIGHTS( ).
FLIGHTS->ACCESS_STANDARD( ).
FLIGHTS->ACCESS_SORTED( ).
FLIGHTS->ACCESS_HASHED( ).
OUT->WRITE( 'Done' ).
4.结果对比
通过ABAP Traces查看三种方式的执行时间。
总结
三种表的使用情况:
- 数据量小的时候,使用标准表即可。
- 数据量大,并且存在查找相关操作时,考虑使用哈希表或排序表。
打完收工。