lestatf4 (1/23/2013)
select c.Reference, mc.MPAN,ElementID,MONTH(hhdatetime) mm,YEAR(hhdatetime) yyfrom 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)
Do you have appropriate indxes on all tables (ON and AND clause involve ) in above query ?
Have you seen the exec plan of this query too ? also there is less chance that there would be optimal use of indexes as LEFT based column "ElementID" is not involve on your view Where clause.
There will be scan, NO seek ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)