| Groups | Blog | Home |
sql server data warehouse : Optimizer not using Indexed View
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
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 -- [quoted text, click to view] "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 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 [quoted text, click to view] "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... > > 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 > > > >
[quoted text, click to view]
"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 --
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |