ABAP 基础知识用 SQL 处理内表
原创 无峰 [ ABAP 技巧与实战 ](javascript:void\(0\);)
一
前言
ABAP升级新语法,很多新语法带来了更简洁的代码及更容易理解的代码形式.
下图是截至S4 1909版本的ABAP语法升级信息(该截图来自ABAP中的帮助)
在项目中也越来越多的使用新语法来处理逻辑,
内表是ABAP中最关注处理性能的部分,新语法允许把内表引入到SQL语句中执行.因此带来了使用SQL语句处理内表的新方式
**本文通过示例比较内表常用的几种处理的新旧语法的性能比较**
二
示例程序的数据样本
来自ACDOCA表,该系统中记录数30,129
三
SQL处理内表
用@标记引入的内表且必须给出别名,into子句要放到最后
@内表 as a
获取一条记录
分组小计
内表关联物理表(推荐使用,取代for all entries in )
四
通过关键字访问内表
新语法可以把内表当作一个表引入到select 语句中, 但限制了每次只能引入一个内表
因此可以通过SELECT 语句读取内表的单条记录
但是通过示例程序对二分法,哈希法及SQL法的比较
对标准内表的SELECT SINGLE 访问性能很差.耗时远远高于哈希,二分法读取
五
通过关键字访问不同类型的内表
基于上一个结论联想到内表的三种不同类型(标准,排序,哈希)在正常read读取的差异.这个差异应该也会存在于SELECT SINGLE 读取内表.
因此比较了三种不同内表的SELECT SINGLE差异.
从下图中可以看出SELECT SINGLE 读取不同类型的内表,耗时是不同的. 读取哈希内表耗时最低, 但耗时也比 read table 读取哈希表多很多(
117 VS 12 )
六
内表小计的比较
之前写过文章介绍了几种内表小计的方式
> 详见链接 无峰，公众号：ABAP 技巧与实战 [ ABAP基础知识 内表汇总数据的方式
> ](https://mp.weixin.qq.com/s?__biz=Mzg4MjA1MjE4NQ==&mid=2247486185&idx=1&sn=dfb12f1551e4cebb85962e8ceb8bd7ed&chksm=cf5dd7dff82a5ec941fd7ac532035fb8236dec7b8d5a33666caa0517aee07a0b6bf0384a3caf&token=167701858&lang=zh_CN#rd)
为了和select 语法比较性能.示例程序中给出了几种常用方法
• COLLECT语句
• 循环用哈希表统计
• 用LOOP GROUP统计
• 用SELECT统计
比较结果出乎意料: SELECT 统计内表(标准内表)耗时低于其它方式. 看来使用SQL语句统计内表可以作为首选方式了.
还有个更吃惊的结论: 直接从数据库统计性能远高于从内表统计( 9478 VS 35754 ),这还不包括从数据库读取数据到内表的耗时.
看来对于统计类的数据处理逻辑下沉到数据库层级是非常有必要的
七
程序源代码
*&---------------------------------------------------------------------**& Report ZTS_ITAB_PROC*&---------------------------------------------------------------------**&通过程序比较内表的关键字访问,统计的性能差异*&P_1 比较读取内表的单条记录*&P_2 通过select single 访问内表(三种内表类型比较)*&P_3 内表几种小计方式比较*&---------------------------------------------------------------------*REPORT zts_itab_proc.
PARAMETERS: p_1 AS CHECKBOX, p_2 AS CHECKBOX, p_3 AS CHECKBOX.
DATA: lt_hs TYPE HASHED TABLE OF acdoca WITH UNIQUE KEY rclnt rldnr rbukrs gjahr belnr docln, "哈希内表 lt_st TYPE TABLE OF acdoca, "通常内表 lt_ss TYPE SORTED TABLE OF acdoca WITH NON-UNIQUE KEY rclnt rldnr rbukrs gjahr belnr docln.IF p_1 = 'X'. cl_demo_output=>next_section('内表关键字访问(微秒)'). cl_demo_output=>next_section('-内表关键字访问-哈希'). PERFORM frm_access_with_key_hs. cl_demo_output=>next_section('-内表关键字访问-二分'). PERFORM frm_access_with_key_st. cl_demo_output=>next_section('-内表关键字访问-select'). PERFORM frm_access_with_key_select. cl_demo_output=>write('结论:哈希会优化性能,二分法性能也可以,内表作为表的select 语句没有性能优化').ENDIF.
IF p_2 = 'X'. cl_demo_output=>next_section('select single 内表(微秒)'). cl_demo_output=>write('结论:通过SELECT single 语句访问内表性能差于通过read table 访问内表,内表类型对SELECT SINGLE 访问存在优化'). PERFORM frm_access_by_select_itab.ENDIF.
IF p_3 = 'X'. PERFORM frm_compare_sum.ENDIF.perform frm_inner_join.
cl_demo_output=>display( ).*&---------------------------------------------------------------------**& Form frm_access_with_key*&---------------------------------------------------------------------**& text*&---------------------------------------------------------------------**& --> p1 text*& <-- p2 text*&---------------------------------------------------------------------*FORM frm_access_with_key_hs . "耗时考虑,哈希表的读取,及访问 GET RUN TIME FIELD DATA(t1). SELECT * FROM acdoca INTO TABLE lt_hs. GET RUN TIME FIELD DATA(t2). READ TABLE lt_hs INTO DATA(ls_hs) WITH TABLE KEY rclnt = '800' rldnr = '0L' rbukrs = '2450' gjahr = '2021' belnr = '0100000239' docln = '000003' . GET RUN TIME FIELD DATA(t3).cl_demo_output=>write( |取数耗时: { t2 - t1 }| ). cl_demo_output=>write( |读取耗时: { t3 - t2 }| ). cl_demo_output=>write( |总耗时 : { t3 - t1 }| ).
ENDFORM.
FORM frm_access_with_key_st . "耗时考虑,哈希表的读取,及访问 GET RUN TIME FIELD DATA(t1). SELECT * FROM acdoca INTO TABLE lt_st ORDER BY rldnr rbukrs gjahr belnr docln. GET RUN TIME FIELD DATA(t2). READ TABLE lt_st INTO DATA(ls_st) WITH KEY rldnr = '0L' rbukrs = '2450' gjahr = '2021' belnr = '0100000239' docln = '000003' BINARY SEARCH. GET RUN TIME FIELD DATA(t3).cl_demo_output=>write( |取数耗时: { t2 - t1 }| ). cl_demo_output=>write( |读取耗时: { t3 - t2 }| ). cl_demo_output=>write( |总耗时 : { t3 - t1 }| ).
ENDFORM.
FORM frm_access_with_key_select. DATA: ls_st TYPE acdoca. "耗时考虑,哈希表的读取,及访问 GET RUN TIME FIELD DATA(t1). SELECT * FROM acdoca INTO TABLE lt_st . " ORDER BY rldnr rbukrs gjahr belnr docln. GET RUN TIME FIELD DATA(t2). SELECT SINGLE * FROM @lt_st AS a WHERE rldnr = '0L' AND rbukrs = '2450' AND gjahr = '2021' AND belnr = '0100000239' AND docln = '000003' INTO @ls_st. GET RUN TIME FIELD DATA(t3). READ TABLE lt_st INTO ls_st WITH KEY rldnr = '0L' rbukrs = '2450' gjahr = '2021' belnr = '0100000239' docln = '000003'. GET RUN TIME FIELD DATA(t4). READ TABLE lt_st INTO ls_st WITH KEY rldnr = '0L' rbukrs = '2450' gjahr = '2024' belnr = '0100001960' docln = '000001'. GET RUN TIME FIELD DATA(t5).cl_demo_output=>write( |取数耗时 : { t2 - t1 }| ). cl_demo_output=>write( |读取耗时 : { t3 - t2 }| ). cl_demo_output=>write( |READ耗时(前面的行): { t4 - t3 }| ). cl_demo_output=>write( |READ耗时(后面的行): { t5 - t4 }| ). cl_demo_output=>write( |SELECT总耗时 : { t3 - t1 }| ).
ENDFORM.
FORM frm_access_by_select_itab. DATA: ls_st TYPE acdoca. "耗时考虑,哈希表的读取,及访问 GET RUN TIME FIELD DATA(t1). SELECT * FROM acdoca INTO TABLE lt_st ORDER BY rldnr rbukrs gjahr belnr docln. GET RUN TIME FIELD DATA(t2). lt_hs[] = lt_st[]. GET RUN TIME FIELD DATA(t3). lt_ss[] = lt_st[]. GET RUN TIME FIELD DATA(t4).
SELECT SINGLE * FROM @lt_st AS a WHERE rclnt = '800' AND rldnr = '0L' AND rbukrs = '2450' AND gjahr = '2021' AND belnr = '0100000239' AND docln = '000003' INTO @ls_st. GET RUN TIME FIELD DATA(t5). SELECT SINGLE * FROM @lt_ss AS a WHERE rclnt = '800' AND rldnr = '0L' AND rbukrs = '2450' AND gjahr = '2021' AND belnr = '0100000239' AND docln = '000003' INTO @ls_st. GET RUN TIME FIELD DATA(t6). SELECT SINGLE * FROM @lt_hs AS a WHERE rclnt = '800' AND rldnr = '0L' AND rbukrs = '2450' AND gjahr = '2021' AND belnr = '0100000239' AND docln = '000003' INTO @ls_st. GET RUN TIME FIELD DATA(t7). cl_demo_output=>write( |取数耗时 : { t2 - t1 }| ). cl_demo_output=>write( |赋值哈希表耗时: { t3 - t2 }| ). cl_demo_output=>write( |赋值排序表耗时: { t4 - t3 }| ). cl_demo_output=>write( |标准表取数耗时: { t5 - t4 }| ). cl_demo_output=>write( |排序表取数耗时: { t6 - t5 }| ). cl_demo_output=>write( |哈希表取数耗时: { t7 - t6 }| ).
ENDFORM.*&---------------------------------------------------------------------**& Form frm_compare_sum*&---------------------------------------------------------------------**& text*&---------------------------------------------------------------------**& --> p1 text*& <-- p2 text*&---------------------------------------------------------------------*FORM frm_compare_sum .
DATA: ls_st TYPE acdoca. "耗时考虑,哈希表的读取,及访问 GET RUN TIME FIELD...
