MS SQL Data Warehousing - Optimizer not using Indexed View

Author

Optimizer not using Indexed View

Atholl

2006-01-10, 7:23 am

Hi All
We are having problem on indexed views when we try to join to more than one
table that is not part of the original indexed view in a query. Has anybody
seen a similar problem?

Thanks
Atholl

See SQL examples below:

--New Indexed View - fact table grouped by three columns, no joins drop view
dbo. IV_FACT_DIAGNOSTIC_R
ESULTS_1

CREATE VIEW dbo. IV_FACT_DIAGNOSTIC_R
ESULTS_1
WITH SCHEMABINDING
AS
SELECT COUNT_BIG (*) AS Expr1
,a.business_unit_id
,a.date_id
,a.test_id
,sum(a. RECORD_COUNT_FAILED)
record_count_failed
from dbo. fact_diagnostic_resu
lts a
group by a.business_unit_id, a.date_id, a.test_id

CREATE UNIQUE CLUSTERED INDEX & #91;ivx_FACT_DIAGNOS
TIC_RESULTS_1] ON
[dbo].& #91;IV_FACT_DIAGNOST
IC_RESULTS_1]
(business_unit_id, date_id, test_id) ON [Indexes]

--WORKING - joined to 2 dimension tables, uses dimension table id columns in
group by select bu.business_unit_id
,d.date_id
,sum(a. record_count_failed)
record_count_1
from dbo. fact_diagnostic_resu
lts a
inner join dbo.lu_business_unit bu on a.business_unit_id =
bu.business_unit_id
inner join dbo.lu_date d on a.date_id=d.date_id
group by bu.business_unit_id
,d.date_id

--NOT WORKING - joined to 2 dimension tables, uses dimension table columns
in group by select bu.business_unit_srccd
,d.calendar_date_desc
,sum(a. record_count_failed)
record_count_1
from dbo. fact_diagnostic_resu
lts a
inner join dbo.lu_business_unit bu on a.business_unit_id =
bu.business_unit_id
inner join dbo.lu_date d on a.date_id=d.date_id
group by bu.business_unit_srccd
,d.calendar_date_desc

--WORKING - joined to 1 dimension table, uses dimension table columns in
group by select bu.business_unit_srccd
-- ,d.calendar_date_desc
,sum(a. record_count_failed)
record_count_1
from dbo. fact_diagnostic_resu
lts a
inner join dbo.lu_business_unit bu on a.business_unit_id =
bu.business_unit_id
-- inner join dbo.lu_date d on a.date_id=d.date_id
group by bu.business_unit_srccd
-- ,d.calendar_date_desc

Adam Machanic

2006-01-10, 1:23 pm

I'm surprised that third query is using the indexed view -- what is the
execution plan for that?

Can you add business_unit_srccd to the view?

--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--

"Atholl" <Atholl@discussions.microsoft.com> wrote in message
news:6E3F6D63-6A57-4D9F-95B8- A6774E2D858B@microso
ft.com...
> Hi All
> We are having problem on indexed views when we try to join to more than
> one
> table that is not part of the original indexed view in a query. Has
> anybody
> seen a similar problem?
>
> Thanks
> Atholl
>
> See SQL examples below:
>
> --New Indexed View - fact table grouped by three columns, no joins drop
> view
> dbo. IV_FACT_DIAGNOSTIC_R
ESULTS_1
>
> CREATE VIEW dbo. IV_FACT_DIAGNOSTIC_R
ESULTS_1
> WITH SCHEMABINDING
> AS
> SELECT COUNT_BIG (*) AS Expr1
> ,a.business_unit_id
> ,a.date_id
> ,a.test_id
> ,sum(a. RECORD_COUNT_FAILED)
record_count_failed
> from dbo. fact_diagnostic_resu
lts a
> group by a.business_unit_id, a.date_id, a.test_id
>
> CREATE UNIQUE CLUSTERED INDEX & #91;ivx_FACT_DIAGNOS
TIC_RESULTS_1] ON
> [dbo].& #91;IV_FACT_DIAGNOST
IC_RESULTS_1]
> (business_unit_id, date_id, test_id) ON [Indexes]
>
> --WORKING - joined to 2 dimension tables, uses dimension table id columns
> in
> group by select bu.business_unit_id
> ,d.date_id
> ,sum(a. record_count_failed)
record_count_1
> from dbo. fact_diagnostic_resu
lts a
> inner join dbo.lu_business_unit bu on a.business_unit_id =
> bu.business_unit_id
> inner join dbo.lu_date d on a.date_id=d.date_id
> group by bu.business_unit_id
> ,d.date_id
>
> --NOT WORKING - joined to 2 dimension tables, uses dimension table columns
> in group by select bu.business_unit_srccd
> ,d.calendar_date_desc
> ,sum(a. record_count_failed)
record_count_1
> from dbo. fact_diagnostic_resu
lts a
> inner join dbo.lu_business_unit bu on a.business_unit_id =
> bu.business_unit_id
> inner join dbo.lu_date d on a.date_id=d.date_id
> group by bu.business_unit_srccd
> ,d.calendar_date_desc
>
> --WORKING - joined to 1 dimension table, uses dimension table columns in
> group by select bu.business_unit_srccd
> -- ,d.calendar_date_desc
> ,sum(a. record_count_failed)
record_count_1
> from dbo. fact_diagnostic_resu
lts a
> inner join dbo.lu_business_unit bu on a.business_unit_id =
> bu.business_unit_id
> -- inner join dbo.lu_date d on a.date_id=d.date_id
> group by bu.business_unit_srccd
> -- ,d.calendar_date_desc
>

Atholl

2006-01-11, 3:23 am

Hi Adam

The execution plan does a Clustered Index Scan on the view; why are you
surprised that it uses the view?

Yes I could add business_unit_srccd to the view but this is just one of
about 30 columns on the business_unit table, I don't want to add them all to
the view.

The interesting thing is that it seems to work as long as I have the ID
columns in the Group By on the query. This is strange because the ID columns
come from the dimension tables and aren't even part of the view so why are
they any different from the other columns on the dimension tables?

Atholl

"Adam Machanic" wrote:

> I'm surprised that third query is using the indexed view -- what is the
> execution plan for that?
>
> Can you add business_unit_srccd to the view?
>
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>
> "Atholl" <Atholl@discussions.microsoft.com> wrote in message
> news:6E3F6D63-6A57-4D9F-95B8- A6774E2D858B@microso
ft.com...
>
>
>

Adam Machanic

2006-01-11, 11:23 am

"Atholl" <Atholl@discussions.microsoft.com> wrote in message
news:3E604176-E361-41C9-9FC6- 4677B7DAE514@microso
ft.com...
> Hi Adam
>
> The execution plan does a Clustered Index Scan on the view; why are you
> surprised that it uses the view?

Even though it's doing a scan on the view, it still needs to do some
sort of lookup operation to get the values for that missing column -- how
expensive is that? I was assuming it would be a fairly expensive operation,
but perhaps the dimension is not large?

--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--

Sponsored Links