我正在使用SQl服务器，我创建了一个从另一个视图调用数据的视图，性能非常慢 . 有没有办法提高性能？
First View是（此视图用于连接表和创建标志）
ALTER VIEW [dbo].[VW_MCCSR_FLAGS_MP] AS
SELECT TB2.*,
----FLAGS FOR CSV (GF1)
CASE WHEN TB2.PAR_UL_01 IS NOT NULL AND TB2.ROW_ITEM = 'CSV (GF1)' THEN 1 ELSE 0 END AS CSV_GF1_PAR_UL_01_FLAG,
CASE WHEN TB2.NONPAR_UL_01 IS NOT NULL AND TB2.ROW_ITEM = 'CSV (GF1)' THEN 1 ELSE 0 END AS CSV_GF1_NONPAR_UL_01_FLAG,
CASE WHEN TB2.PAR_UL_19 IS NOT NULL AND TB2.ROW_ITEM = 'CSV (GF1)' THEN 1 ELSE 0 END AS CSV_GF1_PAR_UL_19_FLAG,
CASE WHEN TB2.NONPAR_UL_19 IS NOT NULL AND TB2.ROW_ITEM = 'CSV (GF1)' THEN 1 ELSE 0 END AS CSV_GF1_NONPAR_UL_19_FLAG,
CASE WHEN TB2.VUL_19 IS NOT NULL AND TB2.ROW_ITEM = 'CSV (GF1)' THEN 1 ELSE 0 END AS CSV_GF1_VUL_19_FLAG,
CASE WHEN TB2.UL_26 IS NOT NULL AND TB2.ROW_ITEM = 'CSV (GF1)' THEN 1 ELSE 0 END AS CSV_GF1_UL_26_FLAG,
CASE WHEN TB2.UL_67 IS NOT NULL AND TB2.ROW_ITEM = 'CSV (GF1)' THEN 1 ELSE 0 END AS CSV_GF1_UL_67_FLAG,
CASE WHEN TB2.UL_89 IS NOT NULL AND TB2.ROW_ITEM = 'CSV (GF1)' THEN 1 ELSE 0 END AS CSV_GF1_UL_89_FLAG,
CASE WHEN TB2.UL_94 IS NOT NULL AND TB2.ROW_ITEM = 'CSV (GF1)' THEN 1 ELSE 0 END AS CSV_GF1_UL_94_FLAG,
CASE WHEN TB2.VUL_94 IS NOT NULL AND TB2.ROW_ITEM = 'CSV (GF1)' THEN 1 ELSE 0 END AS CSV_GF1_VUL_94_FLAG,
CASE WHEN TB2.UL_102 IS NOT NULL AND TB2.ROW_ITEM = 'CSV (GF1)' THEN 1 ELSE 0 END AS CSV_GF1_UL_102_FLAG
FROM
(
SELECT DISTINCT 'CSV' AS REPORT_NAME, 'CSV (GF1)' AS ROW_ITEM,
CASE WHEN TB1.GL_REINSURANCE_CLASS_CODE IS NULL AND TB1.GL_Legal_ID_BK = 1 AND TB1.US_SOE_Group_Code LIKE 'UL' AND TB1.Par_Code = 4
THEN SUM(TB1.Cash_Surrender_Value_Amt) END AS PAR_UL_01,
CASE WHEN TB1.GL_REINSURANCE_CLASS_CODE IS NULL AND TB1.GL_Legal_ID_BK = 1 AND TB1.US_SOE_Group_Code LIKE 'UL' AND TB1.Par_Code = 2
THEN SUM(TB1.Cash_Surrender_Value_Amt) END AS NONPAR_UL_01,
CASE WHEN TB1.GL_REINSURANCE_CLASS_CODE IS NULL AND TB1.GL_Legal_ID_BK = 19 AND TB1.US_SOE_Group_Code LIKE 'UL' AND TB1.Par_Code = 4
THEN SUM(TB1.Cash_Surrender_Value_Amt) END AS PAR_UL_19,
CASE WHEN TB1.GL_REINSURANCE_CLASS_CODE IS NULL AND TB1.GL_Legal_ID_BK = 19 AND TB1.US_SOE_Group_Code LIKE 'UL' AND TB1.Par_Code = 2
THEN SUM(TB1.Cash_Surrender_Value_Amt) END AS NONPAR_UL_19,
CASE WHEN TB1.GL_REINSURANCE_CLASS_CODE IS NULL AND TB1.GL_Legal_ID_BK = 19 AND TB1.US_SOE_Group_Code LIKE 'VUL' AND TB1.Par_Code = 2
THEN (SUM(TB1.Cash_Surrender_Value_Amt) - SUM(TB1.SEPERATE_ACCOUNT_VALUE)) END AS VUL_19,
CASE WHEN TB1.GL_REINSURANCE_CLASS_CODE IS NULL AND TB1.GL_Legal_ID_BK = 26 AND TB1.US_SOE_Group_Code LIKE 'UL' AND TB1.Par_Code = 2
THEN 0 END AS UL_26,
CASE WHEN TB1.GL_REINSURANCE_CLASS_CODE IS NULL AND TB1.GL_Legal_ID_BK = 94 AND TB1.US_SOE_Group_Code LIKE 'UL' AND TB1.Par_Code = 2
THEN (SUM(TB1.Cash_Surrender_Value_Amt) / 0.09) END AS UL_67,
CASE WHEN TB1.GL_REINSURANCE_CLASS_CODE IS NULL AND TB1.GL_Legal_ID_BK = 89 AND TB1.US_SOE_Group_Code LIKE 'UL' AND TB1.Par_Code = 2
THEN SUM(TB1.Cash_Surrender_Value_Amt) END AS UL_89,
CASE WHEN TB1.GL_REINSURANCE_CLASS_CODE IS NULL AND TB1.GL_Legal_ID_BK = 94 AND TB1.US_SOE_Group_Code LIKE 'UL' AND TB1.Par_Code = 2
THEN SUM(TB1.Cash_Surrender_Value_Amt) END AS UL_94,
CASE WHEN TB1.GL_REINSURANCE_CLASS_CODE IS NULL AND TB1.GL_Legal_ID_BK = 94 AND TB1.US_SOE_Group_Code LIKE 'VUL' AND TB1.Par_Code = 2
THEN (SUM(TB1.Cash_Surrender_Value_Amt) - SUM(TB1.SEPERATE_ACCOUNT_VALUE)) END AS VUL_94,
CASE WHEN TB1.GL_REINSURANCE_CLASS_CODE IS NULL AND TB1.GL_Legal_ID_BK = 102 AND TB1.US_SOE_Group_Code LIKE 'UL' AND TB1.Par_Code = 2
THEN SUM(TB1.Cash_Surrender_Value_Amt) END AS UL_102
FROM
(SELECT distinct PF.POLICY_SID, PF.Cash_Surrender_Value_Amt ,CAST(PD.GL_Legal_ID_BK AS INT) AS GL_Legal_ID_BK,PD.US_SOE_Group_Code , CAST(PD.Par_Code AS INT) AS Par_Code
,CAST(CD.GL_REINSURANCE_CLASS_CODE AS INT) AS GL_REINSURANCE_CLASS_CODE
, TBL1.FUND_BALANCE_AMT AS SEPERATE_ACCOUNT_VALUE
FROM POLICY_FACT PF
INNER JOIN Policy_DIM PD ON PF.Policy_SID = PD.Policy_SID
LEFT OUTER JOIN (SELECT DISTINCT crf.policy_sid , crf.coverage_sid FROM dbo.Coverage_rider_fact crf
WHERE CRF.Reporting_period_sid = (SELECT b.DATE_SID
FROM
[VSTPODS_US].[dbo].[REF_EXTRACT_MONTH] A
INNER JOIN
[dbo].date_dim b
ON
SUBSTRING(CAST(b.date_sid AS VARCHAR(8)),1,6) = A.EXTRACT_MONTH
WHERE A.CURR_IND=1 AND B.Last_Day_of_the_Month_Ind = 1)
) CURF
ON PD.Policy_SID = CURF.POLICY_SID
LEFT OUTER JOIN COVERAGE_DIM CD ON CURF.Coverage_SID = CD.Coverage_SID
LEFT OUTER JOIN ( SELECT FF.policy_sid, FF.FUND_BALANCE_AMT,FD.Fund_Account_Type_Code FROM
dbo.Fund_Account_Fact FF
INNER JOIN dbo.Fund_Account_dim FD ON FF.Fund_Account_sid = FD.Fund_Account_sid
WHERE FD.Fund_Account_Type_Code = 'S' AND
FF.Reporting_Period_SID = (SELECT b.DATE_SID
FROM
[VSTPODS_US].[dbo].[REF_EXTRACT_MONTH] A
INNER JOIN
[dbo].date_dim b
ON
SUBSTRING(CAST(b.date_sid AS VARCHAR(8)),1,6) = A.EXTRACT_MONTH
WHERE A.CURR_IND=1 AND B.Last_Day_of_the_Month_Ind = 1)
) TBL1 ON TBL1.Policy_sid = PF.Policy_SID
WHERE PF.Reporting_Period_SID = (SELECT b.DATE_SID
FROM
[VSTPODS_US].[dbo].[REF_EXTRACT_MONTH] A
INNER JOIN
[dbo].date_dim b
ON
SUBSTRING(CAST(b.date_sid AS VARCHAR(8)),1,6) = A.EXTRACT_MONTH
WHERE A.CURR_IND=1 AND B.Last_Day_of_the_Month_Ind = 1)) AS TB1
GROUP BY TB1.GL_REINSURANCE_CLASS_CODE,TB1.GL_Legal_ID_BK,TB1.US_SOE_Group_Code, TB1.Par_Code ) TB2
此视图的输出就像这样
和调用视图的脚本是
ALTER VIEW [dbo].[VW_MCCSR_USNT_OUTPUT_MP] AS
SELECT DISTINCT V1.REPORT_NAME, V1.ROW_ITEM,
(SELECT PAR_UL_01 FROM VW_MCCSR_FLAGS_MP WHERE CSV_GF1_PAR_UL_01_FLAG = 1) AS PAR_UL_01,
(SELECT NONPAR_UL_01 FROM VW_MCCSR_FLAGS_MP WHERE CSV_GF1_NONPAR_UL_01_FLAG = 1 ) AS NONPAR_UL_01,
(SELECT PAR_UL_19 FROM dbo.VW_MCCSR_FLAGS_MP WHERE CSV_GF1_PAR_UL_19_FLAG IS NOT NULL ) AS PAR_UL_19,
(SELECT NONPAR_UL_19 FROM VW_MCCSR_FLAGS_MP WHERE CSV_GF1_NONPAR_UL_19_FLAG = 1 ) AS NONPAR_UL_19,
(SELECT VUL_19 FROM VW_MCCSR_FLAGS_MP WHERE CSV_GF1_VUL_19_FLAG = 1 ) AS VUL_19,
(SELECT UL_26 FROM VW_MCCSR_FLAGS_MP WHERE CSV_GF1_UL_26_FLAG = 1 ) AS UL_26,
(SELECT UL_67 FROM VW_MCCSR_FLAGS_MP WHERE CSV_GF1_UL_67_FLAG = 1 ) AS UL_67,
(SELECT UL_89 FROM VW_MCCSR_FLAGS_MP WHERE CSV_GF1_UL_89_FLAG = 1 ) AS UL_89,
(SELECT UL_94 FROM VW_MCCSR_FLAGS_MP WHERE CSV_GF1_UL_94_FLAG = 1 ) AS UL_94,
(SELECT VUL_94 FROM VW_MCCSR_FLAGS_MP WHERE CSV_GF1_VUL_94_FLAG = 1 ) AS VUL_94 ,
(SELECT UL_102 FROM VW_MCCSR_FLAGS_MP WHERE CSV_GF1_UL_102_FLAG = 1 ) AS UL_102
FROM
VW_MCCSR_FLAGS_MP V1
WHERE V1.ROW_ITEM = 'CSV (GF1)'
1 回答
不是积极的，但看看这样的事情