降低查询复杂性

Question

我有这个多连接查询，有什么方法可以减少连接的数量吗？或者可能将查询分成两部分，但仍然得到相同的结果集？连接过多会导致查询的执行速度非常慢且效率低下

SELECT  
    MD.EVENT_ID,
    AUI.ATLAS_USER_ID,
    EVENT_TIME, 
    EVENT_TYPE, 
    INTERACTION_TOKEN, 
    CC.COUNTRY_CODE, 
    AP.ADV_PROJECT_ID, AP.ADV_PROJECT_NAME, 
    AC.ADV_CAMPAIGN_ID,
    ADV_CAMPAIGN_NAME,
    PC.PRT_CAMPAIGN_ID, PC.PRT_CAMPAIGN_NAME,
    IP.IP_ADDRESS, 
    OS.OS, 
    BR.BROWSER, 
    FU.FULL_USER_AGENT, 
    RAW_ACTION_ID, 
    SE.SELLER_NETWORK_ID,
    RIURL.RAW_INPUT as URL,
    RIREF.RAW_INPUT as REF_URL,
    MVG1.MAP_VALUE as    TABOOLA   ,    
    MVG2.MAP_VALUE as    APPNEXUS          ,
    MVG3.MAP_VALUE as    ETAG              ,
    MVG4.MAP_VALUE as    FACEBOOK          ,
    MVG5.MAP_VALUE as    MEDIAMATH         ,
    MVG6.MAP_VALUE as    COOKIEID          ,
    MVG7.MAP_VALUE as    IDFA              ,
    MVG8.MAP_VALUE as    ADVLOGIN          ,
    MVG9.MAP_VALUE as    OPENX             ,
    MVG10.MAP_VALUE as   ADTRUTH           ,
    MVG11.MAP_VALUE as   GOOGLE            ,
    MVG12.MAP_VALUE as   ANDROID_ADV_ID    ,
    MVG13.MAP_VALUE as   SDGUPI            ,
    MVG15.MAP_VALUE as   RMX                
FROM 
    EVENT_124_2 BASE
INNER JOIN 
    atlas__atlas_events MD ON BASE.EVENT_ID = MD.EVENT_ID
LEFT JOIN 
    LOOKUP_STG__MAP_VALUE MVG1 ON MVG1.MAP_VALUE_GK = BASE.TABOOLA_HASH
LEFT JOIN 
    LOOKUP_STG__MAP_VALUE MVG2 ON MVG2.MAP_VALUE_GK = BASE.APPNEXUS_HASH       
LEFT JOIN 
    LOOKUP_STG__MAP_VALUE MVG3 ON MVG3.MAP_VALUE_GK = BASE.ETAG_HASH           
LEFT JOIN 
    LOOKUP_STG__MAP_VALUE MVG4 ON MVG4.MAP_VALUE_GK = BASE.FACEBOOK_HASH       
LEFT JOIN 
    LOOKUP_STG__MAP_VALUE MVG5 ON MVG5.MAP_VALUE_GK = BASE.MEDIAMATH_HASH      
LEFT JOIN 
    LOOKUP_STG__MAP_VALUE MVG6 ON MVG6.MAP_VALUE_GK = BASE.COOKIEID_HASH       
LEFT JOIN 
    LOOKUP_STG__MAP_VALUE MVG7 ON MVG7.MAP_VALUE_GK = BASE.IDFA_HASH           
LEFT JOIN 
    LOOKUP_STG__MAP_VALUE MVG8 ON MVG8.MAP_VALUE_GK = BASE.ADVLOGIN_HASH       
LEFT JOIN 
    LOOKUP_STG__MAP_VALUE MVG9 ON MVG9.MAP_VALUE_GK = BASE.OPENX_HASH          
LEFT JOIN 
    LOOKUP_STG__MAP_VALUE MVG10 ON MVG10.MAP_VALUE_GK = BASE.ADTRUTH_HASH        
LEFT JOIN 
    LOOKUP_STG__MAP_VALUE MVG11 ON MVG11.MAP_VALUE_GK = BASE.GOOGLE_HASH         
LEFT JOIN 
    LOOKUP_STG__MAP_VALUE MVG12 ON MVG12.MAP_VALUE_GK = BASE.ANDROID_ADV_ID_HASH 
LEFT JOIN 
    LOOKUP_STG__MAP_VALUE MVG13 ON MVG13.MAP_VALUE_GK = BASE.SDGUPI_HASH         
LEFT JOIN 
    LOOKUP_STG__MAP_VALUE MVG15 ON MVG15.MAP_VALUE_GK = BASE.RMX_HASH  
LEFT JOIN 
    LOOKUP_STG__ATLAS_USER_ID AUI ON AUI.ATLAS_USER_ID_GK = MD.ATLAS_USER_ID
LEFT JOIN 
    GAYA__ADV_PROJECTS AP ON AP.ADV_PROJECT_ID = MD.ADV_PROJECT_ID
LEFT JOIN 
    GAYA__ADV_CAMPAIGNS AC ON AC.ADV_CAMPAIGN_ID = MD.ADV_CAMPAIGN_ID
LEFT JOIN 
    GAYA__PRT_CAMPAIGNS PC ON PC.PRT_CAMPAIGN_ID = MD.PRT_CAMPAIGN_ID
LEFT JOIN 
    LOOKUP_STG__RAW_INPUT RIURL ON RIURL.RAW_INPUT_GK = BASE.URL_HASH
LEFT JOIN 
    LOOKUP_STG__RAW_INPUT RIREF ON RIREF.RAW_INPUT_GK = BASE.REF_HASH
LEFT JOIN 
    LOOKUP_STG__OS OS ON OS.OS_GK = MD.COUNTRY_CODE
LEFT JOIN 
    LOOKUP_STG__COUNTRY_CODE CC ON CC.COUNTRY_CODE_GK = MD.COUNTRY_CODE
LEFT JOIN 
    LOOKUP_STG__BROWSER BR ON BR.BROWSER_GK = MD.BROWSER
LEFT JOIN 
    LOOKUP_STG__IP_ADDRESS IP   ON IP.IP_ADDRESS_GK = MD.IP_ADDRESS
LEFT JOIN 
    LOOKUP_STG__SELLER_NETWORK_ID  SE     ON SE.SELLER_NETWORK_ID_GK = MD.SELLER_NETWORK_ID
LEFT JOIN 
    LOOKUP_STG__FULL_USER_AGENT FU   ON FU.FULL_USER_AGENT_GK = MD.FULL_USER_AGENT
;

Answer 1

您的键列上有索引吗？您可以尝试将位图索引放在主事实表和维度表中的每个键列上。这应该会给您带来相当大的加速比。

Answer 2

您说连接的数量会使查询效率低下。但您应该注意的是，与联接的表中是否有适当的索引。显然，您需要连接到的表中的所有字段，所以不能简单地不连接它们。

对于您在查询中创建的每个JOIN，请验证与联接的字段上是否存在INDEX。例如：

INNER JOIN 
    atlas__atlas_events MD ON BASE.EVENT_ID = MD.EVENT_ID

表atlas__atlas_events的EVENT_ID列上有INDEX吗？

您需要为查询中的每个这样的JOIN验证这一点。如果这样的INDEX不存在，您应该创建一个。

如果在SQL Server Management Studio中执行此查询并包含actual execution plan，则可能已经看到缺少索引的指示。