HANA SQL性能

Question

有人能帮我以适当的方式重塑这段代码吗？此代码是旧代码，希望对其进行重塑。但不确定如何做得更好。

        SELECT COUNT(*) FROM (
        SELECT SRC.CLIENT,SRC.GUID
        ,COALESCE(SRC.PARTNER_ZSCHEDUL,'')                                  PARTNER_ZSCHEDUL
        ,COALESCE(SRC.PARTNER_ZSVCUNIT,'')                                  PARTNER_ZSVCUNIT            
        ,COALESCE(SRC.PARTNER_ZPARTAPP,'')                                  PARTNER_ZPARTAPP            
        ,COALESCE(SRC.PARTNER_ZESCOWN,'')                                   PARTNER_ZESCOWN     
        ,COALESCE(SRC.PARTNER_ZESCAPRV,'')                                  PARTNER_ZESCAPRV
        ,COALESCE(SRC.PARTNER_ZESCRQST,'')                                  PARTNER_ZESCRQST
        ,COALESCE(SRC.PARTNER_ZPARTORD,'')                                  PARTNER_ZPARTORD            
        ,COALESCE(SRC.PARTNER_ER,'')                                        PARTNER_ER                  
          FROM KCP810_HBP.ZCRMD_ORDERADM_I_EXTN TGT,                                (
        SELECT CLIENT,GUID
        , MIN(CASE WHEN PARTNER_FCT = 'ZSCHEDUL' THEN PARTNER           END)    PARTNER_ZSCHEDUL
        , MIN(CASE WHEN PARTNER_FCT = 'ZSVCUNIT' THEN MERGED_PARTNER    END)    PARTNER_ZSVCUNIT
        , MIN(CASE WHEN PARTNER_FCT = 'ZSVCUNIT' THEN PARTNER           END)    PARTNER_ZSVCUNIT_HR
        , MIN(CASE WHEN PARTNER_FCT = 'ZPARTAPP' THEN PARTNER           END)    PARTNER_ZPARTAPP
        , MIN(CASE WHEN PARTNER_FCT = 'ZESCOWN'  THEN PARTNER           END)    PARTNER_ZESCOWN
        , MIN(CASE WHEN PARTNER_FCT = 'ZESCAPRV' THEN PARTNER           END)    PARTNER_ZESCAPRV
        , MIN(CASE WHEN PARTNER_FCT = 'ZESCRQST' THEN PARTNER           END)    PARTNER_ZESCRQST
        , MIN(CASE WHEN PARTNER_FCT = 'ZPARTORD' THEN PARTNER           END)    PARTNER_ZPARTORD
        , MIN(CASE WHEN PARTNER_FCT = '00000014' THEN PARTNER           END)    PARTNER_ER
          FROM "_SYS_BIC"."CRM-SV.A-COMMON-VIEWS.Proc.Item/CV_IT_01_PARTNER_ALL" GROUP BY CLIENT,GUID ) SRC
                                        WHERE     TGT.CLIENT                      = SRC.CLIENT
                                          AND     TGT.GUID                        = SRC.GUID 
                                          AND (   TGT.PARTNER_ZSCHEDUL != SRC.PARTNER_ZSCHEDUL OR TGT.PARTNER_ZSVCUNIT != SRC.PARTNER_ZSVCUNIT OR 
                                                  TGT.PARTNER_ZPARTAPP != SRC.PARTNER_ZPARTAPP OR TGT.PARTNER_ZESCOWN  != SRC.PARTNER_ZESCOWN  OR 
                                                  TGT.PARTNER_ZESCAPRV != SRC.PARTNER_ZESCAPRV OR TGT.PARTNER_ZESCRQST != SRC.PARTNER_ZESCRQST OR 
                        TGT.PARTNER_ZPARTORD != SRC.PARTNER_ZPARTORD OR TGT.PARTNER_00000014 != SRC.PARTNER_ER));

Answer 1

为了提高速度，如果您想继续使用SQL脚本，首先需要将where语句替换为如下所示的join语句：

SELECT COUNT(*) FROM (
    SELECT SRC.CLIENT,SRC.GUID
    ,COALESCE(SRC.PARTNER_ZSCHEDUL,'')                                  PARTNER_ZSCHEDUL
    ,COALESCE(SRC.PARTNER_ZSVCUNIT,'')                                  PARTNER_ZSVCUNIT            
    ,COALESCE(SRC.PARTNER_ZPARTAPP,'')                                  PARTNER_ZPARTAPP            
    ,COALESCE(SRC.PARTNER_ZESCOWN,'')                                   PARTNER_ZESCOWN     
    ,COALESCE(SRC.PARTNER_ZESCAPRV,'')                                  PARTNER_ZESCAPRV
    ,COALESCE(SRC.PARTNER_ZESCRQST,'')                                  PARTNER_ZESCRQST
    ,COALESCE(SRC.PARTNER_ZPARTORD,'')                                  PARTNER_ZPARTORD            
    ,COALESCE(SRC.PARTNER_ER,'')                                        PARTNER_ER                  
      FROM KCP810_HBP.ZCRMD_ORDERADM_I_EXTN TGT
      INNER JOIN                                 (
    SELECT CLIENT,GUID
    , MIN(CASE WHEN PARTNER_FCT = 'ZSCHEDUL' THEN PARTNER           END)    PARTNER_ZSCHEDUL
    , MIN(CASE WHEN PARTNER_FCT = 'ZSVCUNIT' THEN MERGED_PARTNER    END)    PARTNER_ZSVCUNIT
    , MIN(CASE WHEN PARTNER_FCT = 'ZSVCUNIT' THEN PARTNER           END)    PARTNER_ZSVCUNIT_HR
    , MIN(CASE WHEN PARTNER_FCT = 'ZPARTAPP' THEN PARTNER           END)    PARTNER_ZPARTAPP
    , MIN(CASE WHEN PARTNER_FCT = 'ZESCOWN'  THEN PARTNER           END)    PARTNER_ZESCOWN
    , MIN(CASE WHEN PARTNER_FCT = 'ZESCAPRV' THEN PARTNER           END)    PARTNER_ZESCAPRV
    , MIN(CASE WHEN PARTNER_FCT = 'ZESCRQST' THEN PARTNER           END)    PARTNER_ZESCRQST
    , MIN(CASE WHEN PARTNER_FCT = 'ZPARTORD' THEN PARTNER           END)    PARTNER_ZPARTORD
    , MIN(CASE WHEN PARTNER_FCT = '00000014' THEN PARTNER           END)    PARTNER_ER
      FROM "_SYS_BIC"."CRM-SV.A-COMMON-VIEWS.Proc.Item/CV_IT_01_PARTNER_ALL" GROUP BY CLIENT,GUID ) SRC
      ON TGT.CLIENT                      = SRC.CLIENT
      AND TGT.GUID                        = SRC.GUID
      AND (   TGT.PARTNER_ZSCHEDUL != SRC.PARTNER_ZSCHEDUL 
            OR TGT.PARTNER_ZSVCUNIT != SRC.PARTNER_ZSVCUNIT 
            OR TGT.PARTNER_ZPARTAPP != SRC.PARTNER_ZPARTAPP 
            OR TGT.PARTNER_ZESCOWN  != SRC.PARTNER_ZESCOWN  
            OR TGT.PARTNER_ZESCAPRV != SRC.PARTNER_ZESCAPRV 
            OR TGT.PARTNER_ZESCRQST != SRC.PARTNER_ZESCRQST 
            OR TGT.PARTNER_ZPARTORD != SRC.PARTNER_ZPARTORD 
            OR TGT.PARTNER_00000014 != SRC.PARTNER_ER));

如果这对你没有帮助，你应该使用HANA的分析视图，你可以阅读here。