代码之家 › 专栏 › 技术社区 › Ram

左连接视图在SQL Server中占用太多时间

query-performance sql-server-2012

0

Ram · 技术社区 · 7 年前

此查询需要20秒才能显示53000条记录。此查询有五个与视图的左连接。但当我在专栏中发表评论时 MAN.F_PHR AS MANU 在select语句中,显示53000条记录需要三秒。

如何优化此查询?风景 V_PROD_ALIAS_MANU MAN 包含两个左连接。这个 MAN.F_PHRE AS MANU 列从视图中删除。有没有办法优化这个查询?

SELECT DISTINCT 
    TP.PRODUCT AS ID,
    TP.NAME AS [NAME],
    TP.LANGUAGE AS LANGCODE,
    CONVERT(VARCHAR,TP.F_DATE_REVISED,120) AS RDATE,
    CASE
       WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'Â¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'Â¿',' '))),' ','; ') = '') 
          THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'Â¿',' '))),' ','; ') 
          ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'Â¿',' '))),' ','; ')
    END AS CASNUM ,
    TP.F_CUSTOM1 AS cus1,
    TP.F_CUSTOM2 AS cus2,
    (SELECT TC.F_COUNTRY_NAME FROM COUNTRIES TC 
     WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5,
    MAN.F_PHR AS MANU,
    CASE
       WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') 
          THEN TP.F_CUSTOM3 
          ELSE SYN.F_DATA
    END AS SYN, 
    IC.F_DATA AS ICO,
    'SDS - ' + (SELECT TL.F_LAN_NAME FROM T_LANGUAGE TL 
                WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) + ' - PDF' AS DOC,
    '' AS COVER,
    CAST(TP.F_GUID AS VARCHAR(36)) + '_PDF' AS [GUID],
    'PDF' AS SDS
FROM 
    PDF TP
LEFT JOIN 
    V_PROD_ALIAS_SYN SYN ON TP.F_PRODUCT = SYN.F_PRODUCT
LEFT JOIN 
    V_PROD_ALIAS_SITE SIT ON TP.F_PRODUCT = SIT.F_PRODUCT
LEFT JOIN 
    V_PROD_ALIAS_ICO IC ON TP.F_PRODUCT = IC.F_PRODUCT
LEFT JOIN 
    V_PROD_ALIAS_MANU MAN ON TP.F_PRODUCT = MAN.F_PRODUCT
LEFT JOIN 
    V_PROD_ALIAS_CASN CASN ON TP.F_PRODUCT = CASN.F_PRODUCT

在注释掉该列后进行查询 成年男子F_PHRE AS MANU 显示53000条记录需要3秒钟。

SELECT DISTINCT 
    TP.PRODUCT AS ID,
    TP.NAME AS [NAME],
    TP.LANGUAGE AS LANGCODE,
    CONVERT(VARCHAR, TP.F_DATE_REVISED, 120) AS RDATE,
    CASE
       WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'Â¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'Â¿',' '))),' ','; ') = '') 
          THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUM,'Â¿',' '))),' ','; ') 
          ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'Â¿',' '))),' ','; ')
    END AS CASNUM ,
    TP.F_CUSTOM1 AS cus1,
    TP.F_CUSTOM2 AS cus2,
    (SELECT TC.F_COUNTRY_NAME FROM COUNTRIES TC 
     WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5,
    -- MAN.F_PHR AS MANU,
    CASE
       WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') 
          THEN TP.F_CUSTOM3 
          ELSE SYN.F_DATA
    END AS SYN, 
    IC.F_DATA AS ICO,
    'SDS - ' + (SELECT TL.F_LAN_NAME FROM T_LANGUAGE TL 
                WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) + ' - PDF' AS DOC,
    '' AS COVER,
    CAST(TP.F_GUID AS VARCHAR(36)) + '_PDF' AS [GUID],
    'PDF' AS SDS
FROM 
    PDF TP
LEFT JOIN 
    V_PROD_ALIAS_SYN SYN ON TP.F_PRODUCT = SYN.F_PRODUCT
LEFT JOIN 
    V_PROD_ALIAS_SITE SIT ON TP.F_PRODUCT = SIT.F_PRODUCT
LEFT JOIN 
    V_PROD_ALIAS_ICO IC ON TP.F_PRODUCT = IC.F_PRODUCT
LEFT JOIN 
    V_PROD_ALIAS_MANU MAN ON TP.F_PRODUCT = MAN.F_PRODUCT
LEFT JOIN 
    V_PROD_ALIAS_CASN CASN ON TP.F_PRODUCT = CASN.F_PRODUCT

0 回复 | 直到 7 年前