ABAP基础知识 用SQL处理内表

前言

ABAP升级新语法,很多新语法带来了更简洁的代码及更容易理解的代码形式.

下图是截至S4 1909版本的ABAP语法升级信息(该截图来自ABAP中的帮助)

35d4493434b0cffe07d64187931ab129.png

在项目中也越来越多的使用新语法来处理逻辑, 内表是ABAP中最关注处理性能的部分,新语法允许把内表引入到SQL语句中执行.因此带来了使用SQL语句处理内表的新方式

本文通过示例比较内表常用的几种处理的新旧语法的性能比较

示例程序的数据样本

来自ACDOCA表,该系统中记录数30,129

81fee488ad6ebd6afd1b72a0d1eafdfc.png

SQL处理内表

用@标记引入的内表且必须给出别名,into子句要放到最后

@内表 as a

获取一条记录

2173eff10f21cc8e7b5e08061b3dbc0f.png

分组小计

61d46f938edee269da45de842c8e0630.png

内表关联物理表(推荐使用,取代for all entries in )

d509927a6370b75e1d61999a8384d859.png

通过关键字访问内表

f4760da49237c8242bb003c9f0395dae.png

新语法可以把内表当作一个表引入到select 语句中, 但限制了每次只能引入一个内表

因此可以通过SELECT 语句读取内表的单条记录

e2199bd5ae051cc01f9849b0f169db2a.png

但是通过示例程序对二分法,哈希法及SQL法的比较

对标准内表的SELECT SINGLE 访问性能很差.耗时远远高于哈希,二分法读取

07bec6b5fa0ebdf9bab128bdafb33250.png

通过关键字访问不同类型的内表

基于上一个结论联想到内表的三种不同类型(标准,排序,哈希)在正常read读取的差异.这个差异应该也会存在于SELECT SINGLE 读取内表. 因此比较了三种不同内表的SELECT SINGLE差异.

从下图中可以看出SELECT SINGLE 读取不同类型的内表,耗时是不同的. 读取哈希内表耗时最低, 但耗时也比 read table 读取哈希表多很多( 117 VS 12 )

51a0916238e9d358437bc84013d78cf2.png

内表小计的比较

之前写过文章介绍了几种内表小计的方式

详见链接

无峰,公众号:ABAP 技巧与实战ABAP基础知识 内表汇总数据的方式

为了和select 语法比较性能.示例程序中给出了几种常用方法

  • COLLECT语句

  • 循环用哈希表统计

  • 用LOOP GROUP统计

  • 用SELECT统计

比较结果出乎意料: SELECT 统计内表(标准内表)耗时低于其它方式. 看来使用SQL语句统计内表可以作为首选方式了.

还有个更吃惊的结论: 直接从数据库统计性能远高于从内表统计( 9478 VS 35754 ),这还不包括从数据库读取数据到内表的耗时. 

看来对于统计类的数据处理逻辑下沉到数据库层级是非常有必要的

f65af0c795172e37a1a90fee42bb0d85.png

程序源代码

*&---------------------------------------------------------------------*
*& 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 DATA(t1).
  SELECT * FROM acdoca INTO TABLE lt_st ORDER BY rldnr rbukrs gjahr belnr docln.
  GET RUN TIME FIELD DATA(t2).


  PERFORM sum_collect.


  GET RUN TIME FIELD DATA(t3).
  PERFORM sum_hash.


  GET RUN TIME FIELD DATA(t4).
  PERFORM sum_select.


  GET RUN TIME FIELD DATA(t5).


  PERFORM sum_loop_group.
  GET RUN TIME FIELD DATA(t6).


  PERFORM sum_select_table.
  GET RUN TIME FIELD DATA(t7).


  cl_demo_output=>write( |取数耗时       :  { t2 - t1 }| ).
  cl_demo_output=>write( |COLLECT统计耗时:  { t3 - t2 }| ).
  cl_demo_output=>write( |哈希表统计耗时 :   { t4 - t3 }| ).
  cl_demo_output=>write( |SELECT统计耗时 :   { t5 - t4 }| ).
  cl_demo_output=>write( |LOOP GROUP耗时 :   { t6 - t5 }| ).
  cl_demo_output=>write( |数据库统计耗时 :   { t7 - t6 }| ).


ENDFORM.
*&---------------------------------------------------------------------*
*& Form sum_collect
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM sum_collect .
  DATA: BEGIN OF ls_sum,
          racct TYPE acdoca-racct,
          hsl   TYPE acdoca-hsl,
        END OF ls_sum.
  DATA: lt_sum LIKE TABLE OF ls_sum.
  DATA: lt_sum_hs LIKE HASHED TABLE OF ls_sum WITH UNIQUE KEY racct.


  LOOP AT lt_st INTO DATA(ls_st).
    MOVE-CORRESPONDING ls_st TO ls_sum.
    COLLECT ls_sum INTO lt_sum.
  ENDLOOP.
ENDFORM.


FORM sum_hash .
  DATA: BEGIN OF ls_sum,
          racct TYPE acdoca-racct,
          hsl   TYPE acdoca-hsl,
        END OF ls_sum.
  DATA: lt_sum LIKE TABLE OF ls_sum.
  DATA: lt_sum_hs LIKE HASHED TABLE OF ls_sum WITH UNIQUE KEY racct.


  LOOP AT lt_st INTO DATA(ls_st).
    READ TABLE lt_sum_hs ASSIGNING FIELD-SYMBOL(<ls_sum>) WITH TABLE KEY racct = ls_st-racct.
    IF sy-subrc <> 0.
      CLEAR ls_sum.
      ls_sum-racct = ls_st-racct.
      INSERT ls_sum INTO TABLE lt_sum_hs ASSIGNING <ls_sum>.
    ENDIF.
    <ls_sum>-hsl = <ls_sum>-hsl + ls_st-hsl.
  ENDLOOP.
ENDFORM.


FORM sum_select .
  DATA: BEGIN OF ls_sum,
          racct TYPE acdoca-racct,
          hsl   TYPE acdoca-hsl,
        END OF ls_sum.
  DATA: lt_sum LIKE TABLE OF ls_sum.
  DATA: lt_sum_hs LIKE HASHED TABLE OF ls_sum WITH UNIQUE KEY racct.


  SELECT a~racct,SUM( a~hsl ) AS hsl
    FROM @lt_st AS a
    GROUP BY a~racct
    INTO CORRESPONDING FIELDS OF TABLE @lt_sum.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form sum_loop_group
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM sum_loop_group .
  DATA: BEGIN OF ls_sum,
          racct TYPE acdoca-racct,
          hsl   TYPE acdoca-hsl,
        END OF ls_sum.
  DATA: lt_sum LIKE TABLE OF ls_sum.
  DATA: lt_sum_hs LIKE HASHED TABLE OF ls_sum WITH UNIQUE KEY racct.


  LOOP AT lt_st INTO DATA(ls_gp) GROUP BY ( racct = ls_gp-racct ).
    CLEAR ls_sum.
    ls_sum-racct = ls_gp-racct.
    LOOP AT GROUP ls_gp INTO DATA(ls_st).
      ls_sum-hsl = ls_sum-hsl + ls_st-hsl.
    ENDLOOP.
    APPEND ls_sum TO lt_sum.
  ENDLOOP.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form sum_SELECT_TABLE
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM sum_select_table .
  DATA: BEGIN OF ls_sum,
          racct TYPE acdoca-racct,
          hsl   TYPE acdoca-hsl,
        END OF ls_sum.
  DATA: lt_sum LIKE TABLE OF ls_sum.
  DATA: lt_sum_hs LIKE HASHED TABLE OF ls_sum WITH UNIQUE KEY racct.


  SELECT a~racct,SUM( a~hsl ) AS hsl
    FROM acdoca AS a
    GROUP BY a~racct
    INTO CORRESPONDING FIELDS OF TABLE @lt_sum.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_inner_join
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_inner_join .
  data: lt_bkpf like table of bkpf.
  select distinct b~* from @lt_st as a inner join bkpf as b on b~belnr = a~belnr and b~gjahr = a~gjahr and b~bukrs = a~rbukrs
    into table @lt_bkpf.
ENDFORM.

总结

本文只是简单比较了一下内表的常规语句与SELECT 语句操作内表的性能差异,这次比较的结论会作用于后续项目中的程序性能优化

单条读取内表优选方式:

  • READ 哈希 

  • READ 排序 或 READ 标准 binary search

当然 SELECT SINGLE 读取内表可以使用WHERE 条件添加一些特殊条件. 这个远比read 语句可以使用条件更丰富. 在一些性能要求不高的程序中, 可以用它来实现一些特殊逻辑

尽量从数据库表中获取聚合的数据(汇总),如果一定要在内表基础上小计或总计, 直接使用SQL语句处理内表也是一个优选.

  • 11
    点赞
  • 12
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
ABAP是一种编程语言,用于SAP系统中的应用程序开发。在ABAP中,动态内表是一种特殊类型的内表,它允许在运行时动态地添加、删除和修改表格的行和列。动态内表是通过使用ABAP内部表类型"ANY TABLE"来实现的。 要处理动态内表数据,您可以按照以下步骤进行操作: 1. 定义动态内表:使用内部表类型"ANY TABLE"来定义动态内表。例如: DATA: dynamic_table TYPE STANDARD TABLE OF ANY. 2. 创建动态行结构:定义一个结构体,用于描述动态内表中的行。这个结构体可以包含任意数量和类型的字段。例如: DATA: dynamic_row TYPE REF TO data, dynamic_row_structure TYPE STANDARD TABLE OF string. 3. 添加行到动态内表:使用CREATE DATA语句来创建动态行,并将其添加到动态内表中。例如: CREATE DATA dynamic_row TYPE HANDLE dynamic_row_structure. ASSIGN dynamic_row->* TO <dynamic_row>. APPEND <dynamic_row> TO dynamic_table. 4. 访问动态内表数据:使用ASSIGN语句将动态行的字段赋值给变量,并对其进行操作。例如: FIELD-SYMBOLS: <field> TYPE any. ASSIGN COMPONENT 'FIELD_NAME' OF STRUCTURE <dynamic_row> TO <field>. IF sy-subrc = 0. <field> = 'VALUE'. ENDIF. 5. 删除动态内表行:使用DELETE语句从动态内表中删除行。例如: DELETE dynamic_table INDEX i. 6. 修改动态内表行:使用MODIFY语句修改动态内表中的行。例如: MODIFY dynamic_table INDEX i FROM <dynamic_row>. 以上是处理ABAP动态内表数据的基本步骤。根据您具体的需求,您可以进一步扩展和优化这些步骤。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值