1
需求介绍
需求说明：查询acdoca，需要使用acdoca.rbukrs = znefit_cfl02.bukrs，判断当前公司在znefit_cfl02中是否维护。
如果没有维护，就获取acdoca.racct；
如果维护了，就获取acdoca.lokkt；
经过判断获取计算后的值作为acdoca的科目号zracct，并且这个科目号要和znefit_cfl01.racct匹配，确保科目在znefit_cfl01中。
测试数据：本次测试数据中acdoca一共有4亿7千万条数据，自身条件过滤后有1亿8千万条，与znefit_cfl01过滤后有2万6千条（最终结果）。
文章说明：因为要用判断后的racct结果关联znefit_cfl01表进行查询，因此出现以下几种不同的写法，
文章将一步步优化，展示优化的整个过程。
2
实现方法
2.1
分段获取
首先获取znefit_cfl02表中去重后的公司代码，得到公司代码的结果lt_bukrs
然后查询acdoca和lt_bukrs的结果集，并计算出科目racct，得到结果lt_acdoca
查询lt_acdoca和znefit_cfl01表，得到最终结果
最终耗时372044986微秒（6.2分钟）（后续都是相同的测试数据）
"--------------------@斌将军--------------------CLASS-METHODS get_cfl_data "版本1 AMDP OPTIONS CDS SESSION CLIENT current IMPORTING VALUE(iv_mandt) TYPE mandt VALUE(iv_bukrs) TYPE bukrs VALUE(iv_budat) TYPE budat EXPORTING VALUE(et_data) TYPE tt_cfl01. METHOD get_cfl_data BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING acdoca znefit_cfl01 znefit_cfl02 t001 ekko kna1. -- 获取公司代码列表lt_bukrs = SELECT bukrs FROM znefit_cfl02 WHERE bukrs <> '' and mandt = iv_mandt GROUP BY bukrs; lt_acdoca = select acdoca.rldnr, --总账会计中的分类账 acdoca.rbukrs as bukrs, --公司代码 acdoca.gjahr, --会计年度 acdoca.belnr, --会计凭证的凭证编号 acdoca.docln, --分类账 6 字符过账项目 right(acdoca.docln,3) as zbuzei, --过账项目 case when b.bukrs = '' or b.bukrs is null then acdoca.racct --总账科目 else acdoca.lokkt --备选科目 end as zracct, acdoca.koart, --科目类型 acdoca.xopvw, --未清项管理 acdoca.kunnr, --客户 kna1.name1 as zkhmc, case when acdoca.lifnr = '' and acdoca.ebeln <> '' then ekko.lifnr else acdoca.lifnr end as lifnr, --供应商 acdoca.ebeln, --采购订单号 acdoca.ebelp, --采购订单行项目 acdoca.budat, --过账日期 acdoca.augdt, --清账日期 acdoca.rwcur, --交易货币 acdoca.wsl, --交易货币金额 acdoca.rhcur, --本位币 acdoca.hsl, --本位币金额 acdoca.blart, -- acdoca.poper, -- t001.butxt --公司名称 from acdoca left outer join :lt_bukrs as b on acdoca.rbukrs = b.bukrs left outer join t001 on acdoca.rbukrs = t001.bukrs and t001.mandt = iv_mandt left outer join ekko on acdoca.ebeln = ekko.ebeln and ekko.mandt = iv_mandt left outer join kna1 on acdoca.kunnr = kna1.kunnr and kna1.mandt = iv_mandt where acdoca.rldnr = '0L' and acdoca.rbukrs = iv_bukrs and acdoca.budat <= iv_budat and( acdoca.augdt = '00000000' or acdoca.augdt is null or acdoca.augdt > iv_budat ) and acdoca.blart <> '' and acdoca.hsl <> 0 and acdoca.RCLNT = iv_mandt; et_data = select a.*, znefit_cfl01.zfw, --范围 znefit_cfl01.zfwms, --范围 znefit_cfl01.zzfw, --子范围 znefit_cfl01.txt20, -- a.bukrs as zbukrs from :lt_acdoca as a inner join znefit_cfl01 on a.zracct = znefit_cfl01.racct and znefit_cfl01.mandt = iv_mandt and znefit_cfl01.zfw not in ( 'E', 'F' ); --如果不需要从关联表中获取字段，只判断是否存在，可以用 -- where EXISTS ( SELECT 1 FROM lt_clf01 AS f WHERE a.calc_racct = f.racct );ENDMETHOD."--------------------@斌将军--------------------推测查询缓慢原因：
获取lt_acdoca时，acdoca表未经过滤全部关联lt_bukrs、t001、kna1、ekko表，造成浪费。
优化措施：
先查询acdoca并做排除
将lt_bukrs、t001、kna1、ekko放在排除后再关联
优化后的结果是315499123微秒（5.2分钟）
"--------------------@斌将军--------------------METHOD get_cfl_data4 BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING acdoca znefit_cfl01 znefit_cfl02 t001 ekko kna1. -- 创建临时表来存储中间结果 -- 获取公司代码列表 lt_bukrs = SELECT bukrs FROM znefit_cfl02 WHERE bukrs <> '' and mandt = iv_mandt GROUP BY bukrs; lt_acdoca_all = SELECT RCLNT, rldnr, rbukrs, gjahr, belnr, docln, racct, lokkt, koart, xopvw, kunnr, lifnr, ebeln, ebelp, budat, augdt, rwcur, wsl, rhcur, hsl, blart, poper FROM acdoca WHERE rldnr = '0L' AND rbukrs = iv_bukrs -- 参数绑定 AND budat <= iv_budat -- 参数绑定 AND (augdt = '00000000' OR augdt IS NULL OR augdt > iv_budat) -- 参数绑定 AND blart <> '' AND hsl <> 0 and acdoca.RCLNT = iv_mandt; lt_acdoca = SELECT a.*, CASE WHEN b.bukrs IS NOT NULL THEN a.lokkt ELSE a.racct END AS zracct from :lt_acdoca_all as a left outer join :lt_bukrs as b on a.rbukrs = b.bukrs; et_data = SELECT a.rldnr, --总账会计中的分类账 a.rbukrs as bukrs, --公司代码 a.gjahr, --会计年度 a.belnr, --会计凭证的凭证编号 a.docln, --分类账 6 字符过账项目 right(a.docln,3) as zbuzei, --过账项目 a.zracct as racct, a.koart, --科目类型 a.xopvw, --未清项管理 a.kunnr, --客户 kna1.name1 as zkhmc, case when a.lifnr = '' and a.ebeln <> '' then ekko.lifnr else a.lifnr end as lifnr, --供应商 a.ebeln, --采购订单号 a.ebelp, --采购订单行项目 a.budat, --过账日期 a.augdt, --清账日期 a.rwcur, --交易货币 a.wsl, --交易货币金额 a.rhcur, --本位币 a.hsl, --本位币金额 a.blart, -- a.poper, -- t001.butxt, --公司名称 znefit_cfl01.zfw, --范围 znefit_cfl01.zfwms, --范围 znefit_cfl01.zzfw, --子范围 znefit_cfl01.txt20, -- a.rbukrs as zbukrs from :lt_acdoca as a inner join znefit_cfl01 on a.zracct = znefit_cfl01.racct and znefit_cfl01.mandt = iv_mandt and znefit_cfl01.zfw not in ( 'E', 'F' ) left outer join t001 on a.rbukrs = t001.bukrs and t001.mandt = iv_mandt left outer join ekko on a.ebeln = ekko.ebeln and ekko.mandt = iv_mandt left outer join kna1 on a.kunnr = kna1.kunnr and kna1.mandt = iv_mandt; ENDMETHOD."--------------------@斌将军--------------------分析数据集，acdoca排除之后，会在lt_acdoca_all中获取1亿8千万条数据，通过与lt_bukrs关联，计算科目racct。
如果在和lt_bukrs关联时，加入DISTINCT
"--------------------@斌将军--------------------lt_acdoca = SELECT DISTINCT ...... from :lt_acdoca_all as a left outer join :lt_bukrs as b on a.rbukrs = b.bukrs;"--------------------@斌将军--------------------整个查询过程，耗时减少到11879548微秒（11秒）
推测原因：虽然结果不会出现重复（lt_acdoca_all中的bukrs会有重复的，但是lt_bukrs的bukrs是唯一的），但使用DISTINCT查询耗时11秒，不使用DISTINCT查询耗时5分钟。
这是因为结果虽然不会重复，但是HANA优化器会根据此处的DISTINCT，进行预先的去重，减小连接过程中出现的巨大中间结果集，减少内存开销，大大提高查询效率。
进一步优化：如果对后续的查询也使用DISTINCT是不是效率更高？
①比如在最终查询时加上DISTINCT
et_data = SELECT DISTINCT②或者先与znefit_cfl01关联，结果lt_base再与t001等表关联，在关联znefit_cfl01时加上DISTINCT，如下：
"--------------------@斌将军---------------------- 第三步：最终关联配置表 lt_base = SELECT DISTINCT ...FROM :lt_acdoca AS a inner join znefit_cfl01 as f on a.racct = f.racct and f.mandt = iv_mandt and f.zfw not in ( 'E', 'F' ); et_data = select ... from :lt_base as a LEFT JOIN t001 AS t ON a.rbukrs = t.bukrs and t.mandt = iv_mandt LEFT JOIN ekko AS e ON a.ebeln = e.ebeln and e.mandt = iv_mandt LEFT JOIN kna1 AS k ON a.kunnr = k.kunnr and k.mandt = iv_mandt;"--------------------@斌将军--------------------①②两种优化最终结果还是11秒左右，并没有变化，为什么多加一个DISTINCT没有效果？
推测原因：HANA优化器在执行时，识别第一次已经将数据去重，而第二次的去重操作，将不会改变数据结果，因此优化器识别冗余操作后，将消除冗余的 DISTINCT，将其合并为一个更优的去重操作，因此，第二次的DISTINCT并没有实现第一次的优化操作。
2.2
使用WITH AS （CTE）
步骤与上述一致，只是使用了WITH AS表达式
最终耗时需12941073微秒（约12秒）并没有什么效果
WITH AS 表达式（即通用表表达式 CTE - Common Table Expressions），其主要作用在于提高代码的可读性、可维护性和结构化程度，并允许使用递归查询。对查询效率方面并无较大作用。
"--------------------@斌将军--------------------METHOD get_cfl_data2 BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING acdoca znefit_cfl01 znefit_cfl02 t001 ekko kna1. et_data = WITH lt_bukrs AS ( SELECT DISTINCT bukrs FROM znefit_cfl02 WHERE bukrs <> '' and mandt = iv_mandt GROUP BY bukrs ), lt_acdoca_all AS ( -- 第一步：快速过滤ACDOCA SELECT RCLNT, rldnr, rbukrs, gjahr, belnr, docln, racct, lokkt, koart, xopvw, kunnr, lifnr, ebeln, ebelp, budat, augdt, rwcur, wsl, rhcur, hsl, blart, poper FROM acdoca WHERE rldnr = '0L' AND rbukrs = iv_bukrs -- 参数绑定 AND budat <= iv_budat -- 参数绑定 AND (augdt = '00000000' OR augdt IS NULL OR augdt > iv_budat) -- 参数绑定 AND blart <> '' AND hsl <> 0 and acdoca.RCLNT = iv_mandt ), lt_acdoca AS ( -- 第二步：关联基础信息 SELECT DISTINCT a.*, CASE WHEN c.bukrs IS NOT NULL THEN a.lokkt ELSE a.racct END AS zracct FROM lt_acdoca_all AS a LEFT JOIN lt_bukrs AS c ON a.rbukrs = c.bukrs ) select ...... from lt_acdoca as a inner join znefit_cfl01 on a.zracct = znefit_cfl01.racct and znefit_cfl01.mandt = iv_mandt and znefit_cfl01.zfw not in ( 'E', 'F' ) left outer join t001 on a.rbukrs = t001.bukrs and t001.mandt = iv_mandt left outer join ekko on a.ebeln = ekko.ebeln and ekko.mandt = iv_mandt left outer join kna1 on a.kunnr = kna1.kunnr and kna1.mandt =iv_mandt;ENDMETHOD."--------------------@斌将军--------------------2.3
计算结果关联查询
以上两种方法是分步查询，而本方法是直接用计算结果进行关联 znefit_cfl01 on ( case when b.bukrs is null then acdoca.racct else acdoca.lokkt end ) = znefit_cfl01.racct
耗时13058358 微秒（13秒）相对还慢一点
推测原因：可能由于关联条件的计算，加剧了逻辑复杂度，没有起到优化作用
"--------------------@斌将军--------------------METHOD get_cfl_data5 BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING acdoca znefit_cfl01 znefit_cfl02 t001 ekko kna1. -- 获取公司代码列表 lt_bukrs = SELECT bukrs FROM znefit_cfl02 WHERE bukrs <> '' and mandt = iv_mandt GROUP BY bukrs; lt_acdoca_all = SELECT RCLNT, rldnr, rbukrs, gjahr, belnr, docln, racct, lokkt, koart, xopvw, kunnr, lifnr, ebeln, ebelp, budat, augdt, rwcur, wsl, rhcur, hsl, blart, poper FROM acdoca WHERE rldnr = '0L' AND rbukrs = iv_bukrs -- 参数绑定 AND budat <= iv_budat -- 参数绑定 AND (augdt = '00000000' OR augdt IS NULL OR augdt > iv_budat) -- 参数绑定 AND blart <> '' AND hsl <> 0 and acdoca.RCLNT = iv_mandt; lt_clf01 = select * from znefit_cfl01 where mandt = iv_mandt and znefit_cfl01.zfw not in ( 'E','F' ); et_data = select disTINCT ... from :lt_acdoca_all as a left outer join :lt_bukrs as b on a.rbukrs = b.bukrs inner join :lt_clf01 as znefit_cfl01 on ( case when b.bukrs is null then a.racct else a.lokkt end ) = znefit_cfl01.racct left outer join t001 on a.rbukrs = t001.bukrs and t001.mandt = iv_mandt left outer join ekko on a.ebeln = ekko.ebeln and ekko.mandt = iv_mandt left outer join kna1 on a.kunnr = kna1.kunnr and kna1.mandt = iv_mandt; ENDMETHOD."--------------------@斌将军--------------------2.4
UNION ALL
以上3种方法，都需要通过计算后的zracct连接znefit_cfl01进行过滤，造成需要设计成分步获取结果进行关联，或在关联条件中增加判断的方式。
而当acdoca.rbukrs不在znefit_cfl02中时，其实获取的就是acdoca.racct，即acdoca.racct = 计算后的zracct，反之则acdoca.lokkt = 计算后的zracct。
测试，如果直接用acdoca.racct = znefit_cfl01.racct
"--------------------@斌将军--------------------et_data = SELECT ... from :lt_acdoca as a inner join znefit_cfl01 on a.racct = znefit_cfl01.racct"--------------------@斌将军--------------------执行查询只需要646501微秒（0.6秒）
对于lt_acdoca来说，racct和zracct都是表中的字段，而且内容完全一致，为什么不同的字段连接，会导致20倍的效率差异？
推测原因：
acdoca.racct是一个原始、稳定的列。HANA优化器执行高效的哈希连接或索引扫描，从而实现快速连接。
acdoca.zracct是一个表达式列，缺少相应信息，HANA优化器不得不选择效率低的连接策略，导致性能极具下降。
所以据此可以改变一下思路，将连接分为两种情况，并用union all连接两部分内容：
第一部分：获取acdoca.lokkt = znefit_cfl01.racct
union all
第二部分：获取acdoca.racct = znefit_cfl01.racct
耗时514004微秒（0.5秒）
"--------------------@斌将军--------------------METHOD get_cfl_data3 BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING acdoca znefit_cfl01 znefit_cfl02 t001 ekko kna1. lt_bukrs = SELECT bukrs FROM znefit_cfl02 WHERE bukrs <> '' and mandt = iv_mandt GROUP BY bukrs; et_data = select ...... acdoca.racct, --总账科目 ...... b.bukrs as zbukrs-- from acdoca inner join znefit_cfl01 on acdoca.racct = znefit_cfl01.racct and znefit_cfl01.zfw not in ( 'E', 'F' ) and znefit_cfl01.mandt = iv_mandt left outer join :lt_bukrs as b on acdoca.rbukrs = b.bukrs left outer join t001 on acdoca.rbukrs = t001.bukrs and t001.mandt = iv_mandt left outer join ekko on acdoca.ebeln = ekko.ebeln and ekko.mandt = iv_mandt left outer join kna1 on acdoca.kunnr = kna1.kunnr and kna1.mandt = iv_mandt where acdoca.rldnr = '0L' and acdoca.rbukrs = iv_bukrs and acdoca.budat <= iv_budat and( acdoca.augdt = '00000000' or acdoca.augdt is null or acdoca.augdt > iv_budat ) and acdoca.blart <> '' and acdoca.hsl <> 0 and acdoca.RCLNT = iv_mandt and b.bukrs is null union all select ...... acdoca.lokkt as racct, ...... b.bukrs as zbukrs -- from acdoca inner join znefit_cfl01 on acdoca.lokkt = znefit_cfl01.racct and znefit_cfl01.zfw not in ( 'E', 'F' ) and znefit_cfl01.mandt = iv_mandt inner join :lt_bukrs as b on acdoca.rbukrs = b.bukrs left outer join t001 on acdoca.rbukrs = t001.bukrs and t001.mandt = iv_mandt left outer join ekko on acdoca.ebeln = ekko.ebeln and ekko.mandt = iv_mandt left outer join kna1 on acdoca.kunnr = kna1.kunnr and kna1.mandt = iv_mandt where acdoca.rldnr = '0L' and acdoca.rbukrs = iv_bukrs and acdoca.budat <= iv_budat and( acdoca.augdt = '00000000' or acdoca.augdt is null or acdoca.augdt > iv_budat ) and acdoca.blart <> '' and acdoca.hsl <> 0 and acdoca.RCLNT = iv_mandt; ENDMETHOD."--------------------@斌将军--------------------综上，当面临需要使用计算之后的结果进行关联查询时，最快的方法是通过分情况查询再合并的方式，充分利用HANA优化器的优化作用，高效率进行数据关联查询。
本人能力有限，读者对上述AMDP实现过程有更好方式，望不吝赐教。
—— 希望本篇文章对您有所帮助 ——
