lestatf4 (1/23/2013)
Here is the script, is should return a years worth of data from @month and @year.Surely the column order in the query is irrelevant to the optimizer?
create view withClusteredIdx
with schemabinding
as
select c.Reference, mc.MPAN,ElementID,MONTH(hhdatetime) mm,YEAR(hhdatetime) yy
from CONTRACTS.MPAN_Contract mc
join CONTRACTS.ContractElementUnitInstance ui
on mc.ContractID = ui.ContractID
join CONTRACTS.Contract c
on ui.ContractID = c.id
and ui.ElementID in (2,3,4)
join CONTRACTS.CalendarAllHH cal
on cal.HHDatetime between ui.Startdate and ui.Enddate
and cal.HHDatetime between mc.startdate and mc.enddate
group by c.Reference, mc.MPAN,ElementID,MONTH(hhdatetime),YEAR(hhdatetime)
CREATE UNIQUE CLUSTERED INDEX [idxTest] ON [dbo].[withClusteredIdx]
(
[ElementID] ASC,
[mm] ASC,
[MPAN] ASC,
[Reference] ASC,
[yy] ASC
) go
select Reference,mpan
from mpanContractMMyyCache
where yy between @YEAR and @YEAR + 1
and ((mm >= @MONTH and yy = @YEAR) or (mm <= @MONTH and yy = @YEAR + 1))
Have you tried the following in the from part of your query:
from mpanContractMMyyCache with (NOEXPAND)
This may force the use of your clustered index on the schemabound view.