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_RESULTS_1
CREATE VIEW dbo.IV_FACT_DIAGNOSTIC_RESULTS_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_results a
group by a.business_unit_id, a.date_id, a.test_id
CREATE UNIQUE CLUSTERED INDEX [ivx_FACT_DIAGNOSTIC_RESULTS_1] ON
[dbo].[IV_FACT_DIAGNOSTIC_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_results 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_results 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_results 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_descI'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@.microsoft.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_RESULTS_1
> CREATE VIEW dbo.IV_FACT_DIAGNOSTIC_RESULTS_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_results a
> group by a.business_unit_id, a.date_id, a.test_id
> CREATE UNIQUE CLUSTERED INDEX [ivx_FACT_DIAGNOSTIC_RESULTS_1] ON
> [dbo].[IV_FACT_DIAGNOSTIC_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_results 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_results 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_results 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
>|||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 column
s
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@.microsoft.com...
>
>|||"Atholl" <Atholl@.discussions.microsoft.com> wrote in message
news:3E604176-E361-41C9-9FC6-4677B7DAE514@.microsoft.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
--
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment