加盟优化

2020-08-25 03:18发布

         点击此处--->   EasySAP.com群内免费提供SAP练习系统(在群公告中)

加入QQ群:457200227(SAP S4 HANA技术交流) 群内免费提供SAP练习系统(在群公告中)


这是一个新话题,可以继续进行讨论

当前需要在两个巨大的事实表之间进行联接。 如果在HANA上即时执行连接,则性能会降低,HANA有时会崩溃。

因此,存储过程将数据分块,然后对较小的分块执行联接,然后将结果转储到物理表中。 分块效率低下,但数据插入量甚至更多。 大部分时间花费在增量合并(数据插入)中。

这是代码

基本上,有两个表Table_A和Table_B,并从中创建一个分析视图。

我们从Table_A的分析视图中选择2个时间戳之间的数据

在一个循环中,我们联接来自Table_A和Table_B的数据

并将结果转储到物理表中。

可以做什么来优化。

不幸的是,我似乎无法像

那样将while循环的结果保存在内存中

A =联合(while循环的结果)

创建过程CPM_SAP.SP_ZZFSL_CE11_REV_JOIN_V4 
(在MIN_TIMESTAMP NVARCHAR(14)中,在MAX_TIMESTAMP NVARCHAR(14)中,在MIN_PERIOD NVARCHAR(6)中,在MAX_PERIOD NVARCHAR(6)中)
MIN_PERIOD;
XYEAR INT:= 0;
XMONTH INT:= 0; BEGINVAR_Z_NAME_OBSCURED =选择" RYEAR"," RTCUR"," RUNIT"," POPER"," RBUKRS"," RACCT"," RCNTR" ," RPRCTR"," RZZSITE"," RZZPOSID"," RZZFKBER"," DOCTY"," REFDOCNR"," AUFNR"," ZZLDGRP"," VBUND"," ZZBUZEI"," AWORG"," CPUDT" "," CPUTM",
" ZZBUZEI_RPOSN"," PERIOD_ID"," TIMESTAMP",
" TSL" AS" TSL"," HSL" AS" HSL"," KSL" AS" KSL"," MSL " AS" MSL"
FROM" _SYS_BIC"。" wlclose/AN_Z_NAME_OBSCURED_REV_KSLNN"
其中" TIMESTAMP">:MIN_TIMESTAMP和" TIMESTAMP" <=:MAX_TIMESTAMP; WHILE:XPERIOD <=:MAX_PERIOD DO VAR1_Z_NAME = ," RTCUR"," RUNIT"," POPER"," RBUKRS"," RACCT"," RCNTR"," RPRCTR"," RZZSITE"," RZZPOSID"," RZZFKBER"," DOCTY"," REFDOCNR"," AUFNR"," ZZLDGRP"," VBUND"," ZZBUZEI"," AWORG", " CPUDT"," CPUTM",
" ZZBUZEI_RPOSN"," PERIOD_ID"," TIMESTAMP",
" TSL" AS" TSL"," HSL" AS" HSL"," KSL" AS" KSL", " MSL" AS" MSL"
FROM:VAR_Z_NAME_OBSCURED
其中" PERIOD_ID" = CAST(:XPERIOD AS VARCHAR(6)); VAR_CE11000 =选择" PALEDGER"," KSTAR"," PERIOD_ID"," BUKRS"," COPA_AWORG"," ZZBUZEI_RPOSN"," RBELN"," PERIO",
" GJAHR"," PERDE"," RPOSN", " PRCTR"," PPRCTR"," SKOST"," RKAUFNR"," KURSF"," VV005_ME"," VV006_ME"," WW004"," WW011"," WW012"," WW013",
" WW008" ," WW023"," WW010"," WW020"," WW015"," WW006"," WW024"," WW026"," VV005" AS" VV005"," VV006" AS" VV006"来自" _SYS_BIC "。" wlclose/AN_CE11000_REV_LDG01"
其中" PERIOD_ID" = CAST(:XPERIOD AS VARCHAR(6)); VAR_JOIN =选择Z.RACCT AS RACCT,Z.RBUKRS AS RBUKRS,Z。" PERIOD_ID" AS ZPERIOD,Z 。" AWORG"作为AWORG,Z。" ZZBUZEI",Z。" REFDOCNR"作为REFDOCNR,Z。" RCNTR"作为RCNTR,Z。" RPRCTR"作为RPRCTR,Z。" RZZSITE"作为RZZSITE,Z。 " RZZPOSID" AS RZZPOSID,Z。" RZZFKBER" AS RZZFKBER,Z。" DOCTY" AS DOCTY,Z。" AUFNR" AS AUFNR,Z。" ZZLDGRP" AS ZZLDGRP,Z。" VBUND" AS VBUND,< br> Z。" TIMESTAMP"作为时间戳,Z。" RYEAR"作为RYEAR,Z。" POPER"作为POPER,Z。" TSL"作为TSL,Z。" HSL"作为HSL,Z。" RTCUR"作为RTCUR, Z." KSL" AS KSL,Z。" MSL" AS MSL,Z。" RUNIT" AS RUNIT,C。 " PALEDGER" AS PALEDGER,C。" KSTAR" AS KSTAR,C。" BUKRS" AS BUKRS,C。" PERIOD_ID" AS CPERIOD,C。" COPA_AWORG" AS COPA_AWORG,C。" RBELN" AS RBELN,C 。" ZZBUZEI_RPOSN" AS RPOSN,C。" WW004" AS WW004,C。" WW011" as WW011,C." WW012" AS WW012," WW013" AS WW013,C。" WW008" AS WW008,C. " WW023" AS WW023,C." WW010" AS WW010,C." WW020" AS WW020,C." WW015" AS WW015,C." WW006" AS WW006,C." VV005" AS VV005,C 。" VV005_ME" AS VV005_ME,C。" VV006" AS VV006,C." VV006_ME" AS VV006_ME
从:VAR1_Z_NAME_OBSCURED Z
左外连接:VAR_CE11000 C在Z." ZZBUZEI_RPOSN" = C。" ZZBUZEI_RPOSN"和
Z." REFDOCNR" = C。" RBELN"和
Z." AWORG" = C。" COPA_AWORG"和
Z." RBUKRS" = C。" BUKRS " AND
Z." PERIOD_ID" = C。" PERIOD_ID" AND
Z.RACCT = C.KSTAR;
插入CPM_SAP.ZZFSL_CE11_REV_JOIN(选择*来自:VAR_JOIN); XYEAR:=(:XPERIOD/100);
XMONTH:=:XPERI OD-(:XYEAR * 100); IF:XMONTH = 12 XPERIOD时:=((:XYEAR + 1)* 100)+1;
ELSE
XPERIOD:=:XPERIOD +1 ;
END IF; END WHILE;
END;

         点击此处--->   EasySAP.com群内免费提供SAP练习系统(在群公告中)

加入QQ群:457200227(SAP S4 HANA技术交流) 群内免费提供SAP练习系统(在群公告中)


这是一个新话题,可以继续进行讨论

当前需要在两个巨大的事实表之间进行联接。 如果在HANA上即时执行连接,则性能会降低,HANA有时会崩溃。

因此,存储过程将数据分块,然后对较小的分块执行联接,然后将结果转储到物理表中。 分块效率低下,但数据插入量甚至更多。 大部分时间花费在增量合并(数据插入)中。

这是代码

基本上,有两个表Table_A和Table_B,并从中创建一个分析视图。

我们从Table_A的分析视图中选择2个时间戳之间的数据

在一个循环中,我们联接来自Table_A和Table_B的数据

并将结果转储到物理表中。

可以做什么来优化。

不幸的是,我似乎无法像

那样将while循环的结果保存在内存中

A =联合(while循环的结果)

创建过程CPM_SAP.SP_ZZFSL_CE11_REV_JOIN_V4 
(在MIN_TIMESTAMP NVARCHAR(14)中,在MAX_TIMESTAMP NVARCHAR(14)中,在MIN_PERIOD NVARCHAR(6)中,在MAX_PERIOD NVARCHAR(6)中)
MIN_PERIOD;
XYEAR INT:= 0;
XMONTH INT:= 0; BEGINVAR_Z_NAME_OBSCURED =选择" RYEAR"," RTCUR"," RUNIT"," POPER"," RBUKRS"," RACCT"," RCNTR" ," RPRCTR"," RZZSITE"," RZZPOSID"," RZZFKBER"," DOCTY"," REFDOCNR"," AUFNR"," ZZLDGRP"," VBUND"," ZZBUZEI"," AWORG"," CPUDT" "," CPUTM",
" ZZBUZEI_RPOSN"," PERIOD_ID"," TIMESTAMP",
" TSL" AS" TSL"," HSL" AS" HSL"," KSL" AS" KSL"," MSL " AS" MSL"
FROM" _SYS_BIC"。" wlclose/AN_Z_NAME_OBSCURED_REV_KSLNN"
其中" TIMESTAMP">:MIN_TIMESTAMP和" TIMESTAMP" <=:MAX_TIMESTAMP; WHILE:XPERIOD <=:MAX_PERIOD DO VAR1_Z_NAME = ," RTCUR"," RUNIT"," POPER"," RBUKRS"," RACCT"," RCNTR"," RPRCTR"," RZZSITE"," RZZPOSID"," RZZFKBER"," DOCTY"," REFDOCNR"," AUFNR"," ZZLDGRP"," VBUND"," ZZBUZEI"," AWORG", " CPUDT"," CPUTM",
" ZZBUZEI_RPOSN"," PERIOD_ID"," TIMESTAMP",
" TSL" AS" TSL"," HSL" AS" HSL"," KSL" AS" KSL", " MSL" AS" MSL"
FROM:VAR_Z_NAME_OBSCURED
其中" PERIOD_ID" = CAST(:XPERIOD AS VARCHAR(6)); VAR_CE11000 =选择" PALEDGER"," KSTAR"," PERIOD_ID"," BUKRS"," COPA_AWORG"," ZZBUZEI_RPOSN"," RBELN"," PERIO",
" GJAHR"," PERDE"," RPOSN", " PRCTR"," PPRCTR"," SKOST"," RKAUFNR"," KURSF"," VV005_ME"," VV006_ME"," WW004"," WW011"," WW012"," WW013",
" WW008" ," WW023"," WW010"," WW020"," WW015"," WW006"," WW024"," WW026"," VV005" AS" VV005"," VV006" AS" VV006"来自" _SYS_BIC "。" wlclose/AN_CE11000_REV_LDG01"
其中" PERIOD_ID" = CAST(:XPERIOD AS VARCHAR(6)); VAR_JOIN =选择Z.RACCT AS RACCT,Z.RBUKRS AS RBUKRS,Z。" PERIOD_ID" AS ZPERIOD,Z 。" AWORG"作为AWORG,Z。" ZZBUZEI",Z。" REFDOCNR"作为REFDOCNR,Z。" RCNTR"作为RCNTR,Z。" RPRCTR"作为RPRCTR,Z。" RZZSITE"作为RZZSITE,Z。 " RZZPOSID" AS RZZPOSID,Z。" RZZFKBER" AS RZZFKBER,Z。" DOCTY" AS DOCTY,Z。" AUFNR" AS AUFNR,Z。" ZZLDGRP" AS ZZLDGRP,Z。" VBUND" AS VBUND,< br> Z。" TIMESTAMP"作为时间戳,Z。" RYEAR"作为RYEAR,Z。" POPER"作为POPER,Z。" TSL"作为TSL,Z。" HSL"作为HSL,Z。" RTCUR"作为RTCUR, Z." KSL" AS KSL,Z。" MSL" AS MSL,Z。" RUNIT" AS RUNIT,C。 " PALEDGER" AS PALEDGER,C。" KSTAR" AS KSTAR,C。" BUKRS" AS BUKRS,C。" PERIOD_ID" AS CPERIOD,C。" COPA_AWORG" AS COPA_AWORG,C。" RBELN" AS RBELN,C 。" ZZBUZEI_RPOSN" AS RPOSN,C。" WW004" AS WW004,C。" WW011" as WW011,C." WW012" AS WW012," WW013" AS WW013,C。" WW008" AS WW008,C. " WW023" AS WW023,C." WW010" AS WW010,C." WW020" AS WW020,C." WW015" AS WW015,C." WW006" AS WW006,C." VV005" AS VV005,C 。" VV005_ME" AS VV005_ME,C。" VV006" AS VV006,C." VV006_ME" AS VV006_ME
从:VAR1_Z_NAME_OBSCURED Z
左外连接:VAR_CE11000 C在Z." ZZBUZEI_RPOSN" = C。" ZZBUZEI_RPOSN"和
Z." REFDOCNR" = C。" RBELN"和
Z." AWORG" = C。" COPA_AWORG"和
Z." RBUKRS" = C。" BUKRS " AND
Z." PERIOD_ID" = C。" PERIOD_ID" AND
Z.RACCT = C.KSTAR;
插入CPM_SAP.ZZFSL_CE11_REV_JOIN(选择*来自:VAR_JOIN); XYEAR:=(:XPERIOD/100);
XMONTH:=:XPERI OD-(:XYEAR * 100); IF:XMONTH = 12 XPERIOD时:=((:XYEAR + 1)* 100)+1;
ELSE
XPERIOD:=:XPERIOD +1 ;
END IF; END WHILE;
END;
付费偷看设置
发送
8条回答
SAP砖家
1楼 · 2020-08-25 03:44.采纳回答

我很抱歉,因为我不在乎翻阅代码并弄清楚到底发生了什么。 因此,完全承认我的无知,我不得不问-

如果您想合并两个事实表,为什么不在现场执行"具有恒定值的联合"(UCV)- 飞? 如果您不熟悉此方法,则可以在Werner上将其记录在案(幻灯片15): http://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/6056911a-07cc-2e10-7a8a-ffa9b8cf579c?QuickLink = index&overridelayout = true&52415780970082

如果两个事实表的共享尺寸都不完全相同,有时您仍可以构建具有所需尺寸的中间表 ,将其加入事实表-然后执行UCV。

hengyuye
2楼-- · 2020-08-25 03:38

感谢大家在我的这个查询中所做的努力和分析。 我没时间,但是现在将测试这里提出的想法,并告诉大家。

一周热门 更多>