eu tenho essa mesa
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)
);
que atualmente é preenchido como
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
que permite a fácil conversão de valores do calendário YTD para valores não do calendário YTD.
No entanto, a tabela é uma tabela derivada, o que significa que deve ser mantida à medida que novos anos são instanciados. Gostaria de substituí-la por uma exibição indexada para eliminar a necessidade dessa manutenção.
Provei a equivalência do select acima com o abaixo, utilizando uma tabela NUMBERS, que é precisa e determinística e não utiliza subconsultas ou operadores APPLY, permitindo que seja a definição de uma Visualização Indexada:
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
Observe que o índice clusterizado único na exibição é idêntico ao (um e único) índice clusterizado na tabela original.
No entanto, várias consultas executadas na Exibição Indexada são executadas mais lentamente (em média cerca de 3*, variando até cerca de 6*, mais lentas) do que na tabela original.
Alguém sabe porque isso pode acontecer? É um possível bug no mecanismo não tratar dois índices agrupados idênticos de forma idêntica? Meus dados de teste atualmente cobrem apenas dois períodos, com um ano de intervalo.
Inicialmente, pensei que poderia ser devido às colunas da exibição serem anuláveis, mas usar isnull para agrupá-las simplesmente torna as consultas tão lentas que nem consigo medir o desempenho.
Estou no 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)
As exibições indexadas geralmente não podem ser usadas como substitutos imediatos para tabelas. A razão é que a
WITH (NOEXPAND)
dica é muitas vezes necessária. Muitas vezes, a visão é expandida (apesar do índice) e a definição subjacente é usada. Paul White escreveu sobre isso anteriormente .Alterar consultas de:
SELECT * FROM SkaData.FiscalPeriod
Para:
SELECT * FROM SkaData.FiscalPeriod WITH(NOEXPAND)