sql-server-2008 - sql server 中视图和临时表之间的性能差异

Question

我的数据库中有以下视图。我在存储过程中使用左联接在该查询中使用此视图。执行需要更多时间。

CREATE VIEW [dbo].[V_PROD_ALIAS_CASN] AS
SELECT PD.F_PRODUCT AS F_PRODUCT, PD.F_DATA_CODE AS F_DATA_CODE, PD.F_DATA AS F_DATA
FROM PROD_DATA PD WHERE PD.F_DATA_CODE = 'CASN'
UNION ALL
SELECT AD.F_ALIAS AS F_PRODUCT, AD.F_DATA_CODE AS F_DATA_CODE, AD.F_DATA AS F_DATA
FROM PROD_ALIAS_DATA AD WHERE AD.F_DATA_CODE = 'CASN'

CREATE VIEW [dbo].[V_PROD_ALIAS_ICO] AS
SELECT PD.F_PRODUCT AS F_PRODUCT, PD.F_DATA_CODE AS F_DATA_CODE, PD.F_DATA AS F_DATA
FROM PROD_DATA PD WHERE PD.F_DATA_CODE = 'ICO'
UNION ALL
SELECT AD.F_ALIAS AS F_PRODUCT, AD.F_DATA_CODE AS F_DATA_CODE, AD.F_DATA AS F_DATA
FROM PROD_ALIAS_DATA AD WHERE AD.F_DATA_CODE = 'ICO'

CREATE VIEW [dbo].[V_PROD_ALIAS_MAN] AS
SELECT DISTINCT PT.F_PRODUCT AS F_PRODUCT, PT.F_TEXT_CODE AS F_TEXT_CODE, PHT.F_PHRASE AS F_PHRASE FROM T_PROD_TEXT PT
LEFT JOIN PHRASE_LINKAGE PHL
    ON PT.F_TEXT_CODE = PHL.F_TEXT_CODE
INNER JOIN PHRASE_TRANSLATIONS PHT
    ON PHL.F_PHRASE_ID = PHT.F_PHRASE_ID
WHERE PT.F_DATA_CODE = 'MAN' AND PHT.F_LANGUAGE = 'EN'
UNION ALL
SELECT DISTINCT PT.F_ALIAS AS F_PRODUCT, PT.F_TEXT_CODE AS F_TEXT_CODE, PHT.F_PHRASE AS F_PHRASE FROM T_PROD_ALIAS_TEXT PT
LEFT JOIN PHRASE_LINKAGE PHL
    ON PT.F_TEXT_CODE = PHL.F_TEXT_CODE
INNER JOIN PHRASE_TRANSLATIONS PHT
    ON PHL.F_PHRASE_ID = PHT.F_PHRASE_ID
WHERE PT.F_DATA_CODE = 'MAN' AND PHT.F_LANGUAGE = 'EN'


CREATE VIEW [dbo].[V_PROD_ALIAS_MANU] AS
SELECT DISTINCT PT.F_PRODUCT AS F_PRODUCT, PT.F_TEXT_CODE AS F_TEXT_CODE, PHT.F_PHRASE AS F_PHRASE FROM T_PROD_TEXT PT
LEFT JOIN PHRASE_LINKAGE PHL
    ON PT.F_TEXT_CODE = PHL.F_TEXT_CODE
INNER JOIN PHRASE_TRANSLATIONS PHT
    ON PHL.F_PHRASE_ID = PHT.F_PHRASE_ID
WHERE PT.F_DATA_CODE = 'MANU' AND PHT.F_LANGUAGE = 'EN'
UNION ALL
SELECT DISTINCT PT.F_ALIAS AS F_PRODUCT, PT.F_TEXT_CODE AS F_TEXT_CODE, PHT.F_PHRASE AS F_PHRASE FROM T_PROD_ALIAS_TEXT PT
LEFT JOIN PHRASE_LINKAGE PHL
    ON PT.F_TEXT_CODE = PHL.F_TEXT_CODE
INNER JOIN PHRASE_TRANSLATIONS PHT
    ON PHL.F_PHRASE_ID = PHT.F_PHRASE_ID
WHERE PT.F_DATA_CODE = 'MANU' AND PHT.F_LANGUAGE = 'EN'


CREATE VIEW [dbo].[V_PROD_ALIAS_SITE] AS
SELECT DISTINCT PT.F_PRODUCT AS F_PRODUCT, PT.F_TEXT_CODE AS F_TEXT_CODE, PHT.F_PHRASE AS F_PHRASE FROM T_PROD_TEXT PT
LEFT JOIN PHRASE_LINKAGE PHL
    ON PT.F_TEXT_CODE = PHL.F_TEXT_CODE
INNER JOIN PHRASE_TRANSLATIONS PHT
    ON PHL.F_PHRASE_ID = PHT.F_PHRASE_ID
WHERE PT.F_DATA_CODE = 'SITES' AND PHT.F_LANGUAGE = 'EN'
UNION ALL
SELECT DISTINCT PT.F_ALIAS AS F_PRODUCT, PT.F_TEXT_CODE AS F_TEXT_CODE, PHT.F_PHRASE AS F_PHRASE FROM T_PROD_ALIAS_TEXT PT
LEFT JOIN PHRASE_LINKAGE PHL
    ON PT.F_TEXT_CODE = PHL.F_TEXT_CODE
INNER JOIN PHRASE_TRANSLATIONS PHT
    ON PHL.F_PHRASE_ID = PHT.F_PHRASE_ID
WHERE PT.F_DATA_CODE = 'SITES' AND PHT.F_LANGUAGE = 'EN'


CREATE VIEW [dbo].[V_PROD_ALIAS_SYN] AS
SELECT PD.F_PRODUCT AS F_PRODUCT, PD.F_DATA_CODE AS F_DATA_CODE, PD.F_DATA AS F_DATA
FROM T_PROD_DATA PD WHERE PD.F_DATA_CODE = 'SYN2'
UNION ALL
SELECT AD.F_ALIAS AS F_PRODUCT, AD.F_DATA_CODE AS F_DATA_CODE, AD.F_DATA AS F_DATA
FROM T_PROD_ALIAS_DATA AD WHERE AD.F_DATA_CODE = 'SYN2'

在 Left Join 中使用上述视图进行原始查询。

SELECT DISTINCT TP.F_PRODUCT AS ID,
        TP.F_PRODUCT_NAME AS [NAME],
        (SELECT MS.F_MSDSTYPE_DESC FROM T_MSDSTYPES MS WHERE MS.F_MSDSTYPE = TP.F_SUBFORMAT AND MS.F_FORMAT = TP.F_FORMAT) AS SFMT,
        (SELECT TL.F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) AS LANG,
        TP.F_LANGUAGE AS LANGCODE,
        TP.F_SUBFORMAT AS SFMTCODE,
        TP.F_PLANT AS PLANTCODE,
        TP.F_FORMAT AS FMTCODE,
        CONVERT(VARCHAR,TP.F_DATE_REVISED,120) AS RDATE,
        CASE
            WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') 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 T_COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5,
        MAN.F_PHRASE AS MANU,
        CASE
            WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE ''
        END + ' ' +
        CASE
            WHEN NOT(SYN.F_DATA IS NULL OR SYN.F_DATA = '') THEN SYN.F_DATA ELSE ''
        END AS SYN,
        IC.F_DATA AS ICO,
        'SDS - ' + (SELECT TL.F_LANGUAGE_NAME FROM T_LANGUAGES 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 T_PDF_MSDS 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 
WHERE (TP.F_CUSTOM5 IS NULL OR TP.F_CUSTOM5 = '')  AND  (TP.F_SUBFORMAT IN (SELECT ITEM FROM SPLIT_NEW('EGHS,VCH,VJP,EGHS,VFR,VDE,VIR,AGHS,VUS,VCA,VCH,VJP',',')))  AND (TP.F_DATE_REVISED BETWEEN '2018-07-16 15:04:31' AND '2018-07-18 21:51:45')  AND TP.F_AUTHORIZED IN (1,3)

因此，我为所有视图创建了临时表，并从视图中插入了记录，并为临时表创建了非聚集索引，并在下面的左连接中使用了临时表。

SELECT * INTO #V_PROD_ALIAS_CASN_TEMP FROM (SELECT F_PRODUCT,F_DATA_CODE,F_DATA FROM V_PROD_ALIAS_CASN) AS A;
CREATE NONCLUSTERED INDEX IX_V_PROD_ALIAS_CASN ON #V_PROD_ALIAS_CASN_TEMP(F_PRODUCT,F_DATA_CODE,F_DATA);

SELECT * INTO #V_PROD_ALIAS_ICO_TEMP FROM (SELECT F_PRODUCT,F_DATA_CODE,F_DATA FROM  V_PROD_ALIAS_ICO) AS B;
CREATE NONCLUSTERED INDEX IX_V_PROD_ALIAS_ICO ON #V_PROD_ALIAS_ICO_TEMP(F_PRODUCT,F_DATA_CODE,F_DATA);

SELECT * INTO #V_PROD_ALIAS_MAN_TEMP FROM (SELECT F_PRODUCT,F_TEXT_CODE,F_PHRASE FROM  V_PROD_ALIAS_MAN) AS C;
CREATE NONCLUSTERED INDEX IX_V_PROD_ALIAS_MAN ON  #V_PROD_ALIAS_MAN_TEMP(F_PRODUCT,F_TEXT_CODE);

SELECT * INTO #V_PROD_ALIAS_MANU_TEMP FROM (SELECT F_PRODUCT,F_TEXT_CODE,F_PHRASE FROM  V_PROD_ALIAS_MANU) AS D;
CREATE NONCLUSTERED INDEX IX_V_PROD_ALIAS_MANU ON  #V_PROD_ALIAS_MANU_TEMP(F_PRODUCT,F_TEXT_CODE);

SELECT * INTO  #V_PROD_ALIAS_SITE_TEMP FROM (SELECT F_PRODUCT,F_TEXT_CODE,F_PHRASE FROM  V_PROD_ALIAS_SITE) AS E;
CREATE NONCLUSTERED INDEX IX_V_PROD_ALIAS_SITE ON  #V_PROD_ALIAS_SITE_TEMP(F_PRODUCT,F_TEXT_CODE);

SELECT * INTO #V_PROD_ALIAS_SYN_TEMP FROM (SELECT F_PRODUCT,F_DATA_CODE,F_DATA FROM V_PROD_ALIAS_SYN) AS F;
CREATE NONCLUSTERED INDEX IX_V_PROD_ALIAS_SYN ON #V_PROD_ALIAS_SYN_TEMP(F_PRODUCT,F_DATA_CODE,F_DATA);

在 Left JOIN 中使用临时表更改查询

    SELECT DISTINCT TP.F_PRODUCT AS ID,
        TP.F_PRODUCT_NAME AS [NAME],
        (SELECT MS.F_MSDSTYPE_DESC FROM T_MSDSTYPES MS WHERE MS.F_MSDSTYPE = TP.F_SUBFORMAT AND MS.F_FORMAT = TP.F_FORMAT) AS SFMT,
    (SELECT TL.F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) AS LANG,
        TP.F_LANGUAGE AS LANGCODE,
        TP.F_SUBFORMAT AS SFMTCODE,
        TP.F_PLANT AS PLANTCODE,
        TP.F_FORMAT AS FMTCODE,
        CONVERT(VARCHAR,TP.F_DATE_REVISED,120) AS RDATE,
        CASE
            WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') 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 T_COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5,
        MAN.F_PHRASE AS MANU,
        CASE
            WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE ''
        END + ' ' +
        CASE
            WHEN NOT(SYN.F_DATA IS NULL OR SYN.F_DATA = '') THEN SYN.F_DATA ELSE ''
        END AS SYN,
        IC.F_DATA AS ICO,
        'SDS - ' + (SELECT TL.F_LANGUAGE_NAME FROM T_LANGUAGES 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 T_PDF_MSDS TP
    LEFT JOIN #V_PROD_ALIAS_SYN_TEMP SYN
        ON TP.F_PRODUCT = SYN.F_PRODUCT
    LEFT JOIN #V_PROD_ALIAS_SITE_TEMP SIT
        ON TP.F_PRODUCT = SIT.F_PRODUCT
    LEFT JOIN #V_PROD_ALIAS_ICO_TEMP IC
        ON TP.F_PRODUCT = IC.F_PRODUCT
    LEFT JOIN #V_PROD_ALIAS_MANU_TEMP MAN
        ON TP.F_PRODUCT = MAN.F_PRODUCT
    LEFT JOIN #V_PROD_ALIAS_CASN_TEMP CASN
        ON TP.F_PRODUCT = CASN.F_PRODUCT WHERE (TP.F_CUSTOM5 IS NULL OR TP.F_CUSTOM5 = '')   
      AND (REPLACE(REPLACE(REPLACE(REPLACE(TP.F_PRODUCT_NAME,'™','|TM'),'®','|TS'),'©','|CP'),'°','|DEG')
      LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('PDF Test Documet','[','\['),'_','\_'),'™','|TM'),'®','|TS'),'©','|CP'),'°','|DEG') ESCAPE '\'
     OR REPLACE(TP.F_CUSTOM1,'^','') LIKE 'PDF Test Documet' OR REPLACE(TP.F_CUSTOM2,'^','') LIKE 'PDF Test Documet' OR REPLACE(TP.F_CUSTOM3,'^','') LIKE 'PDF Test Documet' OR SYN.F_DATA LIKE 'PDF Test Documet') 
     AND TP.F_AUTHORIZED IN (1,3) 

将视图更改为临时表并在连接中使用是正确的方法吗？它会提高性能吗？插入临时表需要一些时间。

标签： sql-server-2008viewtemp-tables