Sql-server – Why would Indexed View perform much worse than equivalent table

materialized-viewsql serversql server 2014

I have this table

create table SkaData.FiscalPeriod(
     PeriodNo               int     not null constraint FiscalPeriod_FK_BasePeriod    references SkaData.Period(Number)
    ,YearEndMonthNo         int     not null constraint Fiscalperiod_CK_YearEnd       check (YearEndMonthNo between 1 and 12)
    ,ContributionType       char(4) not null constraint FiscalPeriod_CK_ContribType   check(ContributionType in ('Base','Cr','Dr'))

    ,ContributionSign       int     not null constraint FiscalPeriod_CK_ContribSign   check(ContributionSign in (+1,-1))
    ,ContributionPeriodNo   int     not null constraint FiscalPeriod_FK_ContribPeriod references SkaData.Period(Number)

    ,constraint FiscalPeriod_PK unique clustered (PeriodNo,YearEndMonthNo,ContributionType)
);

which is currently populated as

    insert Skadata.FiscalPeriod(
        PeriodNo,YearEndMonthNo,ContributionType,ContributionperiodNo,ContributionSign)
        select
             data.PeriodNo
            ,YearEndMonthNo
            ,ContributionType
            ,ContributionPeriodNo   = pvt.PeriodNo
            ,ContributionSign       = pvt.Sign
        from (
            select 
                 YearEndMonthNo = N
                ,PeriodNo       = Period.Number
                ,CreditPeriodNo = Period.Number - MonthNo
                ,DebitPeriodNo  = Period.Number - MonthNo + N - 12
            from SkaData.Period
            join (select * from 
                (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12) )Number(N)
            )Number(N)  on Number.N >= MonthNo + 12 - Period.Number
        ) data
        cross apply (values
             (PeriodNo,         +1, 0,  'Base')
            ,(CreditPeriodNo,   +1, 12, 'Cr')
            ,(DebitPeriodNo,    -1, 12, 'Dr')
        ) pvt(PeriodNo, Sign, TestPeriod,ContributionType)
        where YearEndMonthNo   <> pvt.TestPeriod

which allows the easy conversion from Calendar YTD values to non-Calendar YTD values.

However the table is a derived table, meaning that it must be maintained as new years are instantiated. I would like to replace it with an Indexed View to eliminate the need for this maintenance.

I have proved the equivalence of the select above with that below, using a NUMBERS table, which is precise and deterministic and uses no sub-queries or APPLY operators, allowing it to be the definition of an Indexed View:

create view SkaData.FiscalPeriod with schemabinding as
    select
         PeriodNo               = cast(Period.Number as int)
        ,YearEndMonthNo         = cast(months.N/3 + 1 as int)
        ,ContributionType       = cast(case months.N%3 when 0 then 'Base'
                                                       when 1 then 'Cr'
                                                       when 2 then 'Dr'
                                  end as char(4))
        ,ContributionPeriodNo   = cast(case months.N%3 when 0 then Period.Number
                                                       when 1 then Period.Number - MonthNo
                                                       when 2 then Period.Number - MonthNo + (months.N/3) + 1 - 12
                                  end as int)
        ,ContributionSign       = cast(case months.N%3 when 0 then +1
                                                       when 1 then +1
                                                       when 2 then -1
                                  end as int)
    from SkaData.Period
    join SkaData.Number months   on (months.N/3) + 1 >= MonthNo + 12 - Period.Number
                                and months.N between 0 and 33
go

create unique clustered index FiscalPeriod_PK on SkaData.FiscalPeriod(PeriodNo,YearEndMonthNo,ContributionType);
go

Note that the single clustered index on the view is identical to the (one and only) clustered index on the original table.

However, several queries running against the Indexed View run slower (averaging about 3*, ranging up to about 6*, slower) than against the original table.

Does anyone know why this could happen? Is it a possible bug in the Engine to not treat two identical clustered indices identically? My test data currently covers only two periods, one year apart.

I initially thought it might be due to the columns of the view being nullable, but using isnull to coalesce them simply makes the queries so slow I can't even measure the performance.

I am on SQL Server 2014:

Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) - 12.0.5207.0 (Intel X86) 
    Jul  3 2017 02:37:05 
    Copyright (c) Microsoft Corporation
    Developer Edition on Windows NT 6.1 <X64> (Build 7601: ) (WOW64)

Best Answer

Indexed views often can't be used as drop-in replacements for tables. The reason is that the WITH (NOEXPAND) hint is often needed. Often, the view is expanded (despite the index) and the underlying definition is used. Paul White has written about it previously.

Change queries from: SELECT * FROM SkaData.FiscalPeriod

To: SELECT * FROM SkaData.FiscalPeriod WITH(NOEXPAND)