视图vMemberListByDate外按日期过滤极慢，求优化方案

你遇到的核心问题是：通过视图外层添加SnapshotDate = '11/1/2022'过滤时耗时26分钟，而将该条件直接写入视图底层查询仅需10秒。这确实是因为SQL Server未将外层过滤条件下推到视图底层，导致视图先扫描全量数据再过滤，而非提前缩小数据集后再进行关联计算。以下是可行的解决方案：

1. 改用内联表值函数（Inline Table-Valued Function） #

将视图改为带参数的内联表值函数，强制过滤条件嵌入底层查询，让SQL Server提前过滤数据。示例代码：

CREATE FUNCTION ML.ufnMemberListByDate(@SnapshotDate DATE)
RETURNS TABLE
AS
RETURN
(
    select
          mpi.membermpi
        , mpi.accountnumber
        , mpi.MemberKey
        , membergroupkey = m.groupkey
        , mpi.accountkey
        , cr.CreditScore
        , m.HashedSSN
        , OpenDate = mpi.AccountOpenDate
        , Closedate = mpi.AccountCloseDate
        , Tenure = DATEDIFF(MONTH, mpi.AccountOpenDate, mpi.SnapshotDate)
        , AgeIndex = db.Sort
        , mpi.SnapshotDate
        , ShareBalanceAmt = fmad.TotalShareBalance
        , cr.TotalUnsecuredBalance
        , CreditLineBalance = fmad.CreditLineLoanBalance
        , MortgageBalance = fmad.FirstMortgageLoanBalance + fmad.SecondMortgageLoanBalance
        , cr.RevolvingBalance
        , IsActive
        , fmad.LastChargeOffDate
        , fmad.TotalChargeOffCnt
    from
        EDW.Global.vFactMemberMPIDaily as mpi (nolock)
    inner join 
        EDW.Global.DimMember as m (nolock) on m.MemberKey = mpi.MemberKey
    inner join 
        EDW.Global.FactMemberAccountDaily as fmad (nolock) on fmad.AccountKey = mpi.AccountKey 
                                                           and fmad.SnapshotDate = mpi.SnapshotDate
    left join 
        EDW.Global.DimBand as db (nolock) on isnull(datediff(year, m.BirthDate, mpi.SnapshotDate), -1) between db.LowValue and db.HighValue 
                                          and db.GroupDescription = 'Age 2'
    left join 
        EDW.Global.DimCredit cr (nolock) on cr.HashedSSN = m.HashedSSN 
                                         and mpi.SnapshotDate between cr.StartDate and cr.EndDate
    where mpi.SnapshotDate = @SnapshotDate
)

调用方式：

select count(*) from ML.ufnMemberListByDate('11/1/2022')

2. 移除视图中的临时表 #

原视图底层使用into #Results临时表，这会强制SQL Server先写入全量临时数据，再对外提供查询，直接阻断了过滤条件下推。修改视图逻辑，去掉临时表直接返回结果：

CREATE OR ALTER VIEW ML.vMemberListByDate
AS
select
      mpi.membermpi
    , mpi.accountnumber
    , mpi.MemberKey
    , membergroupkey = m.groupkey
    , mpi.accountkey
    , cr.CreditScore
    , m.HashedSSN
    , OpenDate = mpi.AccountOpenDate
    , Closedate = mpi.AccountCloseDate
    , Tenure = DATEDIFF(MONTH, mpi.AccountOpenDate, mpi.SnapshotDate)
    , AgeIndex = db.Sort
    , mpi.SnapshotDate
    , ShareBalanceAmt = fmad.TotalShareBalance
    , cr.TotalUnsecuredBalance
    , CreditLineBalance = fmad.CreditLineLoanBalance
    , MortgageBalance = fmad.FirstMortgageLoanBalance + fmad.SecondMortgageLoanBalance
    , cr.RevolvingBalance
    , IsActive
    , fmad.LastChargeOffDate
    , fmad.TotalChargeOffCnt
from
    EDW.Global.vFactMemberMPIDaily as mpi (nolock)
inner join 
    EDW.Global.DimMember as m (nolock) on m.MemberKey = mpi.MemberKey
inner join 
    EDW.Global.FactMemberAccountDaily as fmad (nolock) on fmad.AccountKey = mpi.AccountKey 
                                                       and fmad.SnapshotDate = mpi.SnapshotDate
left join 
    EDW.Global.DimBand as db (nolock) on isnull(datediff(year, m.BirthDate, mpi.SnapshotDate), -1) between db.LowValue and db.HighValue 
                                      and db.GroupDescription = 'Age 2'
left join 
    EDW.Global.DimCredit cr (nolock) on cr.HashedSSN = m.HashedSSN 
                                     and mpi.SnapshotDate between cr.StartDate and cr.EndDate

修改后再执行原查询，SQL Server的查询优化器有机会自动将过滤条件下推到底层表，提升性能。

3. 优化底层表索引 #

确保核心表的过滤、关联字段存在合适索引，这是性能提升的基础：

• 给EDW.Global.vFactMemberMPIDaily和EDW.Global.FactMemberAccountDaily的SnapshotDate字段创建索引，建议包含常用关联/返回字段以避免键查找：

CREATE NONCLUSTERED INDEX IX_vFactMemberMPIDaily_SnapshotDate 
ON EDW.Global.vFactMemberMPIDaily(SnapshotDate)
INCLUDE (membermpi, accountnumber, MemberKey, accountkey, AccountOpenDate, HashedSSN)

• 检查MemberKey、AccountKey、HashedSSN等关联字段是否存在主键或非聚集索引，确保关联效率。

4. 改用存储过程（可选） #

如果需要更复杂的逻辑控制，可使用存储过程封装查询，同样提前传入过滤参数：

CREATE PROCEDURE ML.uspGetMemberListCountByDate
    @SnapshotDate DATE
AS
BEGIN
    SET NOCOUNT ON;
    select count(*)
    from (
        -- 嵌入原视图逻辑并添加过滤条件
        select
              mpi.membermpi
            , mpi.accountnumber
            , mpi.MemberKey
            , membergroupkey = m.groupkey
            , mpi.accountkey
            , cr.CreditScore
            , m.HashedSSN
            , OpenDate = mpi.AccountOpenDate
            , Closedate = mpi.AccountCloseDate
            , Tenure = DATEDIFF(MONTH, mpi.AccountOpenDate, mpi.SnapshotDate)
            , AgeIndex = db.Sort
            , mpi.SnapshotDate
            , ShareBalanceAmt = fmad.TotalShareBalance
            , cr.TotalUnsecuredBalance
            , CreditLineBalance = fmad.CreditLineLoanBalance
            , MortgageBalance = fmad.FirstMortgageLoanBalance + fmad.SecondMortgageLoanBalance
            , cr.RevolvingBalance
            , IsActive
            , fmad.LastChargeOffDate
            , fmad.TotalChargeOffCnt
        from
            EDW.Global.vFactMemberMPIDaily as mpi (nolock)
        inner join 
            EDW.Global.DimMember as m (nolock) on m.MemberKey = mpi.MemberKey
        inner join 
            EDW.Global.FactMemberAccountDaily as fmad (nolock) on fmad.AccountKey = mpi.AccountKey 
                                                               and fmad.SnapshotDate = mpi.SnapshotDate
        left join 
            EDW.Global.DimBand as db (nolock) on isnull(datediff(year, m.BirthDate, mpi.SnapshotDate), -1) between db.LowValue and db.HighValue 
                                              and db.GroupDescription = 'Age 2'
        left join 
            EDW.Global.DimCredit cr (nolock) on cr.HashedSSN = m.HashedSSN 
                                             and mpi.SnapshotDate between cr.StartDate and cr.EndDate
        where mpi.SnapshotDate = @SnapshotDate
    ) t
END

调用方式：

EXEC ML.uspGetMemberListCountByDate '11/1/2022'

内容的提问来源于stack exchange，提问作者Jon Nguyen