March 8, 2013 at 5:30 pm
I created a indexed view as suggested earlier and need help with that.
Here is my indexed view.
ALTER VIEW dbo.vFact WITH SCHEMABINDING
AS
SELECT a.MinorMkt
, SUM(ISNULL(admits,0)) AS Admits
,SUM(ISNULL([days],0)) AS TotalDays
,SUM(ISNULL(ExpDays,0)) AS ExpDays
,SUM(ISNULL(OppDaysPos,0)) AS OppDaysPos
,SUM(ISNULL(readmit,0)) AS Readmits
,SUM(ISNULL(Readmit30,0)) AS Readmit30
,SUM(ISNULL(Readmit_Expected,0)) AS ReadmitExpected
,SUM(ISNULL(Amt_allowed_case,0)) AS AmtAllowedCase
,SUM(ISNULL(ER,0)) AS ER
,SUM(ISNULL(Mcg_Obs_Oneday_Count,0)) AS Mcg_Obs_Oneday_Count
,COUNT_BIG(*) AS countMkt
FROM dbo.tbl_Fact a
where [plan] IN('COM','MCR')
and adm_dt >= CONVERT(DATETIME,'01/01/2009',120) and adm_dt < CONVERT(DATETIME,'01/01/2013',120)
group by a.MinorMkt
go
CREATE UNIQUE CLUSTERED INDEX ix_Mkt on dbo.vFact(MinorMkt)
Any idea why the following is not using the indexed view - I changed the date range. If i keep the same date range as defined in the view, only then the query uses indexed view.
SELECT a.MinorMkt
, SUM(ISNULL(admits,0)) AS Admits
,SUM(ISNULL([days],0)) AS TotalDays
,SUM(ISNULL([days],0))/NULLIF(SUM(ISNULL(admits,0)),0) as 'LOS'
,SUM(ISNULL(ExpDays,0))/NULLIF(SUM(ISNULL(admits,0)),0) as 'Expected LOS'
,SUM(ISNULL([Days],0))/NULLIF(sum(ISNULL(ExpDays,0)),0) as 'LOS To Expected'
,SUM(ISNULL(OppDaysPos,0))/NULLIF(SUM(ISNULL(admits,0)),0) as 'Opport Days/Admit'
,SUM(ISNULL(readmit,0)) as 'Readmits'
,SUM(ISNULL(Readmit30,0))/NULLIF(SUM(ISNULL(admits,0)),0) as 'Readmit Rate'
,SUM(ISNULL(Readmit_Expected,0))/NULLIF(SUM(ISNULL(admits,0)),0) as 'Expected Readmit Rate'
,SUM(ISNULL(Readmit30,0))/NULLIF(sum(ISNULL(Readmit_Expected,0)),0) as 'Readmits to Expected'
,SUM(ISNULL(Amt_allowed_case,0))/NULLIF(SUM(ISNULL(admits,0)),0) as 'Allowed Per Case'
,SUM(ISNULL(ER,0))/NULLIF(SUM(ISNULL(admits,0)),0) as '% ER'
,SUM(ISNULL(Mcg_Obs_Oneday_Count,0))/NULLIF(SUM(ISNULL(admits,0)),0) as '% MCG Obs 1 Day'
,SUM(ISNULL(Mcg_Obs_Oneday_Count,0))/NULLIF(SUM(ISNULL(admits,0)),0) as '% MCG OP Surg 1 Day'
from
tbl_fact a
where [plan] IN('COM','MCR' )
and adm_dt >= CONVERT(DATETIME,'01/01/2010',120) and adm_dt < CONVERT(DATETIME,'01/01/2013',120)
group by a.MinorMkt order by a.MinorMkt
March 8, 2013 at 8:05 pm
kk1173 (3/8/2013)
Any idea why the following is not using the indexed view
You're supposed to select from the indexed view itself.
Shifting gears, I'd not make an indexed view in such a manner. You have it doing a sum of everything. Not very reusable. I'd be tempted to have it do a sum by month so that it could be used for more than one thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 9, 2013 at 6:07 am
kk1173 (3/8/2013)
I created a indexed view as suggested earlier and need help with that.Here is my indexed view.
ALTER VIEW dbo.vFact WITH SCHEMABINDINGAS
SELECT a.MinorMkt
, SUM(ISNULL(admits,0)) AS Admits
,SUM(ISNULL([days],0)) AS TotalDays
,SUM(ISNULL(ExpDays,0)) AS ExpDays
,SUM(ISNULL(OppDaysPos,0)) AS OppDaysPos
,SUM(ISNULL(readmit,0)) AS Readmits
,SUM(ISNULL(Readmit30,0)) AS Readmit30
,SUM(ISNULL(Readmit_Expected,0)) AS ReadmitExpected
,SUM(ISNULL(Amt_allowed_case,0)) AS AmtAllowedCase
,SUM(ISNULL(ER,0)) AS ER
,SUM(ISNULL(Mcg_Obs_Oneday_Count,0)) AS Mcg_Obs_Oneday_Count
,COUNT_BIG(*) AS countMkt
FROM dbo.tbl_Fact a
where [plan] IN('COM','MCR')
and adm_dt >= CONVERT(DATETIME,'01/01/2009',120) and adm_dt < CONVERT(DATETIME,'01/01/2013',120)
group by a.MinorMkt
go
CREATE UNIQUE CLUSTERED INDEX ix_Mkt on dbo.vFact(MinorMkt)
Any idea why the following is not using the indexed view - I changed the date range. If i keep the same date range as defined in the view, only then the query uses indexed view.
Your indexed view definition should also make all those columns available which are to be filtered as defined in the WHERE clause i.e. plan and adm_dt.
I would suggest that try aggregating the data for each MinorMkt, plan on day basis. Then you would be able to apply date and plan filter correctly by using the indexed view in the query. Not to mention NOEXPAND hint which is to force the usage of indexed view's indexes.
Having said that, I strongly recommend reading BOL to understand indexed views and how to design/implement them. Also, please keep in mind that indexed views are not always feasable in very busy OLTP systems.
March 12, 2013 at 3:12 pm
So I created indexed views and it is working great when data is grouped by year, month or quarter.
here is my indexed view.
SELECT
a.MinorMkt as MinorMkt
,a.plan1 AS Plan1
--,a.adm_dt
,YEAR(a.adm_dt) as YrAdmDt
,DATEPART(qq, a.adm_dt) AS QtrAdmDt
,DATEPART(mm, a.adm_dt) AS MonthAdmDt
,SUM(ISNULL(admits,0)) AS Admits
,SUM(ISNULL([days],0)) AS TotalDays
....
,COUNT_BIG(*) AS countMkt
FROM dbo.tbl_factWithHospAprMinorMkt a
WHERE
a.plan1 IN('COM','MCR')
AND adm_dt >= CONVERT(DATETIME,'01/01/2009',120) and adm_dt < CONVERT(DATETIME,'01/01/2013',120)
GROUP BY a.MinorMkt
, a.plan1
--, a.adm_dt
,YEAR(a.adm_dt)
,DATEPART(qq, a.adm_dt)
,DATEPART(mm, a.adm_dt)
GO
CREATE UNIQUE CLUSTERED INDEX IX_PX
ON vFact (
MinorMkt
, plan1
--, adm_dt
,YrAdmDt
,QtrAdmDt
,MonthAdmDt
Notice that I commented out adm_dt field. If i uncomment and add the adm_dt field, the view does not get indexed and throws an error
"..select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list."
Another way of getting this to work is to just have adm_dt field in the select list and group by clause and remove Year, month, and Quarter expressions from both select and group by clauses.
Basically if I have adm_dt and Yr/Qtr/Month together the indexed view throws the above error
I am not sure why, my goal is that the aggregates grouped by at a Year, Qtr, Month or a day basis.
Any ideas?
Thanks
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply