Sql server CASE表达式列索引

下面的查询运行时间太长 . 我怀疑我的低效率是因为我在子查询中使用CASE条件的方式 .

请帮助正确索引这些表以使此查询有效

主要事实表包含大约6000万行

这就是IO统计数据显示的结果

表'Dim_Dealership' . 扫描计数4，逻辑读取766，物理读取0，预读取读取0，lob逻辑读取0，lob物理读取0，lob预读取读取0.表'Dim_Date' . 扫描计数1，逻辑读取2，物理读取0，预读取读取0，lob逻辑读取0，lob物理读取0，lob预读读取0 .

表'Fact_Member_Accruals' . 扫描计数3，逻辑读取521，物理读取0，预读取读取0，lob逻辑读取0，lob物理读取0，lob预读读取0 .

表'Dim_Member' . 扫描计数58617，逻辑读取249549，物理读取0，预读读取0，lob逻辑读取0，lob物理读取0，lob预读读取0 .

表'工作文件' . 扫描计数64，逻辑读取2424，物理读取182，预读读取2298，lob逻辑读取0，lob物理读取0，lob预读读取0 .

表'工作台' . 扫描计数81，逻辑读取401006，物理读取0，预读读取0，lob逻辑读取0，lob物理读取0，lob预读读取0 .

表'工作台' . 扫描计数0，逻辑读取0，物理读取0，预读取读取0，lob逻辑读取0，lob物理读取0，lob预读读取0 .

SELECT 
    A_Code
    ,P_A_Code
    ,Region_Code
    ,Duration_Wave
    ,Country
    ,DateRollupLevel
    ,DateRollupValue

    --Enrollement Penetration Data 
    ,Enp_Num
    ,Enp_Den
    ,ISNULL(CAST(Enp_Num AS FLOAT)/NULLIF(Enp_Den,0), 0) AS Enp_Rate

    ,Enp_Num_Auto_Nation
    ,Enp_Den_Auto_Nation
    ,ISNULL(CAST(Enp_Num_Auto_Nation AS FLOAT)/NULLIF(Enp_Den_Auto_Nation,0), 0) AS Enp_Rate_Auto_Nation

    ,Enp_Num_QuickLane
    ,Enp_Den_QuickLane
    ,ISNULL(CAST(Enp_Num_QuickLane AS FLOAT)/NULLIF(Enp_Den_QuickLane,0), 0) AS Enp_Rate_QuickLane

    ,Enp_Num_Top_Tier_Flag
    ,Enp_Den_Top_Tier_Flag
    ,ISNULL(CAST(Enp_Num_Top_Tier_Flag AS FLOAT)/NULLIF(Enp_Den_Top_Tier_Flag,0), 0) AS Enp_Rate_Top_Tier_Flag

    --Enrollement Capture Data 
    ,Enc_Num
    ,Enc_Den
    ,ISNULL(CAST(Enc_Num AS FLOAT)/NULLIF(Enc_Den,0), 0) AS Enc_Rate

    ,Enc_Num_Auto_Nation
    ,Enc_Den_Auto_Nation
    ,ISNULL(CAST(Enc_Num_Auto_Nation AS FLOAT)/NULLIF(Enc_Den_Auto_Nation,0), 0) AS Enc_Rate_Auto_Nation

    ,Enc_Num_QuickLane
    ,Enc_Den_QuickLane
    ,ISNULL(CAST(Enc_Num_QuickLane AS FLOAT)/NULLIF(Enc_Den_QuickLane,0), 0) AS Enc_Rate_QuickLane

    ,Enc_Num_Top_Tier_Flag
    ,Enc_Den_Top_Tier_Flag
    ,ISNULL(CAST(Enc_Num_Top_Tier_Flag AS FLOAT)/NULLIF(Enc_Den_Top_Tier_Flag,0), 0) AS Enc_Rate_Top_Tier_Flag      
FROM
(
SELECT
DD.A_Code
,DD.P_A_Code
,DD.Region_Code
,DD.Duration_Wave
,DD.Country
,'Year' AS DateRollupLevel
,'2013-01-01' as DateRollupValue


--Enrollement Penetration Data (Enp)
,COUNT(DISTINCT (CASE WHEN FSE.IsNumeratorEnrollmentPenetration = 1  THEN  DM.Member_registration_ID END)) AS Enp_Num
,COUNT(DISTINCT (CASE WHEN FSE.IsDenominatorEnrollmentPenetration = 1  THEN  DM.Member_registration_ID END)) AS Enp_Den

--AutoNation = 'Yes'
,COUNT(DISTINCT (CASE WHEN FSE.IsNumeratorEnrollmentPenetration = 1 AND DD.AutoNation_Flag = 'Yes' THEN  DM.Member_registration_ID END)) AS Enp_Num_Auto_Nation
,COUNT(DISTINCT (CASE WHEN FSE.IsDenominatorEnrollmentPenetration = 1  AND DD.AutoNation_Flag = 'Yes' THEN  DM.Member_registration_ID END)) AS Enp_Den_Auto_Nation

--QuickLane = 'Yes'
,COUNT(DISTINCT (CASE WHEN FSE.IsNumeratorEnrollmentPenetration = 1 AND DD.Quick_Lane_Enroll_Flag = 'Yes' THEN  DM.Member_registration_ID END)) AS Enp_Num_QuickLane
,COUNT(DISTINCT (CASE WHEN FSE.IsDenominatorEnrollmentPenetration = 1  AND DD.Quick_Lane_Enroll_Flag = 'Yes' THEN  DM.Member_registration_ID END)) AS Enp_Den_QuickLane

--Top_Tier_Flag = 'Yes'
,COUNT(DISTINCT (CASE WHEN FSE.IsNumeratorEnrollmentPenetration = 1 AND DD.Top_Tier_Flag = 'Yes' THEN  DM.Member_registration_ID END)) AS Enp_Num_Top_Tier_Flag
,COUNT(DISTINCT (CASE WHEN FSE.IsDenominatorEnrollmentPenetration = 1  AND DD.Top_Tier_Flag = 'Yes' THEN  DM.Member_registration_ID END)) AS Enp_Den_Top_Tier_Flag


--  Enrollement Capture Data (Enc)

,COUNT(DISTINCT (CASE WHEN IsNumeratorEnrollmentCapture  = 1  THEN  DM.Member_registration_ID END)) AS Enc_Num
,COUNT(DISTINCT (CASE WHEN IsDenominatorEnrollmentCapture  = 1  THEN  DM.Member_registration_ID END)) AS Enc_Den

--AutoNation = 'Yes'
,COUNT(DISTINCT (CASE WHEN IsNumeratorEnrollmentCapture = 1 AND DD.AutoNation_Flag = 'Yes' THEN  DM.Member_registration_ID END)) AS Enc_Num_Auto_Nation
,COUNT(DISTINCT (CASE WHEN IsDenominatorEnrollmentCapture = 1  AND DD.AutoNation_Flag = 'Yes' THEN  DM.Member_registration_ID END)) AS Enc_Den_Auto_Nation

--QuickLane = 'Yes'
,COUNT(DISTINCT (CASE WHEN IsNumeratorEnrollmentCapture = 1 AND DD.Quick_Lane_Enroll_Flag = 'Yes' THEN  DM.Member_registration_ID END)) AS Enc_Num_QuickLane
,COUNT(DISTINCT (CASE WHEN IsDenominatorEnrollmentCapture = 1  AND DD.Quick_Lane_Enroll_Flag = 'Yes' THEN  DM.Member_registration_ID END)) AS Enc_Den_QuickLane

--Top_Tier_Flag = 'Yes'
,COUNT(DISTINCT (CASE WHEN IsNumeratorEnrollmentCapture = 1 AND DD.Top_Tier_Flag = 'Yes' THEN  DM.Member_registration_ID END)) AS Enc_Num_Top_Tier_Flag
,COUNT(DISTINCT (CASE WHEN IsDenominatorEnrollmentCapture = 1  AND DD.Top_Tier_Flag = 'Yes' THEN  DM.Member_registration_ID END)) AS Enc_Den_Top_Tier_Flag

FROM dbo.Fact_Member_Accruals FSE WITH (NOLOCK)
INNER JOIN dbo.Dim_Dealership DD ON FSE.Dim_Dealership_SK = DD.Dim_Dealership_SK
INNER JOIN dbo.Dim_Member DM WITH (NOLOCK) ON FSE.Dim_UDB_Customer_RepairOrder_SK = DM.Dim_Member_SK
INNER JOIN dbo.Dim_Date DT WITH (NOLOCK) ON Dim_RO_Closed_Date_SK = DT.Dim_Date_SK
WHERE 0 = 0

AND DT.Cal_Date BETWEEN '2013-01-01' AND '2015-03-22'
AND DD.Launched_Date <= '2015-03-22'
GROUP BY 
        GROUPING SETS((DD.A_Code),(P_A_Code), (Region_Code), (Duration_Wave),(DD.Country))
)Mbrs