时间戳

首页 > 解决方案 > HANA SQL 性能

sql - HANA SQL 性能

问题描述

有人可以帮助我以适当的方式重塑这段代码。该守则是旧守则，希望对其进行改造。但不知道如何做得更好。

        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));

标签： sqlhana

解决方案

To improve the speed, if you want to keep using SQL script, firstly you should replace the where statements with a join statement like below:

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));

If this doesn't help you, you should use HANA's Analytic Views for which you could read here.

推荐阅读