优化Hana程序,目前需要2.5个小时才能执行

2020-09-06 18:36发布

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

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


这是我插入STOCK摘要表中的过程。
每天最多可输出2.5亿条记录。
是否有必要优化以下过程或以更好的方式编写它?

创建过程PROC_CSR_CURR_STOCK
 语言SQLSCRIPT
 SQL安全调用程序
 预设SCHEMA HADMIN
 如
 开始
 -------------------代码开始-------------------------------------  ---------------

 DECLARE X INT:= 0;
 DECLARE计数INT:= 0;
 DECLARE v_cnt INT;
 DECLARE v_loop INT;

 t_rst =
 选择
 a.SUPPLYING_VENDOR,
 a.SUPPLYING_DC,
 网站,
 LTRIM(a.ARTICLE,'0')文章,
 d.lgort SLOC,
 B.MAABC ABC_CLASSIFICATION_MARC,
 B.MMSTA SITE_ARTICLE_STATUS,
 B.DISMM RP_TYPE,
 B.CONS_PROCG PROCUREMENT_TYPE,
 B.NON_MDSE_ID DELIVERY_TYPE,
 B.ZRED_DOT BLACK_DOT_FLAG,
 d.LABST SALEABLE_STK_QTY,
 0 DISPLAY_STK_QTY,
 0 OTHER_SLOC_STK_QTY,
 d.LABST UNRESTRICTED_STK_QTY,
 d.INSME QUALITY_STK_QTY,
 d.SPEME BLOCKED_STK_QTY,
 B.UMLMC TRANSFER_STK_QTY,
 B.TRAME TRANSIT_STK_QTY,
 (COALESCE(d.LABST,0)+ COALESCE(B.TRAME,0)+ COALESCE(B.UMLMC,0)+ COALESCE(d.INSME,0)+ COALESCE(d.SPEME,0))TOTAL_STK_QTY,
 (((COALESCE(d.LABST,0)+ COALESCE(B.TRAME,0)+ COALESCE(B.UMLMC,0)+ COALESCE(d.INSME,0)+ COALESCE(d.SPEME,0))* C.  VERPR),共TOTAL_STK_VALUE,
 C.VERPR MOVING_AVG_PRICE,
 B.MINBE REORDER_POINT,
 B.EISBE SAFETY_STOCK,
 (当B.MABST <= 0则TARGET_STOCK ELSE B.MABST结束时的情况)MBQ,
 (当B.MABST <= 0且TARGET_STOCK <= 0则0 ELSE 1 END时)MBQ_FLAG,
 (C.LBKUM <0 THEN 1的情况,C.LBKUM = 0 THEN 2的情况,C.LBKUM> 0 THEN 3 ELSE 2 END的情况)OOS_QTY,
 (当C.SALK3 <0 THEN 1时,C.SALK3 = 0 THEN 2时,C.SALK3> 0 THEN 3 ELSE 2 END时)OOS_VALUE,
 1条ARTICLE_COUNT,
 CURRENT_DATERECORD_DATE,
 0 CONSIGN_STOCK,
 B.SOBSL SP_PROC_TYPE,
 B.BWSCL SOURCE_OF_SUPP,
 d.LABST * C.VERPR SLOC_VALUE,
 a.NIA_FLAG,
 a.PROMOTION_NO,
 a.NIP_PROMO,
 a.LAST_SALE_DATE,
 a.LAST_SALE_BUCKET,
 SBU,
 段
 一个家族,
 一类,
 砖头
 a.MATL_GROUP,
 a.MATL_TYPE,
 邦
 a.PH_LEVEL6,
 a.PH_LEVEL7,
 a.PH_LEVEL8,
 a.PH_LEVEL9,
 a.PH_LEVEL10,
 d.DISKZ SLOC_RP_IND,
 a.FRP_MBQ,
 a.FRP_MDQ,
 a.FRP_RP_TYPE,
 b.EKGRP PURCHASE_GRP,
 SGST,
 CGST,
 IGST,
 a.J_1BBRANCH,
 a.GSTIN,
 合并(a.CONVERTED_TRANSIT_QTY,1)* B.TRAME CONVERTED_TRANSIT_QTY,
 a.FORMAT_CD,
 一个地区,
 a.SITE_CATEGORY,
 a.ZONE_ID,
 a.CLUSTER_ID,
 a.COMPANY_CODE,
 a.ARTICLE_HIERARCHY
 --- '#N/A'
 和W.SPDBI ='00000000'
 和d.LGORT ='1000'

 全联盟

 选择
 a.SUPPLYING_VENDOR,
 a.SUPPLYING_DC,
 网站,
 LTRIM(a.ARTICLE,'0')文章,
 合并(d.lgort,'1000')SLOC,
 B.MAABC ABC_CLASSIFICATION_MARC,
 B.MMSTA SITE_ARTICLE_STATUS,
 B.DISMM RP_TYPE,
 B.CONS_PROCG PROCUREMENT_TYPE,
 B.NON_MDSE_ID DELIVERY_TYPE,
 B.ZRED_DOT BLACK_DOT_FLAG,
 (以d.LGORT IN('1003','1010','2000','2004')然后d.LABST ELSE 0 END为例)SALEABLE_STK_QTY,
 (以d.LGORT IN('1001')然后d.LABST ELSE 0 END为例)DISPLAY_STK_QTY,
 (当d.LGORT不在('1001','1003','1010','2000','2004')然后LABST ELSE 0 END时的情况)OTHER_SLOC_STK_QTY,
 d.LABST UNRESTRICTED_STK_QTY,
 d.INSME QUALITY_STK_QTY,
 d.SPEME BLOCKED_STK_QTY,
 (当COALESCE(d.LGORT,'1000')= COALESCE(e.SLOC,'1000')THEN B.UMLMC ELSE 0 END时),TRANSFER_STK_QTY,
 (当COALESCE(d.LGORT,'1000')= COALESCE(e.SLOC,'1000')然后B.TRAME ELSE 0 END时的情况)TRANSIT_STK_QTY,


 (当COALESCE(d.LGORT,'1000')= COALESCE(e.SLOC,'1000')时的情况
 然后(COALESCE(d.LABST,0)+ COALESCE(B.TRAME,0)+ COALESCE(B.UMLMC,0)+ COALESCE(d.INSME,0)+ COALESCE(d.SPEME,0))
 否则(COALESCE(d.LABST,0)+ COALESCE(d.INSME,0)+ COALESCE(d.SPEME,0))结束
 )TOTAL_STK_QTY,
 ((当COALESCE(d.LGORT,'1000')= COALESCE(e.SLOC,'1000'时的情况)
 然后(COALESCE(d.LABST,0)+ COALESCE(B.TRAME,0)+ COALESCE(B.UMLMC,0)+ COALESCE(d.INSME,0)+ COALESCE(d.SPEME,0))
 否则(COALESCE(d.LABST,0)+ COALESCE(d.INSME,0)+ COALESCE(d.SPEME,0))结束
 )* C.VERPR)TOTAL_STK_VALUE,
 C.VERPR MOVING_AVG_PRICE,
 (当COALESCE(d.LGORT,'1000')= COALESCE(e.SLOC,'1000')然后B.MINBE ELSE 0 END时)REORDER_POINT,
 (当COALESCE(d.LGORT,'1000')= COALESCE(e.SLOC,'1000')然后B.EISBE ELSE 0 END时)SAFETY_STOCK,
 (当COALESCE(d.LGORT,'1000')= COALESCE(e.SLOC,'1000')时的情况(当B.MABST <= 0则TARGET_STOCK ELSE B.MABST END)ELSE 0 END的情况)MBQ,
 (当B.MABST <= 0且TARGET_STOCK <= 0则0 ELSE 1 END时)MBQ_FLAG,
 (C.LBKUM <0 THEN 1的情况,C.LBKUM = 0 THEN 2的情况,C.LBKUM> 0 THEN 3 ELSE 2 END的情况)OOS_QTY,
 (当C.SALK3 <0 THEN 1时,C.SALK3 = 0 THEN 2时,C.SALK3> 0 THEN 3 ELSE 2 END时)OOS_VALUE,
 (在COALESCE(d.LGORT,'1000')= COALESCE(e.SLOC,'1000')THEN 1 ELSE 0 END的情况下)ARTICLE_COUNT,
 CURRENT_DATERECORD_DATE,
 0 CONSIGN_STOCK,
 B.SOBSL SP_PROC_TYPE,
 B.BWSCL SOURCE_OF_SUPP,
 d.LABST * C.VERPR SLOC_VALUE,
 a.NIA_FLAG,
 a.PROMOTION_NO,
 a.NIP_PROMO,
 a.LAST_SALE_DATE,
 a.LAST_SALE_BUCKET,
 SBU,
 段
 一个家族,
 一类,
 砖头
 a.MATL_GROUP,
 a.MATL_TYPE,
 邦
 a.PH_LEVEL6,
 a.PH_LEVEL7,
 a.PH_LEVEL8,
 a.PH_LEVEL9,
 a.PH_LEVEL10,
 d.DISKZ SLOC_RP_IND,
 a.FRP_MBQ,
 a.FRP_MDQ,
 a.FRP_RP_TYPE,
 b.EKGRP PURCHASE_GRP,
 SGST,
 CGST,
 IGST,
 a.J_1BBRANCH,
 a.GSTIN,
 coalesce(a.CONVERTED_TRANSIT_QTY,1)*(当COALESCE(d.LGORT,'1000')= COALESCE(e.SLOC,'1000')THEN B.TRAME ELSE 0 END时的情况)CONVERTED_TRANSIT_QTY,
 a.FORMAT_CD,
 一个地区,
 a.SITE_CATEGORY,
 a.ZONE_ID,
 a.CLUSTER_ID,
 a.COMPANY_CODE,
 a.ARTICLE_HIERARCHY
 从HADMIN.S_CSR_ACTIVE_SITE_ARTICLE a
 a.site = b.werks和a.article = b.matnr上的左外部连接P22.MARC b
 a.site = c.bwkey和a.article = c.matnr上的左外部连接P22.MBEW c
 a.site = d.werks和a.article = d.matnr上的左外部连接P22.MARD d
 左外连接(
 从P22.MARD中选择WERKS站点,MATNR文章,MIN(LGORT)SLOC
 按工作组,MATNR
 MIN(LGORT)<>'1000'
 )e在a.SITE = e.SITE和a.ARTICLE = e.ARTICLE上
 内部联接P22.WRF1 w ON a.SITE = w.LOCNR
 其中a.ARTICLE <>'#N/A'
 和W.SPDBI ='00000000'
 和d.LGORT <>'1000'
 ;

 从:t_rst选择count(*)到v_cnt;

 v_loop = v_cnt/2500000;


 --------截断表STG_CSR_CURR_STOCK -------------------------

 截断表HADMIN.SUM_CSR_CURR_STOCK;


 ----------尝试实现批量插入逻辑----------------------------------  ------


 FOR X in 0 .. v_loop DO


 插入HADMIN.SUM_CSR_CURR_STOCK
 选择* FROM:t_rst LIMIT 2500000 OFFSET:count;
 承诺;
 计数:=计数+ 2500000;
 结束于;
 结束;
 

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

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


这是我插入STOCK摘要表中的过程。
每天最多可输出2.5亿条记录。
是否有必要优化以下过程或以更好的方式编写它?

创建过程PROC_CSR_CURR_STOCK
 语言SQLSCRIPT
 SQL安全调用程序
 预设SCHEMA HADMIN
 如
 开始
 -------------------代码开始-------------------------------------  ---------------

 DECLARE X INT:= 0;
 DECLARE计数INT:= 0;
 DECLARE v_cnt INT;
 DECLARE v_loop INT;

 t_rst =
 选择
 a.SUPPLYING_VENDOR,
 a.SUPPLYING_DC,
 网站,
 LTRIM(a.ARTICLE,'0')文章,
 d.lgort SLOC,
 B.MAABC ABC_CLASSIFICATION_MARC,
 B.MMSTA SITE_ARTICLE_STATUS,
 B.DISMM RP_TYPE,
 B.CONS_PROCG PROCUREMENT_TYPE,
 B.NON_MDSE_ID DELIVERY_TYPE,
 B.ZRED_DOT BLACK_DOT_FLAG,
 d.LABST SALEABLE_STK_QTY,
 0 DISPLAY_STK_QTY,
 0 OTHER_SLOC_STK_QTY,
 d.LABST UNRESTRICTED_STK_QTY,
 d.INSME QUALITY_STK_QTY,
 d.SPEME BLOCKED_STK_QTY,
 B.UMLMC TRANSFER_STK_QTY,
 B.TRAME TRANSIT_STK_QTY,
 (COALESCE(d.LABST,0)+ COALESCE(B.TRAME,0)+ COALESCE(B.UMLMC,0)+ COALESCE(d.INSME,0)+ COALESCE(d.SPEME,0))TOTAL_STK_QTY,
 (((COALESCE(d.LABST,0)+ COALESCE(B.TRAME,0)+ COALESCE(B.UMLMC,0)+ COALESCE(d.INSME,0)+ COALESCE(d.SPEME,0))* C.  VERPR),共TOTAL_STK_VALUE,
 C.VERPR MOVING_AVG_PRICE,
 B.MINBE REORDER_POINT,
 B.EISBE SAFETY_STOCK,
 (当B.MABST <= 0则TARGET_STOCK ELSE B.MABST结束时的情况)MBQ,
 (当B.MABST <= 0且TARGET_STOCK <= 0则0 ELSE 1 END时)MBQ_FLAG,
 (C.LBKUM <0 THEN 1的情况,C.LBKUM = 0 THEN 2的情况,C.LBKUM> 0 THEN 3 ELSE 2 END的情况)OOS_QTY,
 (当C.SALK3 <0 THEN 1时,C.SALK3 = 0 THEN 2时,C.SALK3> 0 THEN 3 ELSE 2 END时)OOS_VALUE,
 1条ARTICLE_COUNT,
 CURRENT_DATERECORD_DATE,
 0 CONSIGN_STOCK,
 B.SOBSL SP_PROC_TYPE,
 B.BWSCL SOURCE_OF_SUPP,
 d.LABST * C.VERPR SLOC_VALUE,
 a.NIA_FLAG,
 a.PROMOTION_NO,
 a.NIP_PROMO,
 a.LAST_SALE_DATE,
 a.LAST_SALE_BUCKET,
 SBU,
 段
 一个家族,
 一类,
 砖头
 a.MATL_GROUP,
 a.MATL_TYPE,
 邦
 a.PH_LEVEL6,
 a.PH_LEVEL7,
 a.PH_LEVEL8,
 a.PH_LEVEL9,
 a.PH_LEVEL10,
 d.DISKZ SLOC_RP_IND,
 a.FRP_MBQ,
 a.FRP_MDQ,
 a.FRP_RP_TYPE,
 b.EKGRP PURCHASE_GRP,
 SGST,
 CGST,
 IGST,
 a.J_1BBRANCH,
 a.GSTIN,
 合并(a.CONVERTED_TRANSIT_QTY,1)* B.TRAME CONVERTED_TRANSIT_QTY,
 a.FORMAT_CD,
 一个地区,
 a.SITE_CATEGORY,
 a.ZONE_ID,
 a.CLUSTER_ID,
 a.COMPANY_CODE,
 a.ARTICLE_HIERARCHY
 --- '#N/A'
 和W.SPDBI ='00000000'
 和d.LGORT ='1000'

 全联盟

 选择
 a.SUPPLYING_VENDOR,
 a.SUPPLYING_DC,
 网站,
 LTRIM(a.ARTICLE,'0')文章,
 合并(d.lgort,'1000')SLOC,
 B.MAABC ABC_CLASSIFICATION_MARC,
 B.MMSTA SITE_ARTICLE_STATUS,
 B.DISMM RP_TYPE,
 B.CONS_PROCG PROCUREMENT_TYPE,
 B.NON_MDSE_ID DELIVERY_TYPE,
 B.ZRED_DOT BLACK_DOT_FLAG,
 (以d.LGORT IN('1003','1010','2000','2004')然后d.LABST ELSE 0 END为例)SALEABLE_STK_QTY,
 (以d.LGORT IN('1001')然后d.LABST ELSE 0 END为例)DISPLAY_STK_QTY,
 (当d.LGORT不在('1001','1003','1010','2000','2004')然后LABST ELSE 0 END时的情况)OTHER_SLOC_STK_QTY,
 d.LABST UNRESTRICTED_STK_QTY,
 d.INSME QUALITY_STK_QTY,
 d.SPEME BLOCKED_STK_QTY,
 (当COALESCE(d.LGORT,'1000')= COALESCE(e.SLOC,'1000')THEN B.UMLMC ELSE 0 END时),TRANSFER_STK_QTY,
 (当COALESCE(d.LGORT,'1000')= COALESCE(e.SLOC,'1000')然后B.TRAME ELSE 0 END时的情况)TRANSIT_STK_QTY,


 (当COALESCE(d.LGORT,'1000')= COALESCE(e.SLOC,'1000')时的情况
 然后(COALESCE(d.LABST,0)+ COALESCE(B.TRAME,0)+ COALESCE(B.UMLMC,0)+ COALESCE(d.INSME,0)+ COALESCE(d.SPEME,0))
 否则(COALESCE(d.LABST,0)+ COALESCE(d.INSME,0)+ COALESCE(d.SPEME,0))结束
 )TOTAL_STK_QTY,
 ((当COALESCE(d.LGORT,'1000')= COALESCE(e.SLOC,'1000'时的情况)
 然后(COALESCE(d.LABST,0)+ COALESCE(B.TRAME,0)+ COALESCE(B.UMLMC,0)+ COALESCE(d.INSME,0)+ COALESCE(d.SPEME,0))
 否则(COALESCE(d.LABST,0)+ COALESCE(d.INSME,0)+ COALESCE(d.SPEME,0))结束
 )* C.VERPR)TOTAL_STK_VALUE,
 C.VERPR MOVING_AVG_PRICE,
 (当COALESCE(d.LGORT,'1000')= COALESCE(e.SLOC,'1000')然后B.MINBE ELSE 0 END时)REORDER_POINT,
 (当COALESCE(d.LGORT,'1000')= COALESCE(e.SLOC,'1000')然后B.EISBE ELSE 0 END时)SAFETY_STOCK,
 (当COALESCE(d.LGORT,'1000')= COALESCE(e.SLOC,'1000')时的情况(当B.MABST <= 0则TARGET_STOCK ELSE B.MABST END)ELSE 0 END的情况)MBQ,
 (当B.MABST <= 0且TARGET_STOCK <= 0则0 ELSE 1 END时)MBQ_FLAG,
 (C.LBKUM <0 THEN 1的情况,C.LBKUM = 0 THEN 2的情况,C.LBKUM> 0 THEN 3 ELSE 2 END的情况)OOS_QTY,
 (当C.SALK3 <0 THEN 1时,C.SALK3 = 0 THEN 2时,C.SALK3> 0 THEN 3 ELSE 2 END时)OOS_VALUE,
 (在COALESCE(d.LGORT,'1000')= COALESCE(e.SLOC,'1000')THEN 1 ELSE 0 END的情况下)ARTICLE_COUNT,
 CURRENT_DATERECORD_DATE,
 0 CONSIGN_STOCK,
 B.SOBSL SP_PROC_TYPE,
 B.BWSCL SOURCE_OF_SUPP,
 d.LABST * C.VERPR SLOC_VALUE,
 a.NIA_FLAG,
 a.PROMOTION_NO,
 a.NIP_PROMO,
 a.LAST_SALE_DATE,
 a.LAST_SALE_BUCKET,
 SBU,
 段
 一个家族,
 一类,
 砖头
 a.MATL_GROUP,
 a.MATL_TYPE,
 邦
 a.PH_LEVEL6,
 a.PH_LEVEL7,
 a.PH_LEVEL8,
 a.PH_LEVEL9,
 a.PH_LEVEL10,
 d.DISKZ SLOC_RP_IND,
 a.FRP_MBQ,
 a.FRP_MDQ,
 a.FRP_RP_TYPE,
 b.EKGRP PURCHASE_GRP,
 SGST,
 CGST,
 IGST,
 a.J_1BBRANCH,
 a.GSTIN,
 coalesce(a.CONVERTED_TRANSIT_QTY,1)*(当COALESCE(d.LGORT,'1000')= COALESCE(e.SLOC,'1000')THEN B.TRAME ELSE 0 END时的情况)CONVERTED_TRANSIT_QTY,
 a.FORMAT_CD,
 一个地区,
 a.SITE_CATEGORY,
 a.ZONE_ID,
 a.CLUSTER_ID,
 a.COMPANY_CODE,
 a.ARTICLE_HIERARCHY
 从HADMIN.S_CSR_ACTIVE_SITE_ARTICLE a
 a.site = b.werks和a.article = b.matnr上的左外部连接P22.MARC b
 a.site = c.bwkey和a.article = c.matnr上的左外部连接P22.MBEW c
 a.site = d.werks和a.article = d.matnr上的左外部连接P22.MARD d
 左外连接(
 从P22.MARD中选择WERKS站点,MATNR文章,MIN(LGORT)SLOC
 按工作组,MATNR
 MIN(LGORT)<>'1000'
 )e在a.SITE = e.SITE和a.ARTICLE = e.ARTICLE上
 内部联接P22.WRF1 w ON a.SITE = w.LOCNR
 其中a.ARTICLE <>'#N/A'
 和W.SPDBI ='00000000'
 和d.LGORT <>'1000'
 ;

 从:t_rst选择count(*)到v_cnt;

 v_loop = v_cnt/2500000;


 --------截断表STG_CSR_CURR_STOCK -------------------------

 截断表HADMIN.SUM_CSR_CURR_STOCK;


 ----------尝试实现批量插入逻辑----------------------------------  ------


 FOR X in 0 .. v_loop DO


 插入HADMIN.SUM_CSR_CURR_STOCK
 选择* FROM:t_rst LIMIT 2500000 OFFSET:count;
 承诺;
 计数:=计数+ 2500000;
 结束于;
 结束;
 
付费偷看设置
发送
3条回答
悠然的二货
1楼-- · 2020-09-06 19:16

您好

您要获取2个数据集,执行UNION ALL,然后将其拆分为250k块以插入它。 UNION ALL没有功能上的好处,因此作为初步建议,因为将并行执行INSERT,所以分别处理2个数据集。

您是否尝试过将2个数据集直接插入目标表中? 无需分块(使用带有嵌套SELECT的INSERT)?

对目标表进行分区也可以提高INSERT性能。

Michael

三十六小时_GS
2楼-- · 2020-09-06 19:12

并行插入将在分区表上工作。

我在堆栈溢出时看到了这个确切的问题,所以我们现在正在重复工作- https://stackoverflow.com/questions/54274795/sap-hana-bulk-insert-in-sap-hana

悠然的二货
3楼-- · 2020-09-06 19:08

嗨,

这也许也很愚蠢,执行

ST04->诊断->解释

这也应该表明可能的改进。

>

亲切的问候

约翰

一周热门 更多>