Cannot join more than one table to an indexed view

We are running SQL2000 sp3 (SP4 on our dev environment) as a data warehouse
solution. We have decided to use indexed views for performance optimization.
The problem we are having is that if I create an indexed view on one of my
fact tables with a clustered index, the query optimizer does not use the
indexed view if I my query joins my fact table to more than one dimension
table, even though the columns used to join on are in my group by. Is this a
limitation of Indexed views or is there sometihig else I need to setup to get
this to work correctly?Richard,
For the indexed view, did you configure the constraint? Are you using the
Enterprise Edition of SQL? The constraint is what tells the optimizer only
consider one or more partitions. Posting the DDL will help the group assist
you.
"Richard Greyling" <Richard Greyling@.discussions.microsoft.com> wrote in
message news:DC07E76B-671C-4EE9-9C20-56D1F6F0DD02@.microsoft.com...
> We are running SQL2000 sp3 (SP4 on our dev environment) as a data
> warehouse
> solution. We have decided to use indexed views for performance
> optimization.
> The problem we are having is that if I create an indexed view on one of
> my
> fact tables with a clustered index, the query optimizer does not use the
> indexed view if I my query joins my fact table to more than one dimension
> table, even though the columns used to join on are in my group by. Is this
> a
> limitation of Indexed views or is there sometihig else I need to setup to
> get
> this to work correctly?|||I assume your SQL 2000 SP3 server is Enterprise Edition? Are you getting
the same behavior in both environments?
Please post the DDL for the tables and your query. Without these details,
it's hard to say what the reason might be. The plan can affected by
hardware, statistics and service pack level. If you haven't already done
so, try updating stats with fullscan to make sure the optimizer has the
correct info.
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Greyling" <Richard Greyling@.discussions.microsoft.com> wrote in
message news:DC07E76B-671C-4EE9-9C20-56D1F6F0DD02@.microsoft.com...
> We are running SQL2000 sp3 (SP4 on our dev environment) as a data
> warehouse
> solution. We have decided to use indexed views for performance
> optimization.
> The problem we are having is that if I create an indexed view on one of
> my
> fact tables with a clustered index, the query optimizer does not use the
> indexed view if I my query joins my fact table to more than one dimension
> table, even though the columns used to join on are in my group by. Is this
> a
> limitation of Indexed views or is there sometihig else I need to setup to
> get
> this to work correctly?|||Hi
Yes I am using the enterprise edition, but I haven't configured any
constraints
"Danny" wrote:
> Richard,
> For the indexed view, did you configure the constraint? Are you using the
> Enterprise Edition of SQL? The constraint is what tells the optimizer only
> consider one or more partitions. Posting the DDL will help the group assist
> you.
>
> "Richard Greyling" <Richard Greyling@.discussions.microsoft.com> wrote in
> message news:DC07E76B-671C-4EE9-9C20-56D1F6F0DD02@.microsoft.com...
> > We are running SQL2000 sp3 (SP4 on our dev environment) as a data
> > warehouse
> > solution. We have decided to use indexed views for performance
> > optimization.
> > The problem we are having is that if I create an indexed view on one of
> > my
> > fact tables with a clustered index, the query optimizer does not use the
> > indexed view if I my query joins my fact table to more than one dimension
> > table, even though the columns used to join on are in my group by. Is this
> > a
> > limitation of Indexed views or is there sometihig else I need to setup to
> > get
> > this to work correctly?
>
>|||Hi Dan
Thanks, Yes and yes, I will post the DDL in a short while
"Dan Guzman" wrote:
> I assume your SQL 2000 SP3 server is Enterprise Edition? Are you getting
> the same behavior in both environments?
> Please post the DDL for the tables and your query. Without these details,
> it's hard to say what the reason might be. The plan can affected by
> hardware, statistics and service pack level. If you haven't already done
> so, try updating stats with fullscan to make sure the optimizer has the
> correct info.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Richard Greyling" <Richard Greyling@.discussions.microsoft.com> wrote in
> message news:DC07E76B-671C-4EE9-9C20-56D1F6F0DD02@.microsoft.com...
> > We are running SQL2000 sp3 (SP4 on our dev environment) as a data
> > warehouse
> > solution. We have decided to use indexed views for performance
> > optimization.
> > The problem we are having is that if I create an indexed view on one of
> > my
> > fact tables with a clustered index, the query optimizer does not use the
> > indexed view if I my query joins my fact table to more than one dimension
> > table, even though the columns used to join on are in my group by. Is this
> > a
> > limitation of Indexed views or is there sometihig else I need to setup to
> > get
> > this to work correctly?
>
>|||Here is the DDL for my Fact Table and the View:
-- Script for FACT_AR_AGEING
CREATE TABLE [dbo].[FACT_AR_AGEING] (
[COMPANY_ID] [int] NOT NULL ,
[DATE_UPDATED_ID] [int] NOT NULL ,
[BUSINESS_UNIT_ID] [int] NOT NULL ,
[ADDRESS_BOOK_ID] [int] NOT NULL ,
[AR_INVOICE_ID] [int] NOT NULL ,
[INV_DOC_TYPE_ID] [int] NOT NULL ,
[AMOUNT_OPEN] [decimal](15, 2) NOT NULL ,
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[FACT_AR_AGEING] WITH NOCHECK ADD
CONSTRAINT [PK__FACT_AR_AGEING__05257EFE] PRIMARY KEY CLUSTERED
(
[COMPANY_ID],
[DATE_UPDATED_ID],
[BUSINESS_UNIT_ID],
[ADDRESS_BOOK_ID],
[AR_INVOICE_ID],
[INV_DOC_TYPE_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[FACT_AR_AGEING] ADD
CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_CREDIT_LIMIT] DEFAULT (0) FOR
[AMOUNT_CREDIT_LIMIT],
CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_OPEN] DEFAULT (0) FOR [AMOUNT_OPEN]
GO
CREATE INDEX [FACT_AR_AGEING29] ON [dbo].[FACT_AR_AGEING]([COMPANY_ID],
[DATE_UPDATED_ID], [BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID], [AR_INVOICE_ID],
[INV_DOC_TYPE_ID], [WEIGHTED_DAYS_INVOICE]) ON [INDEXES]
GO
-- Script for Indexed View that aggregates to Fiscal period.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.IV_test
WITH SCHEMABINDING
AS
SELECT A1.BUSINESS_UNIT_ID, A1.ADDRESS_BOOK_ID, A2.FISCAL_PERIOD_ID,
SUM(A1.AMOUNT_OPEN) AS Amount, COUNT_BIG (*) AS [Count_Big]
FROM dbo.FACT_AR_AGEING A1 INNER JOIN
dbo.LU_DATE A2 ON A1.DATE_UPDATED_ID = A2.DATE_ID
GROUP BY A1.BUSINESS_UNIT_ID, A2.FISCAL_PERIOD_ID, A1.ADDRESS_BOOK_ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
set
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS on
GO
set NUMERIC_ROUNDABORT off
GO
CREATE UNIQUE CLUSTERED INDEX [IX_Main] ON
[dbo].[IV_test]([BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID] DESC ,
[FISCAL_PERIOD_ID]) ON [PRIMARY]
GO
set NUMERIC_ROUNDABORT off set arithabort OFF
GO
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO
-- the following query hits the Indexed view
SELECT FACT_AR_AGEING.BUSINESS_UNIT_ID, FACT_AR_AGEING.ADDRESS_BOOK_ID,
LU_DATE.FISCAL_PERIOD_ID,
SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount
FROM FACT_AR_AGEING INNER JOIN
LU_DATE ON FACT_AR_AGEING.DATE_UPDATED_ID =LU_DATE.DATE_ID
GROUP BY FACT_AR_AGEING.BUSINESS_UNIT_ID, FACT_AR_AGEING.ADDRESS_BOOK_ID,
LU_DATE.FISCAL_PERIOD_ID
-- however if I join to a second table in the query the indexed view is
ignored
SELECT FACT_AR_AGEING.BUSINESS_UNIT_ID, FACT_AR_AGEING.ADDRESS_BOOK_ID,
LU_DATE.FISCAL_PERIOD_ID,
SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount
FROM FACT_AR_AGEING INNER JOIN
LU_DATE ON FACT_AR_AGEING.DATE_UPDATED_ID =LU_DATE.DATE_ID INNER JOIN
LU_BUSINESS_UNIT ON FACT_AR_AGEING.BUSINESS_UNIT_ID =LU_BUSINESS_UNIT.BUSINESS_UNIT_ID
GROUP BY FACT_AR_AGEING.BUSINESS_UNIT_ID, FACT_AR_AGEING.ADDRESS_BOOK_ID,
LU_DATE.FISCAL_PERIOD_ID
What I have noticed is that this only occurs if the View has a Join defined,
If I remove the join inside the View The problem doesn't exist
-- View definition without a join
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.IV_test
WITH SCHEMABINDING
AS
SELECT BUSINESS_UNIT_ID, ADDRESS_BOOK_ID, SUM(AMOUNT_OPEN) AS Amount,
COUNT_BIG (*) AS [Count_Big]
FROM dbo.FACT_AR_AGEING A1
GROUP BY BUSINESS_UNIT_ID, ADDRESS_BOOK_ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
set
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS on
GO
set NUMERIC_ROUNDABORT off
GO
CREATE UNIQUE CLUSTERED INDEX [IX_IV_test] ON
[dbo].[IV_test]([BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID]) ON [PRIMARY]
GO
set NUMERIC_ROUNDABORT off set arithabort OFF
GO
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO
-- Query with multiple table joins
SELECT FACT_AR_AGEING.BUSINESS_UNIT_ID,
LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD, FACT_AR_AGEING.ADDRESS_BOOK_ID,
SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount,
LU_ADDRESS_BOOK.A_ADDRESS_BOOK_SRCCD
FROM FACT_AR_AGEING INNER JOIN
LU_BUSINESS_UNIT ON FACT_AR_AGEING.BUSINESS_UNIT_ID =LU_BUSINESS_UNIT.BUSINESS_UNIT_ID INNER JOIN
LU_ADDRESS_BOOK ON FACT_AR_AGEING.ADDRESS_BOOK_ID =LU_ADDRESS_BOOK.ADDRESS_BOOK_ID
GROUP BY FACT_AR_AGEING.BUSINESS_UNIT_ID,
LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD, FACT_AR_AGEING.ADDRESS_BOOK_ID,
LU_ADDRESS_BOOK.A_ADDRESS_BOOK_SRCCD
"Dan Guzman" wrote:
> I assume your SQL 2000 SP3 server is Enterprise Edition? Are you getting
> the same behavior in both environments?
> Please post the DDL for the tables and your query. Without these details,
> it's hard to say what the reason might be. The plan can affected by
> hardware, statistics and service pack level. If you haven't already done
> so, try updating stats with fullscan to make sure the optimizer has the
> correct info.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Richard Greyling" <Richard Greyling@.discussions.microsoft.com> wrote in
> message news:DC07E76B-671C-4EE9-9C20-56D1F6F0DD02@.microsoft.com...
> > We are running SQL2000 sp3 (SP4 on our dev environment) as a data
> > warehouse
> > solution. We have decided to use indexed views for performance
> > optimization.
> > The problem we are having is that if I create an indexed view on one of
> > my
> > fact tables with a clustered index, the query optimizer does not use the
> > indexed view if I my query joins my fact table to more than one dimension
> > table, even though the columns used to join on are in my group by. Is this
> > a
> > limitation of Indexed views or is there sometihig else I need to setup to
> > get
> > this to work correctly?
>
>|||Thanks for the DDL but it looks line the LU_DATE table is missing, I also
get errors due to the missing columns referenced by the default constraints
but those probably aren't relevant for this problem.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote in
message news:326AFFB3-BA04-478B-B19D-A88ED4EF7BFF@.microsoft.com...
> Here is the DDL for my Fact Table and the View:
> -- Script for FACT_AR_AGEING
> CREATE TABLE [dbo].[FACT_AR_AGEING] (
> [COMPANY_ID] [int] NOT NULL ,
> [DATE_UPDATED_ID] [int] NOT NULL ,
> [BUSINESS_UNIT_ID] [int] NOT NULL ,
> [ADDRESS_BOOK_ID] [int] NOT NULL ,
> [AR_INVOICE_ID] [int] NOT NULL ,
> [INV_DOC_TYPE_ID] [int] NOT NULL ,
> [AMOUNT_OPEN] [decimal](15, 2) NOT NULL ,
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[FACT_AR_AGEING] WITH NOCHECK ADD
> CONSTRAINT [PK__FACT_AR_AGEING__05257EFE] PRIMARY KEY CLUSTERED
> (
> [COMPANY_ID],
> [DATE_UPDATED_ID],
> [BUSINESS_UNIT_ID],
> [ADDRESS_BOOK_ID],
> [AR_INVOICE_ID],
> [INV_DOC_TYPE_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[FACT_AR_AGEING] ADD
> CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_CREDIT_LIMIT] DEFAULT (0) FOR
> [AMOUNT_CREDIT_LIMIT],
> CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_OPEN] DEFAULT (0) FOR [AMOUNT_OPEN]
> GO
> CREATE INDEX [FACT_AR_AGEING29] ON [dbo].[FACT_AR_AGEING]([COMPANY_ID],
> [DATE_UPDATED_ID], [BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID], [AR_INVOICE_ID],
> [INV_DOC_TYPE_ID], [WEIGHTED_DAYS_INVOICE]) ON [INDEXES]
> GO
> -- Script for Indexed View that aggregates to Fiscal period.
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW dbo.IV_test
> WITH SCHEMABINDING
> AS
> SELECT A1.BUSINESS_UNIT_ID, A1.ADDRESS_BOOK_ID, A2.FISCAL_PERIOD_ID,
> SUM(A1.AMOUNT_OPEN) AS Amount, COUNT_BIG (*) AS [Count_Big]
> FROM dbo.FACT_AR_AGEING A1 INNER JOIN
> dbo.LU_DATE A2 ON A1.DATE_UPDATED_ID = A2.DATE_ID
> GROUP BY A1.BUSINESS_UNIT_ID, A2.FISCAL_PERIOD_ID, A1.ADDRESS_BOOK_ID
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> set
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
> on
> GO
> set NUMERIC_ROUNDABORT off
> GO
> CREATE UNIQUE CLUSTERED INDEX [IX_Main] ON
> [dbo].[IV_test]([BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID] DESC ,
> [FISCAL_PERIOD_ID]) ON [PRIMARY]
> GO
> set NUMERIC_ROUNDABORT off set arithabort OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> SET ANSI_NULLS ON
> GO
> -- the following query hits the Indexed view
> SELECT FACT_AR_AGEING.BUSINESS_UNIT_ID,
> FACT_AR_AGEING.ADDRESS_BOOK_ID,
> LU_DATE.FISCAL_PERIOD_ID,
> SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount
> FROM FACT_AR_AGEING INNER JOIN
> LU_DATE ON FACT_AR_AGEING.DATE_UPDATED_ID => LU_DATE.DATE_ID
> GROUP BY FACT_AR_AGEING.BUSINESS_UNIT_ID, FACT_AR_AGEING.ADDRESS_BOOK_ID,
> LU_DATE.FISCAL_PERIOD_ID
> -- however if I join to a second table in the query the indexed view is
> ignored
> SELECT FACT_AR_AGEING.BUSINESS_UNIT_ID,
> FACT_AR_AGEING.ADDRESS_BOOK_ID,
> LU_DATE.FISCAL_PERIOD_ID,
> SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount
> FROM FACT_AR_AGEING INNER JOIN
> LU_DATE ON FACT_AR_AGEING.DATE_UPDATED_ID => LU_DATE.DATE_ID INNER JOIN
> LU_BUSINESS_UNIT ON FACT_AR_AGEING.BUSINESS_UNIT_ID => LU_BUSINESS_UNIT.BUSINESS_UNIT_ID
> GROUP BY FACT_AR_AGEING.BUSINESS_UNIT_ID, FACT_AR_AGEING.ADDRESS_BOOK_ID,
> LU_DATE.FISCAL_PERIOD_ID
> What I have noticed is that this only occurs if the View has a Join
> defined,
> If I remove the join inside the View The problem doesn't exist
> -- View definition without a join
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW dbo.IV_test
> WITH SCHEMABINDING
> AS
> SELECT BUSINESS_UNIT_ID, ADDRESS_BOOK_ID, SUM(AMOUNT_OPEN) AS Amount,
> COUNT_BIG (*) AS [Count_Big]
> FROM dbo.FACT_AR_AGEING A1
> GROUP BY BUSINESS_UNIT_ID, ADDRESS_BOOK_ID
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> set
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
> on
> GO
> set NUMERIC_ROUNDABORT off
> GO
> CREATE UNIQUE CLUSTERED INDEX [IX_IV_test] ON
> [dbo].[IV_test]([BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID]) ON [PRIMARY]
> GO
> set NUMERIC_ROUNDABORT off set arithabort OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> SET ANSI_NULLS ON
> GO
>
> -- Query with multiple table joins
> SELECT FACT_AR_AGEING.BUSINESS_UNIT_ID,
> LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD, FACT_AR_AGEING.ADDRESS_BOOK_ID,
> SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount,
> LU_ADDRESS_BOOK.A_ADDRESS_BOOK_SRCCD
> FROM FACT_AR_AGEING INNER JOIN
> LU_BUSINESS_UNIT ON FACT_AR_AGEING.BUSINESS_UNIT_ID => LU_BUSINESS_UNIT.BUSINESS_UNIT_ID INNER JOIN
> LU_ADDRESS_BOOK ON FACT_AR_AGEING.ADDRESS_BOOK_ID => LU_ADDRESS_BOOK.ADDRESS_BOOK_ID
> GROUP BY FACT_AR_AGEING.BUSINESS_UNIT_ID,
> LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD, FACT_AR_AGEING.ADDRESS_BOOK_ID,
> LU_ADDRESS_BOOK.A_ADDRESS_BOOK_SRCCD
> "Dan Guzman" wrote:
>> I assume your SQL 2000 SP3 server is Enterprise Edition? Are you getting
>> the same behavior in both environments?
>> Please post the DDL for the tables and your query. Without these
>> details,
>> it's hard to say what the reason might be. The plan can affected by
>> hardware, statistics and service pack level. If you haven't already done
>> so, try updating stats with fullscan to make sure the optimizer has the
>> correct info.
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Richard Greyling" <Richard Greyling@.discussions.microsoft.com> wrote in
>> message news:DC07E76B-671C-4EE9-9C20-56D1F6F0DD02@.microsoft.com...
>> > We are running SQL2000 sp3 (SP4 on our dev environment) as a data
>> > warehouse
>> > solution. We have decided to use indexed views for performance
>> > optimization.
>> > The problem we are having is that if I create an indexed view on one of
>> > my
>> > fact tables with a clustered index, the query optimizer does not use
>> > the
>> > indexed view if I my query joins my fact table to more than one
>> > dimension
>> > table, even though the columns used to join on are in my group by. Is
>> > this
>> > a
>> > limitation of Indexed views or is there sometihig else I need to setup
>> > to
>> > get
>> > this to work correctly?
>>|||Hi Dan
I tried to only include columns relavant to the view
there are three other tables that I didn't include,
LU_DATE
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[LU_DATE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LU_DATE]
GO
CREATE TABLE [dbo].[LU_DATE] (
[DATE_ID] [int] NOT NULL ,
[CALENDAR_DATE] [datetime] NULL ,
[CALENDAR_DATE_DESC] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[FISCAL_WEEK_ID] [int] NULL ,
[FISCAL_WEEK_DESC] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FISCAL_PERIOD_ID] [int] NULL ,
[FISCAL_PERIOD_DESC] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[FISCAL_QUARTER_ID] [int] NULL ,
[FISCAL_QUARTER_DESC] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[FISCAL_YEAR_ID] [int] NULL ,
[CALENDAR_WEEK_ID] [int] NULL ,
[CALENDAR_WEEK_DESC] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CALENDAR_MONTH_ID] [int] NULL ,
[CALENDAR_MONTH_DESC] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CALENDAR_QUARTER_ID] [int] NULL ,
[CALENDAR_QUARTER_DESC] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CALENDAR_YEAR_ID] [int] NULL ,
[MONTH_ID] [int] NULL ,
[MONTH_DESC] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LU_DATE] WITH NOCHECK ADD
CONSTRAINT [PK_LU_DATE] PRIMARY KEY CLUSTERED
(
[DATE_ID]
) ON [PRIMARY]
GO
CREATE INDEX [LU_DATE91] ON [dbo].[LU_DATE]([DATE_ID],
[CALENDAR_MONTH_ID]) ON [INDEXES]
GO
CREATE INDEX [IX_CALENDAR_MONTH_ID] ON
[dbo].[LU_DATE]([CALENDAR_MONTH_ID]) ON [INDEXES]
GO
CREATE INDEX [IX_FISCAL_YEAR_ID_CALENDER_MONTH_ID] ON
[dbo].[LU_DATE]([FISCAL_YEAR_ID], [CALENDAR_MONTH_ID]) ON [INDEXES]
GO
CREATE INDEX [IX_LU_DATE_1] ON [dbo].[LU_DATE]([CALENDAR_DATE]) ON [PRIMARY]
GO
--
--LU_BUSINESS_UNIT
--
CREATE TABLE [dbo].[LU_BUSINESS_UNIT] (
[BUSINESS_UNIT_ID] [int] NOT NULL ,
[BUSINESS_UNIT_SRCCD] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[BUSINESS_UNIT_DESC] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LU_BUSINESS_UNIT] WITH NOCHECK ADD
CONSTRAINT [PK__LU_BUSINESS_UNIT__174363E2] PRIMARY KEY CLUSTERED
(
[BUSINESS_UNIT_ID]
) ON [PRIMARY]
GO
CREATE INDEX [IX_BISINESS_UNIT_SRCCD] ON
[dbo].[LU_BUSINESS_UNIT]([BUSINESS_UNIT_SRCCD]) ON [INDEXES]
GO
--
--LU_ADDRESS_BOOK
--
CREATE TABLE [dbo].[LU_ADDRESS_BOOK] (
[ADDRESS_BOOK_ID] [int] NOT NULL ,
[A_ADDRESS_BOOK_SRCCD] [int] NULL ,
[A_ADDRESS_BOOK_NAME] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LU_ADDRESS_BOOK] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[ADDRESS_BOOK_ID]
) ON [PRIMARY]
GO
CREATE INDEX [LU_ADDRESS_BOOK74] ON
[dbo].[LU_ADDRESS_BOOK]([ADDRESS_BOOK_ID], [A_ADDRESS_BOOK_SRCCD]) ON
[INDEXES]
GO
CREATE INDEX [IX_ADDRESS_BOOK_SRCCD] ON
[dbo].[LU_ADDRESS_BOOK]([A_ADDRESS_BOOK_SRCCD]) ON [INDEXES]
GO
I hope this is enough info
cheerz
Richard
"Dan Guzman" wrote:
> Thanks for the DDL but it looks line the LU_DATE table is missing, I also
> get errors due to the missing columns referenced by the default constraints
> but those probably aren't relevant for this problem.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote in
> message news:326AFFB3-BA04-478B-B19D-A88ED4EF7BFF@.microsoft.com...
> > Here is the DDL for my Fact Table and the View:
> > -- Script for FACT_AR_AGEING
> > CREATE TABLE [dbo].[FACT_AR_AGEING] (
> > [COMPANY_ID] [int] NOT NULL ,
> > [DATE_UPDATED_ID] [int] NOT NULL ,
> > [BUSINESS_UNIT_ID] [int] NOT NULL ,
> > [ADDRESS_BOOK_ID] [int] NOT NULL ,
> > [AR_INVOICE_ID] [int] NOT NULL ,
> > [INV_DOC_TYPE_ID] [int] NOT NULL ,
> > [AMOUNT_OPEN] [decimal](15, 2) NOT NULL ,
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[FACT_AR_AGEING] WITH NOCHECK ADD
> > CONSTRAINT [PK__FACT_AR_AGEING__05257EFE] PRIMARY KEY CLUSTERED
> > (
> > [COMPANY_ID],
> > [DATE_UPDATED_ID],
> > [BUSINESS_UNIT_ID],
> > [ADDRESS_BOOK_ID],
> > [AR_INVOICE_ID],
> > [INV_DOC_TYPE_ID]
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[FACT_AR_AGEING] ADD
> > CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_CREDIT_LIMIT] DEFAULT (0) FOR
> > [AMOUNT_CREDIT_LIMIT],
> > CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_OPEN] DEFAULT (0) FOR [AMOUNT_OPEN]
> >
> > GO
> >
> > CREATE INDEX [FACT_AR_AGEING29] ON [dbo].[FACT_AR_AGEING]([COMPANY_ID],
> > [DATE_UPDATED_ID], [BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID], [AR_INVOICE_ID],
> > [INV_DOC_TYPE_ID], [WEIGHTED_DAYS_INVOICE]) ON [INDEXES]
> > GO
> >
> > -- Script for Indexed View that aggregates to Fiscal period.
> > SET QUOTED_IDENTIFIER ON
> > GO
> > SET ANSI_NULLS ON
> > GO
> >
> > CREATE VIEW dbo.IV_test
> > WITH SCHEMABINDING
> > AS
> > SELECT A1.BUSINESS_UNIT_ID, A1.ADDRESS_BOOK_ID, A2.FISCAL_PERIOD_ID,
> > SUM(A1.AMOUNT_OPEN) AS Amount, COUNT_BIG (*) AS [Count_Big]
> > FROM dbo.FACT_AR_AGEING A1 INNER JOIN
> > dbo.LU_DATE A2 ON A1.DATE_UPDATED_ID = A2.DATE_ID
> > GROUP BY A1.BUSINESS_UNIT_ID, A2.FISCAL_PERIOD_ID, A1.ADDRESS_BOOK_ID
> >
> > GO
> > SET QUOTED_IDENTIFIER OFF
> > GO
> > SET ANSI_NULLS ON
> > GO
> >
> > set
> > ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
> > on
> > GO
> >
> > set NUMERIC_ROUNDABORT off
> > GO
> >
> > CREATE UNIQUE CLUSTERED INDEX [IX_Main] ON
> > [dbo].[IV_test]([BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID] DESC ,
> > [FISCAL_PERIOD_ID]) ON [PRIMARY]
> > GO
> >
> > set NUMERIC_ROUNDABORT off set arithabort OFF
> > GO
> >
> > SET QUOTED_IDENTIFIER OFF
> >
> > SET ANSI_NULLS ON
> > GO
> >
> > -- the following query hits the Indexed view
> > SELECT FACT_AR_AGEING.BUSINESS_UNIT_ID,
> > FACT_AR_AGEING.ADDRESS_BOOK_ID,
> > LU_DATE.FISCAL_PERIOD_ID,
> > SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount
> > FROM FACT_AR_AGEING INNER JOIN
> > LU_DATE ON FACT_AR_AGEING.DATE_UPDATED_ID => > LU_DATE.DATE_ID
> > GROUP BY FACT_AR_AGEING.BUSINESS_UNIT_ID, FACT_AR_AGEING.ADDRESS_BOOK_ID,
> > LU_DATE.FISCAL_PERIOD_ID
> >
> > -- however if I join to a second table in the query the indexed view is
> > ignored
> > SELECT FACT_AR_AGEING.BUSINESS_UNIT_ID,
> > FACT_AR_AGEING.ADDRESS_BOOK_ID,
> > LU_DATE.FISCAL_PERIOD_ID,
> > SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount
> > FROM FACT_AR_AGEING INNER JOIN
> > LU_DATE ON FACT_AR_AGEING.DATE_UPDATED_ID => > LU_DATE.DATE_ID INNER JOIN
> > LU_BUSINESS_UNIT ON FACT_AR_AGEING.BUSINESS_UNIT_ID => > LU_BUSINESS_UNIT.BUSINESS_UNIT_ID
> > GROUP BY FACT_AR_AGEING.BUSINESS_UNIT_ID, FACT_AR_AGEING.ADDRESS_BOOK_ID,
> > LU_DATE.FISCAL_PERIOD_ID
> >
> > What I have noticed is that this only occurs if the View has a Join
> > defined,
> > If I remove the join inside the View The problem doesn't exist
> > -- View definition without a join
> > SET QUOTED_IDENTIFIER ON
> > GO
> > SET ANSI_NULLS ON
> > GO
> >
> > CREATE VIEW dbo.IV_test
> > WITH SCHEMABINDING
> > AS
> > SELECT BUSINESS_UNIT_ID, ADDRESS_BOOK_ID, SUM(AMOUNT_OPEN) AS Amount,
> > COUNT_BIG (*) AS [Count_Big]
> > FROM dbo.FACT_AR_AGEING A1
> > GROUP BY BUSINESS_UNIT_ID, ADDRESS_BOOK_ID
> >
> > GO
> > SET QUOTED_IDENTIFIER OFF
> > GO
> > SET ANSI_NULLS ON
> > GO
> >
> > set
> > ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
> > on
> > GO
> >
> > set NUMERIC_ROUNDABORT off
> > GO
> >
> > CREATE UNIQUE CLUSTERED INDEX [IX_IV_test] ON
> > [dbo].[IV_test]([BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID]) ON [PRIMARY]
> > GO
> >
> > set NUMERIC_ROUNDABORT off set arithabort OFF
> > GO
> >
> > SET QUOTED_IDENTIFIER OFF
> >
> > SET ANSI_NULLS ON
> > GO
> >
> >
> > -- Query with multiple table joins
> > SELECT FACT_AR_AGEING.BUSINESS_UNIT_ID,
> > LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD, FACT_AR_AGEING.ADDRESS_BOOK_ID,
> > SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount,
> > LU_ADDRESS_BOOK.A_ADDRESS_BOOK_SRCCD
> > FROM FACT_AR_AGEING INNER JOIN
> > LU_BUSINESS_UNIT ON FACT_AR_AGEING.BUSINESS_UNIT_ID => > LU_BUSINESS_UNIT.BUSINESS_UNIT_ID INNER JOIN
> > LU_ADDRESS_BOOK ON FACT_AR_AGEING.ADDRESS_BOOK_ID => > LU_ADDRESS_BOOK.ADDRESS_BOOK_ID
> > GROUP BY FACT_AR_AGEING.BUSINESS_UNIT_ID,
> > LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD, FACT_AR_AGEING.ADDRESS_BOOK_ID,
> > LU_ADDRESS_BOOK.A_ADDRESS_BOOK_SRCCD
> > "Dan Guzman" wrote:
> >
> >> I assume your SQL 2000 SP3 server is Enterprise Edition? Are you getting
> >> the same behavior in both environments?
> >>
> >> Please post the DDL for the tables and your query. Without these
> >> details,
> >> it's hard to say what the reason might be. The plan can affected by
> >> hardware, statistics and service pack level. If you haven't already done
> >> so, try updating stats with fullscan to make sure the optimizer has the
> >> correct info.
> >>
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Richard Greyling" <Richard Greyling@.discussions.microsoft.com> wrote in
> >> message news:DC07E76B-671C-4EE9-9C20-56D1F6F0DD02@.microsoft.com...
> >> > We are running SQL2000 sp3 (SP4 on our dev environment) as a data
> >> > warehouse
> >> > solution. We have decided to use indexed views for performance
> >> > optimization.
> >> > The problem we are having is that if I create an indexed view on one of
> >> > my
> >> > fact tables with a clustered index, the query optimizer does not use
> >> > the
> >> > indexed view if I my query joins my fact table to more than one
> >> > dimension
> >> > table, even though the columns used to join on are in my group by. Is
> >> > this
> >> > a
> >> > limitation of Indexed views or is there sometihig else I need to setup
> >> > to
> >> > get
> >> > this to work correctly?
> >>
> >>
> >>
>
>|||Thanks for the additional DDL. The create for the following default
constraints and index failed but I don't think this is relevant for this
issue:
ALTER TABLE [dbo].[FACT_AR_AGEING] ADD
CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_CREDIT_LIMIT] DEFAULT (0) FOR
[AMOUNT_CREDIT_LIMIT],
CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_OPEN] DEFAULT (0) FOR [AMOUNT_OPEN]
CREATE INDEX [FACT_AR_AGEING29] ON [dbo].[FACT_AR_AGEING]([COMPANY_ID],
[DATE_UPDATED_ID], [BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID],
[AR_INVOICE_ID],
[INV_DOC_TYPE_ID], [WEIGHTED_DAYS_INVOICE]) ON [INDEXES]
I did some experimenting (sans data) and could only get the view to be used
by accessing the view directly and adding an optimizer hint.
SELECT
IV_TEST.BUSINESS_UNIT_ID,
IV_TEST.ADDRESS_BOOK_ID,
IV_TEST.FISCAL_PERIOD_ID,
SUM(IV_TEST.Amount) AS Amount
FROM dbo.IV_test WITH (NOEXPAND)
INNER JOIN dbo.LU_BUSINESS_UNIT ON
IV_TEST.BUSINESS_UNIT_ID = LU_BUSINESS_UNIT.BUSINESS_UNIT_ID
GROUP BY
IV_TEST.BUSINESS_UNIT_ID,
IV_TEST.ADDRESS_BOOK_ID,
IV_TEST.FISCAL_PERIOD_ID
It looks to me like this is the more optimal plan so I agree with you that
the optimizer could/should use the view. I assume you see a similar plan,
even with substantial data loaded?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote in
message news:3FABE473-511D-479E-A83D-9EAFE1B697C7@.microsoft.com...
> Hi Dan
> I tried to only include columns relavant to the view
> there are three other tables that I didn't include,
> LU_DATE
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[LU_DATE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[LU_DATE]
> GO
> CREATE TABLE [dbo].[LU_DATE] (
> [DATE_ID] [int] NOT NULL ,
> [CALENDAR_DATE] [datetime] NULL ,
> [CALENDAR_DATE_DESC] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [FISCAL_WEEK_ID] [int] NULL ,
> [FISCAL_WEEK_DESC] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [FISCAL_PERIOD_ID] [int] NULL ,
> [FISCAL_PERIOD_DESC] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [FISCAL_QUARTER_ID] [int] NULL ,
> [FISCAL_QUARTER_DESC] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [FISCAL_YEAR_ID] [int] NULL ,
> [CALENDAR_WEEK_ID] [int] NULL ,
> [CALENDAR_WEEK_DESC] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [CALENDAR_MONTH_ID] [int] NULL ,
> [CALENDAR_MONTH_DESC] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [CALENDAR_QUARTER_ID] [int] NULL ,
> [CALENDAR_QUARTER_DESC] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [CALENDAR_YEAR_ID] [int] NULL ,
> [MONTH_ID] [int] NULL ,
> [MONTH_DESC] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[LU_DATE] WITH NOCHECK ADD
> CONSTRAINT [PK_LU_DATE] PRIMARY KEY CLUSTERED
> (
> [DATE_ID]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [LU_DATE91] ON [dbo].[LU_DATE]([DATE_ID],
> [CALENDAR_MONTH_ID]) ON [INDEXES]
> GO
> CREATE INDEX [IX_CALENDAR_MONTH_ID] ON
> [dbo].[LU_DATE]([CALENDAR_MONTH_ID]) ON [INDEXES]
> GO
> CREATE INDEX [IX_FISCAL_YEAR_ID_CALENDER_MONTH_ID] ON
> [dbo].[LU_DATE]([FISCAL_YEAR_ID], [CALENDAR_MONTH_ID]) ON [INDEXES]
> GO
> CREATE INDEX [IX_LU_DATE_1] ON [dbo].[LU_DATE]([CALENDAR_DATE]) ON
> [PRIMARY]
> GO
> --
> --LU_BUSINESS_UNIT
> --
> CREATE TABLE [dbo].[LU_BUSINESS_UNIT] (
> [BUSINESS_UNIT_ID] [int] NOT NULL ,
> [BUSINESS_UNIT_SRCCD] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [BUSINESS_UNIT_DESC] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[LU_BUSINESS_UNIT] WITH NOCHECK ADD
> CONSTRAINT [PK__LU_BUSINESS_UNIT__174363E2] PRIMARY KEY CLUSTERED
> (
> [BUSINESS_UNIT_ID]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [IX_BISINESS_UNIT_SRCCD] ON
> [dbo].[LU_BUSINESS_UNIT]([BUSINESS_UNIT_SRCCD]) ON [INDEXES]
> GO
> --
> --LU_ADDRESS_BOOK
> --
> CREATE TABLE [dbo].[LU_ADDRESS_BOOK] (
> [ADDRESS_BOOK_ID] [int] NOT NULL ,
> [A_ADDRESS_BOOK_SRCCD] [int] NULL ,
> [A_ADDRESS_BOOK_NAME] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[LU_ADDRESS_BOOK] WITH NOCHECK ADD
> PRIMARY KEY CLUSTERED
> (
> [ADDRESS_BOOK_ID]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [LU_ADDRESS_BOOK74] ON
> [dbo].[LU_ADDRESS_BOOK]([ADDRESS_BOOK_ID], [A_ADDRESS_BOOK_SRCCD]) ON
> [INDEXES]
> GO
> CREATE INDEX [IX_ADDRESS_BOOK_SRCCD] ON
> [dbo].[LU_ADDRESS_BOOK]([A_ADDRESS_BOOK_SRCCD]) ON [INDEXES]
> GO
>
> I hope this is enough info
> cheerz
> Richard
>
>
> "Dan Guzman" wrote:
>> Thanks for the DDL but it looks line the LU_DATE table is missing, I
>> also
>> get errors due to the missing columns referenced by the default
>> constraints
>> but those probably aren't relevant for this problem.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote in
>> message news:326AFFB3-BA04-478B-B19D-A88ED4EF7BFF@.microsoft.com...
>> > Here is the DDL for my Fact Table and the View:
>> > -- Script for FACT_AR_AGEING
>> > CREATE TABLE [dbo].[FACT_AR_AGEING] (
>> > [COMPANY_ID] [int] NOT NULL ,
>> > [DATE_UPDATED_ID] [int] NOT NULL ,
>> > [BUSINESS_UNIT_ID] [int] NOT NULL ,
>> > [ADDRESS_BOOK_ID] [int] NOT NULL ,
>> > [AR_INVOICE_ID] [int] NOT NULL ,
>> > [INV_DOC_TYPE_ID] [int] NOT NULL ,
>> > [AMOUNT_OPEN] [decimal](15, 2) NOT NULL ,
>> > ) ON [PRIMARY]
>> > GO
>> >
>> > ALTER TABLE [dbo].[FACT_AR_AGEING] WITH NOCHECK ADD
>> > CONSTRAINT [PK__FACT_AR_AGEING__05257EFE] PRIMARY KEY CLUSTERED
>> > (
>> > [COMPANY_ID],
>> > [DATE_UPDATED_ID],
>> > [BUSINESS_UNIT_ID],
>> > [ADDRESS_BOOK_ID],
>> > [AR_INVOICE_ID],
>> > [INV_DOC_TYPE_ID]
>> > ) ON [PRIMARY]
>> > GO
>> >
>> > ALTER TABLE [dbo].[FACT_AR_AGEING] ADD
>> > CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_CREDIT_LIMIT] DEFAULT (0) FOR
>> > [AMOUNT_CREDIT_LIMIT],
>> > CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_OPEN] DEFAULT (0) FOR
>> > [AMOUNT_OPEN]
>> >
>> > GO
>> >
>> > CREATE INDEX [FACT_AR_AGEING29] ON
>> > [dbo].[FACT_AR_AGEING]([COMPANY_ID],
>> > [DATE_UPDATED_ID], [BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID],
>> > [AR_INVOICE_ID],
>> > [INV_DOC_TYPE_ID], [WEIGHTED_DAYS_INVOICE]) ON [INDEXES]
>> > GO
>> >
>> > -- Script for Indexed View that aggregates to Fiscal period.
>> > SET QUOTED_IDENTIFIER ON
>> > GO
>> > SET ANSI_NULLS ON
>> > GO
>> >
>> > CREATE VIEW dbo.IV_test
>> > WITH SCHEMABINDING
>> > AS
>> > SELECT A1.BUSINESS_UNIT_ID, A1.ADDRESS_BOOK_ID,
>> > A2.FISCAL_PERIOD_ID,
>> > SUM(A1.AMOUNT_OPEN) AS Amount, COUNT_BIG (*) AS [Count_Big]
>> > FROM dbo.FACT_AR_AGEING A1 INNER JOIN
>> > dbo.LU_DATE A2 ON A1.DATE_UPDATED_ID = A2.DATE_ID
>> > GROUP BY A1.BUSINESS_UNIT_ID, A2.FISCAL_PERIOD_ID, A1.ADDRESS_BOOK_ID
>> >
>> > GO
>> > SET QUOTED_IDENTIFIER OFF
>> > GO
>> > SET ANSI_NULLS ON
>> > GO
>> >
>> > set
>> > ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
>> > on
>> > GO
>> >
>> > set NUMERIC_ROUNDABORT off
>> > GO
>> >
>> > CREATE UNIQUE CLUSTERED INDEX [IX_Main] ON
>> > [dbo].[IV_test]([BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID] DESC ,
>> > [FISCAL_PERIOD_ID]) ON [PRIMARY]
>> > GO
>> >
>> > set NUMERIC_ROUNDABORT off set arithabort OFF
>> > GO
>> >
>> > SET QUOTED_IDENTIFIER OFF
>> >
>> > SET ANSI_NULLS ON
>> > GO
>> >
>> > -- the following query hits the Indexed view
>> > SELECT FACT_AR_AGEING.BUSINESS_UNIT_ID,
>> > FACT_AR_AGEING.ADDRESS_BOOK_ID,
>> > LU_DATE.FISCAL_PERIOD_ID,
>> > SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount
>> > FROM FACT_AR_AGEING INNER JOIN
>> > LU_DATE ON FACT_AR_AGEING.DATE_UPDATED_ID =>> > LU_DATE.DATE_ID
>> > GROUP BY FACT_AR_AGEING.BUSINESS_UNIT_ID,
>> > FACT_AR_AGEING.ADDRESS_BOOK_ID,
>> > LU_DATE.FISCAL_PERIOD_ID
>> >
>> > -- however if I join to a second table in the query the indexed view is
>> > ignored
>> > SELECT FACT_AR_AGEING.BUSINESS_UNIT_ID,
>> > FACT_AR_AGEING.ADDRESS_BOOK_ID,
>> > LU_DATE.FISCAL_PERIOD_ID,
>> > SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount
>> > FROM FACT_AR_AGEING INNER JOIN
>> > LU_DATE ON FACT_AR_AGEING.DATE_UPDATED_ID =>> > LU_DATE.DATE_ID INNER JOIN
>> > LU_BUSINESS_UNIT ON
>> > FACT_AR_AGEING.BUSINESS_UNIT_ID =>> > LU_BUSINESS_UNIT.BUSINESS_UNIT_ID
>> > GROUP BY FACT_AR_AGEING.BUSINESS_UNIT_ID,
>> > FACT_AR_AGEING.ADDRESS_BOOK_ID,
>> > LU_DATE.FISCAL_PERIOD_ID
>> >
>> > What I have noticed is that this only occurs if the View has a Join
>> > defined,
>> > If I remove the join inside the View The problem doesn't exist
>> > -- View definition without a join
>> > SET QUOTED_IDENTIFIER ON
>> > GO
>> > SET ANSI_NULLS ON
>> > GO
>> >
>> > CREATE VIEW dbo.IV_test
>> > WITH SCHEMABINDING
>> > AS
>> > SELECT BUSINESS_UNIT_ID, ADDRESS_BOOK_ID, SUM(AMOUNT_OPEN) AS
>> > Amount,
>> > COUNT_BIG (*) AS [Count_Big]
>> > FROM dbo.FACT_AR_AGEING A1
>> > GROUP BY BUSINESS_UNIT_ID, ADDRESS_BOOK_ID
>> >
>> > GO
>> > SET QUOTED_IDENTIFIER OFF
>> > GO
>> > SET ANSI_NULLS ON
>> > GO
>> >
>> > set
>> > ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
>> > on
>> > GO
>> >
>> > set NUMERIC_ROUNDABORT off
>> > GO
>> >
>> > CREATE UNIQUE CLUSTERED INDEX [IX_IV_test] ON
>> > [dbo].[IV_test]([BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID]) ON [PRIMARY]
>> > GO
>> >
>> > set NUMERIC_ROUNDABORT off set arithabort OFF
>> > GO
>> >
>> > SET QUOTED_IDENTIFIER OFF
>> >
>> > SET ANSI_NULLS ON
>> > GO
>> >
>> >
>> > -- Query with multiple table joins
>> > SELECT FACT_AR_AGEING.BUSINESS_UNIT_ID,
>> > LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD, FACT_AR_AGEING.ADDRESS_BOOK_ID,
>> > SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount,
>> > LU_ADDRESS_BOOK.A_ADDRESS_BOOK_SRCCD
>> > FROM FACT_AR_AGEING INNER JOIN
>> > LU_BUSINESS_UNIT ON
>> > FACT_AR_AGEING.BUSINESS_UNIT_ID =>> > LU_BUSINESS_UNIT.BUSINESS_UNIT_ID INNER JOIN
>> > LU_ADDRESS_BOOK ON FACT_AR_AGEING.ADDRESS_BOOK_ID
>> > =>> > LU_ADDRESS_BOOK.ADDRESS_BOOK_ID
>> > GROUP BY FACT_AR_AGEING.BUSINESS_UNIT_ID,
>> > LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD, FACT_AR_AGEING.ADDRESS_BOOK_ID,
>> > LU_ADDRESS_BOOK.A_ADDRESS_BOOK_SRCCD
>> > "Dan Guzman" wrote:
>> >
>> >> I assume your SQL 2000 SP3 server is Enterprise Edition? Are you
>> >> getting
>> >> the same behavior in both environments?
>> >>
>> >> Please post the DDL for the tables and your query. Without these
>> >> details,
>> >> it's hard to say what the reason might be. The plan can affected by
>> >> hardware, statistics and service pack level. If you haven't already
>> >> done
>> >> so, try updating stats with fullscan to make sure the optimizer has
>> >> the
>> >> correct info.
>> >>
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "Richard Greyling" <Richard Greyling@.discussions.microsoft.com> wrote
>> >> in
>> >> message news:DC07E76B-671C-4EE9-9C20-56D1F6F0DD02@.microsoft.com...
>> >> > We are running SQL2000 sp3 (SP4 on our dev environment) as a data
>> >> > warehouse
>> >> > solution. We have decided to use indexed views for performance
>> >> > optimization.
>> >> > The problem we are having is that if I create an indexed view on one
>> >> > of
>> >> > my
>> >> > fact tables with a clustered index, the query optimizer does not use
>> >> > the
>> >> > indexed view if I my query joins my fact table to more than one
>> >> > dimension
>> >> > table, even though the columns used to join on are in my group by.
>> >> > Is
>> >> > this
>> >> > a
>> >> > limitation of Indexed views or is there sometihig else I need to
>> >> > setup
>> >> > to
>> >> > get
>> >> > this to work correctly?
>> >>
>> >>
>> >>
>>|||Hi Dan,
Thanks for your assistance.
The constraint has no effect on this issue, I removed it from my table.
here is the execution plan if I remove the Join to LU_DATE from my view
SET SHOWPLAN_ALL on
SELECT LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD,
SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount,
LU_ADDRESS_BOOK.A_ADDRESS_BOOK_SRCCD,
LU_COMPANY.COMPANY_SRCCD
FROM FACT_AR_AGEING INNER JOIN
LU_BUSINESS_UNIT ON FACT_AR_AGEING.BUSINESS_UNIT_ID =LU_BUSINESS_UNIT.BUSINESS_UNIT_ID INNER JOIN
LU_ADDRESS_BOOK ON FACT_AR_AGEING.ADDRESS_BOOK_ID =LU_ADDRESS_BOOK.ADDRESS_BOOK_ID INNER JOIN
LU_COMPANY ON FACT_AR_AGEING.COMPANY_ID =LU_COMPANY.COMPANY_ID
GROUP BY
LU_BUSINESS_UNIT.BUSINESS_UNIT_ID,LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD,
LU_ADDRESS_BOOK.ADDRESS_BOOK_ID,LU_ADDRESS_BOOK.A_ADDRESS_BOOK_SRCCD,
LU_COMPANY.COMPANY_ID,LU_COMPANY.COMPANY_SRCCD
|--Hash Match(Inner Join,
HASH:([LU_BUSINESS_UNIT].[BUSINESS_UNIT_ID])=([IV_test].[BUSINESS_UNIT_ID]))
|--Index
Scan(OBJECT:([JDE_WH_PREPD].[dbo].[LU_BUSINESS_UNIT].[IX_BUSINESS_UNIT_ID_BUSINESS_UNIT_SRCCD_]))
|--Hash Match(Inner Join,
HASH:([LU_COMPANY].[COMPANY_ID])=([IV_test].[COMPANY_ID]))
|--Clustered Index
Scan(OBJECT:([JDE_WH_PREPD].[dbo].[LU_COMPANY].[PK__LU_COMPANY__32EB7E57]))
|--Merge Join(Inner Join,
MERGE:([LU_ADDRESS_BOOK].[ADDRESS_BOOK_ID])=([IV_test].[ADDRESS_BOOK_ID]),
RESIDUAL:([IV_test].[ADDRESS_BOOK_ID]=[LU_ADDRESS_BOOK].[ADDRESS_BOOK_ID]))
|--Index
Scan(OBJECT:([JDE_WH_PREPD].[dbo].[LU_ADDRESS_BOOK].[LU_ADDRESS_BOOK74]),
ORDERED FORWARD)
|--Sort(ORDER BY:([IV_test].[ADDRESS_BOOK_ID] ASC))
|--Clustered Index
Scan(OBJECT:([JDE_WH_PREPD].[dbo].[IV_test].[IX_IV_test]))
--
As you can see the Optimizer seems to do its job.
But if I add a Join to my view The same query doesn't play the game.
Unfortunately our front end tool is a ROLAP tool so we cannot hard force the
Query to query directly from the view.
Thanks
Richard
"Dan Guzman" wrote:
> Thanks for the additional DDL. The create for the following default
> constraints and index failed but I don't think this is relevant for this
> issue:
> ALTER TABLE [dbo].[FACT_AR_AGEING] ADD
> CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_CREDIT_LIMIT] DEFAULT (0) FOR
> [AMOUNT_CREDIT_LIMIT],
> CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_OPEN] DEFAULT (0) FOR [AMOUNT_OPEN]
> CREATE INDEX [FACT_AR_AGEING29] ON [dbo].[FACT_AR_AGEING]([COMPANY_ID],
> [DATE_UPDATED_ID], [BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID],
> [AR_INVOICE_ID],
> [INV_DOC_TYPE_ID], [WEIGHTED_DAYS_INVOICE]) ON [INDEXES]
> I did some experimenting (sans data) and could only get the view to be used
> by accessing the view directly and adding an optimizer hint.
> SELECT
> IV_TEST.BUSINESS_UNIT_ID,
> IV_TEST.ADDRESS_BOOK_ID,
> IV_TEST.FISCAL_PERIOD_ID,
> SUM(IV_TEST.Amount) AS Amount
> FROM dbo.IV_test WITH (NOEXPAND)
> INNER JOIN dbo.LU_BUSINESS_UNIT ON
> IV_TEST.BUSINESS_UNIT_ID = LU_BUSINESS_UNIT.BUSINESS_UNIT_ID
> GROUP BY
> IV_TEST.BUSINESS_UNIT_ID,
> IV_TEST.ADDRESS_BOOK_ID,
> IV_TEST.FISCAL_PERIOD_ID
> It looks to me like this is the more optimal plan so I agree with you that
> the optimizer could/should use the view. I assume you see a similar plan,
> even with substantial data loaded?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote in
> message news:3FABE473-511D-479E-A83D-9EAFE1B697C7@.microsoft.com...
> > Hi Dan
> > I tried to only include columns relavant to the view
> > there are three other tables that I didn't include,
> > LU_DATE
> > if exists (select * from dbo.sysobjects where id => > object_id(N'[dbo].[LU_DATE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> > drop table [dbo].[LU_DATE]
> > GO
> >
> > CREATE TABLE [dbo].[LU_DATE] (
> > [DATE_ID] [int] NOT NULL ,
> > [CALENDAR_DATE] [datetime] NULL ,
> > [CALENDAR_DATE_DESC] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NULL ,
> > [FISCAL_WEEK_ID] [int] NULL ,
> > [FISCAL_WEEK_DESC] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NULL ,
> > [FISCAL_PERIOD_ID] [int] NULL ,
> > [FISCAL_PERIOD_DESC] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NULL ,
> > [FISCAL_QUARTER_ID] [int] NULL ,
> > [FISCAL_QUARTER_DESC] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NULL ,
> > [FISCAL_YEAR_ID] [int] NULL ,
> > [CALENDAR_WEEK_ID] [int] NULL ,
> > [CALENDAR_WEEK_DESC] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NULL ,
> > [CALENDAR_MONTH_ID] [int] NULL ,
> > [CALENDAR_MONTH_DESC] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NULL ,
> > [CALENDAR_QUARTER_ID] [int] NULL ,
> > [CALENDAR_QUARTER_DESC] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NULL ,
> > [CALENDAR_YEAR_ID] [int] NULL ,
> > [MONTH_ID] [int] NULL ,
> > [MONTH_DESC] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[LU_DATE] WITH NOCHECK ADD
> > CONSTRAINT [PK_LU_DATE] PRIMARY KEY CLUSTERED
> > (
> > [DATE_ID]
> > ) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [LU_DATE91] ON [dbo].[LU_DATE]([DATE_ID],
> > [CALENDAR_MONTH_ID]) ON [INDEXES]
> > GO
> >
> > CREATE INDEX [IX_CALENDAR_MONTH_ID] ON
> > [dbo].[LU_DATE]([CALENDAR_MONTH_ID]) ON [INDEXES]
> > GO
> > CREATE INDEX [IX_FISCAL_YEAR_ID_CALENDER_MONTH_ID] ON
> > [dbo].[LU_DATE]([FISCAL_YEAR_ID], [CALENDAR_MONTH_ID]) ON [INDEXES]
> > GO
> > CREATE INDEX [IX_LU_DATE_1] ON [dbo].[LU_DATE]([CALENDAR_DATE]) ON
> > [PRIMARY]
> > GO
> > --
> > --LU_BUSINESS_UNIT
> > --
> > CREATE TABLE [dbo].[LU_BUSINESS_UNIT] (
> > [BUSINESS_UNIT_ID] [int] NOT NULL ,
> > [BUSINESS_UNIT_SRCCD] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NOT NULL ,
> > [BUSINESS_UNIT_DESC] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[LU_BUSINESS_UNIT] WITH NOCHECK ADD
> > CONSTRAINT [PK__LU_BUSINESS_UNIT__174363E2] PRIMARY KEY CLUSTERED
> > (
> > [BUSINESS_UNIT_ID]
> > ) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [IX_BISINESS_UNIT_SRCCD] ON
> > [dbo].[LU_BUSINESS_UNIT]([BUSINESS_UNIT_SRCCD]) ON [INDEXES]
> > GO
> > --
> > --LU_ADDRESS_BOOK
> > --
> >
> > CREATE TABLE [dbo].[LU_ADDRESS_BOOK] (
> > [ADDRESS_BOOK_ID] [int] NOT NULL ,
> > [A_ADDRESS_BOOK_SRCCD] [int] NULL ,
> > [A_ADDRESS_BOOK_NAME] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[LU_ADDRESS_BOOK] WITH NOCHECK ADD
> > PRIMARY KEY CLUSTERED
> > (
> > [ADDRESS_BOOK_ID]
> > ) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [LU_ADDRESS_BOOK74] ON
> > [dbo].[LU_ADDRESS_BOOK]([ADDRESS_BOOK_ID], [A_ADDRESS_BOOK_SRCCD]) ON
> > [INDEXES]
> > GO
> >
> > CREATE INDEX [IX_ADDRESS_BOOK_SRCCD] ON
> > [dbo].[LU_ADDRESS_BOOK]([A_ADDRESS_BOOK_SRCCD]) ON [INDEXES]
> > GO
> >
> >
> > I hope this is enough info
> >
> > cheerz
> > Richard
> >
> >
> >
> >
> >
> > "Dan Guzman" wrote:
> >
> >> Thanks for the DDL but it looks line the LU_DATE table is missing, I
> >> also
> >> get errors due to the missing columns referenced by the default
> >> constraints
> >> but those probably aren't relevant for this problem.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote in
> >> message news:326AFFB3-BA04-478B-B19D-A88ED4EF7BFF@.microsoft.com...
> >> > Here is the DDL for my Fact Table and the View:
> >> > -- Script for FACT_AR_AGEING
> >> > CREATE TABLE [dbo].[FACT_AR_AGEING] (
> >> > [COMPANY_ID] [int] NOT NULL ,
> >> > [DATE_UPDATED_ID] [int] NOT NULL ,
> >> > [BUSINESS_UNIT_ID] [int] NOT NULL ,
> >> > [ADDRESS_BOOK_ID] [int] NOT NULL ,
> >> > [AR_INVOICE_ID] [int] NOT NULL ,
> >> > [INV_DOC_TYPE_ID] [int] NOT NULL ,
> >> > [AMOUNT_OPEN] [decimal](15, 2) NOT NULL ,
> >> > ) ON [PRIMARY]
> >> > GO
> >> >
> >> > ALTER TABLE [dbo].[FACT_AR_AGEING] WITH NOCHECK ADD
> >> > CONSTRAINT [PK__FACT_AR_AGEING__05257EFE] PRIMARY KEY CLUSTERED
> >> > (
> >> > [COMPANY_ID],
> >> > [DATE_UPDATED_ID],
> >> > [BUSINESS_UNIT_ID],
> >> > [ADDRESS_BOOK_ID],
> >> > [AR_INVOICE_ID],
> >> > [INV_DOC_TYPE_ID]
> >> > ) ON [PRIMARY]
> >> > GO
> >> >
> >> > ALTER TABLE [dbo].[FACT_AR_AGEING] ADD
> >> > CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_CREDIT_LIMIT] DEFAULT (0) FOR
> >> > [AMOUNT_CREDIT_LIMIT],
> >> > CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_OPEN] DEFAULT (0) FOR
> >> > [AMOUNT_OPEN]
> >> >
> >> > GO
> >> >
> >> > CREATE INDEX [FACT_AR_AGEING29] ON
> >> > [dbo].[FACT_AR_AGEING]([COMPANY_ID],
> >> > [DATE_UPDATED_ID], [BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID],
> >> > [AR_INVOICE_ID],
> >> > [INV_DOC_TYPE_ID], [WEIGHTED_DAYS_INVOICE]) ON [INDEXES]
> >> > GO
> >> >
> >> > -- Script for Indexed View that aggregates to Fiscal period.
> >> > SET QUOTED_IDENTIFIER ON
> >> > GO
> >> > SET ANSI_NULLS ON
> >> > GO
> >> >
> >> > CREATE VIEW dbo.IV_test
> >> > WITH SCHEMABINDING
> >> > AS
> >> > SELECT A1.BUSINESS_UNIT_ID, A1.ADDRESS_BOOK_ID,
> >> > A2.FISCAL_PERIOD_ID,
> >> > SUM(A1.AMOUNT_OPEN) AS Amount, COUNT_BIG (*) AS [Count_Big]
> >> > FROM dbo.FACT_AR_AGEING A1 INNER JOIN
> >> > dbo.LU_DATE A2 ON A1.DATE_UPDATED_ID = A2.DATE_ID
> >> > GROUP BY A1.BUSINESS_UNIT_ID, A2.FISCAL_PERIOD_ID, A1.ADDRESS_BOOK_ID
> >> >
> >> > GO
> >> > SET QUOTED_IDENTIFIER OFF
> >> > GO
> >> > SET ANSI_NULLS ON
> >> > GO
> >> >
> >> > set
> >> > ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
> >> > on
> >> > GO
> >> >
> >> > set NUMERIC_ROUNDABORT off
> >> > GO
> >> >
> >> > CREATE UNIQUE CLUSTERED INDEX [IX_Main] ON
> >> > [dbo].[IV_test]([BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID] DESC ,
> >> > [FISCAL_PERIOD_ID]) ON [PRIMARY]
> >> > GO
> >> >
> >> > set NUMERIC_ROUNDABORT off set arithabort OFF
> >> > GO
> >> >
> >> > SET QUOTED_IDENTIFIER OFF
> >> >
> >> > SET ANSI_NULLS ON
> >> > GO
> >> >
> >> > -- the following query hits the Indexed view
> >> > SELECT FACT_AR_AGEING.BUSINESS_UNIT_ID,
> >> > FACT_AR_AGEING.ADDRESS_BOOK_ID,
> >> > LU_DATE.FISCAL_PERIOD_ID,
> >> > SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount
> >> > FROM FACT_AR_AGEING INNER JOIN
> >> > LU_DATE ON FACT_AR_AGEING.DATE_UPDATED_ID => >> > LU_DATE.DATE_ID
> >> > GROUP BY FACT_AR_AGEING.BUSINESS_UNIT_ID,
> >> > FACT_AR_AGEING.ADDRESS_BOOK_ID,
> >> > LU_DATE.FISCAL_PERIOD_ID
> >> >
> >> > -- however if I join to a second table in the query the indexed view is
> >> > ignored
> >> > SELECT FACT_AR_AGEING.BUSINESS_UNIT_ID,
> >> > FACT_AR_AGEING.ADDRESS_BOOK_ID,
> >> > LU_DATE.FISCAL_PERIOD_ID,
> >> > SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount
> >> > FROM FACT_AR_AGEING INNER JOIN
> >> > LU_DATE ON FACT_AR_AGEING.DATE_UPDATED_ID => >> > LU_DATE.DATE_ID INNER JOIN
> >> > LU_BUSINESS_UNIT ON
> >> > FACT_AR_AGEING.BUSINESS_UNIT_ID => >> > LU_BUSINESS_UNIT.BUSINESS_UNIT_ID
> >> > GROUP BY FACT_AR_AGEING.BUSINESS_UNIT_ID,
> >> > FACT_AR_AGEING.ADDRESS_BOOK_ID,
> >> > LU_DATE.FISCAL_PERIOD_ID
> >> >
> >> > What I have noticed is that this only occurs if the View has a Join
> >> > defined,
> >> > If I remove the join inside the View The problem doesn't exist
> >> > -- View definition without a join
> >> > SET QUOTED_IDENTIFIER ON
> >> > GO
> >> > SET ANSI_NULLS ON
> >> > GO
> >> >
> >> > CREATE VIEW dbo.IV_test
> >> > WITH SCHEMABINDING
> >> > AS
> >> > SELECT BUSINESS_UNIT_ID, ADDRESS_BOOK_ID, SUM(AMOUNT_OPEN) AS|||> Unfortunately our front end tool is a ROLAP tool so we cannot hard force
> the
> Query to query directly from the view.
SQL 2005 offers some relief in this regard. In some situations, plan guides
can be used to provide more optimal plans in cases where query hints can't
be used directly.
I'm trying to develop a simplified test case for analysis. Can you tell me
approximately how many rows are in your fact table and business unit
dimension table?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote in
message news:3BC1F966-42F9-4C84-9B12-791C00C9DD1E@.microsoft.com...
> Hi Dan,
> Thanks for your assistance.
> The constraint has no effect on this issue, I removed it from my table.
> here is the execution plan if I remove the Join to LU_DATE from my view
> SET SHOWPLAN_ALL on
> SELECT LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD,
> SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount,
> LU_ADDRESS_BOOK.A_ADDRESS_BOOK_SRCCD,
> LU_COMPANY.COMPANY_SRCCD
> FROM FACT_AR_AGEING INNER JOIN
> LU_BUSINESS_UNIT ON FACT_AR_AGEING.BUSINESS_UNIT_ID => LU_BUSINESS_UNIT.BUSINESS_UNIT_ID INNER JOIN
> LU_ADDRESS_BOOK ON FACT_AR_AGEING.ADDRESS_BOOK_ID => LU_ADDRESS_BOOK.ADDRESS_BOOK_ID INNER JOIN
> LU_COMPANY ON FACT_AR_AGEING.COMPANY_ID => LU_COMPANY.COMPANY_ID
> GROUP BY
> LU_BUSINESS_UNIT.BUSINESS_UNIT_ID,LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD,
> LU_ADDRESS_BOOK.ADDRESS_BOOK_ID,LU_ADDRESS_BOOK.A_ADDRESS_BOOK_SRCCD,
> LU_COMPANY.COMPANY_ID,LU_COMPANY.COMPANY_SRCCD
>
> |--Hash Match(Inner Join,
> HASH:([LU_BUSINESS_UNIT].[BUSINESS_UNIT_ID])=([IV_test].[BUSINESS_UNIT_ID]))
> |--Index
> Scan(OBJECT:([JDE_WH_PREPD].[dbo].[LU_BUSINESS_UNIT].[IX_BUSINESS_UNIT_ID_BUSINESS_UNIT_SRCCD_]))
> |--Hash Match(Inner Join,
> HASH:([LU_COMPANY].[COMPANY_ID])=([IV_test].[COMPANY_ID]))
> |--Clustered Index
> Scan(OBJECT:([JDE_WH_PREPD].[dbo].[LU_COMPANY].[PK__LU_COMPANY__32EB7E57]))
> |--Merge Join(Inner Join,
> MERGE:([LU_ADDRESS_BOOK].[ADDRESS_BOOK_ID])=([IV_test].[ADDRESS_BOOK_ID]),
> RESIDUAL:([IV_test].[ADDRESS_BOOK_ID]=[LU_ADDRESS_BOOK].[ADDRESS_BOOK_ID]))
> |--Index
> Scan(OBJECT:([JDE_WH_PREPD].[dbo].[LU_ADDRESS_BOOK].[LU_ADDRESS_BOOK74]),
> ORDERED FORWARD)
> |--Sort(ORDER BY:([IV_test].[ADDRESS_BOOK_ID] ASC))
> |--Clustered Index
> Scan(OBJECT:([JDE_WH_PREPD].[dbo].[IV_test].[IX_IV_test]))
> --
> As you can see the Optimizer seems to do its job.
> But if I add a Join to my view The same query doesn't play the game.
> Unfortunately our front end tool is a ROLAP tool so we cannot hard force
> the
> Query to query directly from the view.
> Thanks
> Richard
> "Dan Guzman" wrote:
>> Thanks for the additional DDL. The create for the following default
>> constraints and index failed but I don't think this is relevant for this
>> issue:
>> ALTER TABLE [dbo].[FACT_AR_AGEING] ADD
>> CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_CREDIT_LIMIT] DEFAULT (0) FOR
>> [AMOUNT_CREDIT_LIMIT],
>> CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_OPEN] DEFAULT (0) FOR
>> [AMOUNT_OPEN]
>> CREATE INDEX [FACT_AR_AGEING29] ON [dbo].[FACT_AR_AGEING]([COMPANY_ID],
>> [DATE_UPDATED_ID], [BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID],
>> [AR_INVOICE_ID],
>> [INV_DOC_TYPE_ID], [WEIGHTED_DAYS_INVOICE]) ON [INDEXES]
>> I did some experimenting (sans data) and could only get the view to be
>> used
>> by accessing the view directly and adding an optimizer hint.
>> SELECT
>> IV_TEST.BUSINESS_UNIT_ID,
>> IV_TEST.ADDRESS_BOOK_ID,
>> IV_TEST.FISCAL_PERIOD_ID,
>> SUM(IV_TEST.Amount) AS Amount
>> FROM dbo.IV_test WITH (NOEXPAND)
>> INNER JOIN dbo.LU_BUSINESS_UNIT ON
>> IV_TEST.BUSINESS_UNIT_ID = LU_BUSINESS_UNIT.BUSINESS_UNIT_ID
>> GROUP BY
>> IV_TEST.BUSINESS_UNIT_ID,
>> IV_TEST.ADDRESS_BOOK_ID,
>> IV_TEST.FISCAL_PERIOD_ID
>> It looks to me like this is the more optimal plan so I agree with you
>> that
>> the optimizer could/should use the view. I assume you see a similar
>> plan,
>> even with substantial data loaded?
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote in
>> message news:3FABE473-511D-479E-A83D-9EAFE1B697C7@.microsoft.com...
>> > Hi Dan
>> > I tried to only include columns relavant to the view
>> > there are three other tables that I didn't include,
>> > LU_DATE
>> > if exists (select * from dbo.sysobjects where id =>> > object_id(N'[dbo].[LU_DATE]') and OBJECTPROPERTY(id, N'IsUserTable') =>> > 1)
>> > drop table [dbo].[LU_DATE]
>> > GO
>> >
>> > CREATE TABLE [dbo].[LU_DATE] (
>> > [DATE_ID] [int] NOT NULL ,
>> > [CALENDAR_DATE] [datetime] NULL ,
>> > [CALENDAR_DATE_DESC] [varchar] (10) COLLATE
>> > SQL_Latin1_General_CP1_CI_AS
>> > NULL ,
>> > [FISCAL_WEEK_ID] [int] NULL ,
>> > [FISCAL_WEEK_DESC] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
>> > NULL ,
>> > [FISCAL_PERIOD_ID] [int] NULL ,
>> > [FISCAL_PERIOD_DESC] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
>> > NULL ,
>> > [FISCAL_QUARTER_ID] [int] NULL ,
>> > [FISCAL_QUARTER_DESC] [varchar] (9) COLLATE
>> > SQL_Latin1_General_CP1_CI_AS
>> > NULL ,
>> > [FISCAL_YEAR_ID] [int] NULL ,
>> > [CALENDAR_WEEK_ID] [int] NULL ,
>> > [CALENDAR_WEEK_DESC] [varchar] (30) COLLATE
>> > SQL_Latin1_General_CP1_CI_AS
>> > NULL ,
>> > [CALENDAR_MONTH_ID] [int] NULL ,
>> > [CALENDAR_MONTH_DESC] [varchar] (9) COLLATE
>> > SQL_Latin1_General_CP1_CI_AS
>> > NULL ,
>> > [CALENDAR_QUARTER_ID] [int] NULL ,
>> > [CALENDAR_QUARTER_DESC] [varchar] (9) COLLATE
>> > SQL_Latin1_General_CP1_CI_AS
>> > NULL ,
>> > [CALENDAR_YEAR_ID] [int] NULL ,
>> > [MONTH_ID] [int] NULL ,
>> > [MONTH_DESC] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> > ) ON [PRIMARY]
>> > GO
>> >
>> > ALTER TABLE [dbo].[LU_DATE] WITH NOCHECK ADD
>> > CONSTRAINT [PK_LU_DATE] PRIMARY KEY CLUSTERED
>> > (
>> > [DATE_ID]
>> > ) ON [PRIMARY]
>> > GO
>> >
>> > CREATE INDEX [LU_DATE91] ON [dbo].[LU_DATE]([DATE_ID],
>> > [CALENDAR_MONTH_ID]) ON [INDEXES]
>> > GO
>> >
>> > CREATE INDEX [IX_CALENDAR_MONTH_ID] ON
>> > [dbo].[LU_DATE]([CALENDAR_MONTH_ID]) ON [INDEXES]
>> > GO
>> > CREATE INDEX [IX_FISCAL_YEAR_ID_CALENDER_MONTH_ID] ON
>> > [dbo].[LU_DATE]([FISCAL_YEAR_ID], [CALENDAR_MONTH_ID]) ON [INDEXES]
>> > GO
>> > CREATE INDEX [IX_LU_DATE_1] ON [dbo].[LU_DATE]([CALENDAR_DATE]) ON
>> > [PRIMARY]
>> > GO
>> > --
>> > --LU_BUSINESS_UNIT
>> > --
>> > CREATE TABLE [dbo].[LU_BUSINESS_UNIT] (
>> > [BUSINESS_UNIT_ID] [int] NOT NULL ,
>> > [BUSINESS_UNIT_SRCCD] [varchar] (12) COLLATE
>> > SQL_Latin1_General_CP1_CI_AS
>> > NOT NULL ,
>> > [BUSINESS_UNIT_DESC] [varchar] (30) COLLATE
>> > SQL_Latin1_General_CP1_CI_AS
>> > NULL
>> > ) ON [PRIMARY]
>> > GO
>> >
>> > ALTER TABLE [dbo].[LU_BUSINESS_UNIT] WITH NOCHECK ADD
>> > CONSTRAINT [PK__LU_BUSINESS_UNIT__174363E2] PRIMARY KEY CLUSTERED
>> > (
>> > [BUSINESS_UNIT_ID]
>> > ) ON [PRIMARY]
>> > GO
>> >
>> > CREATE INDEX [IX_BISINESS_UNIT_SRCCD] ON
>> > [dbo].[LU_BUSINESS_UNIT]([BUSINESS_UNIT_SRCCD]) ON [INDEXES]
>> > GO
>> > --
>> > --LU_ADDRESS_BOOK
>> > --
>> >
>> > CREATE TABLE [dbo].[LU_ADDRESS_BOOK] (
>> > [ADDRESS_BOOK_ID] [int] NOT NULL ,
>> > [A_ADDRESS_BOOK_SRCCD] [int] NULL ,
>> > [A_ADDRESS_BOOK_NAME] [varchar] (40) COLLATE
>> > SQL_Latin1_General_CP1_CI_AS
>> > NULL
>> > ) ON [PRIMARY]
>> > GO
>> >
>> > ALTER TABLE [dbo].[LU_ADDRESS_BOOK] WITH NOCHECK ADD
>> > PRIMARY KEY CLUSTERED
>> > (
>> > [ADDRESS_BOOK_ID]
>> > ) ON [PRIMARY]
>> > GO
>> >
>> > CREATE INDEX [LU_ADDRESS_BOOK74] ON
>> > [dbo].[LU_ADDRESS_BOOK]([ADDRESS_BOOK_ID], [A_ADDRESS_BOOK_SRCCD]) ON
>> > [INDEXES]
>> > GO
>> >
>> > CREATE INDEX [IX_ADDRESS_BOOK_SRCCD] ON
>> > [dbo].[LU_ADDRESS_BOOK]([A_ADDRESS_BOOK_SRCCD]) ON [INDEXES]
>> > GO
>> >
>> >
>> > I hope this is enough info
>> >
>> > cheerz
>> > Richard
>> >
>> >
>> >
>> >
>> >
>> > "Dan Guzman" wrote:
>> >
>> >> Thanks for the DDL but it looks line the LU_DATE table is missing, I
>> >> also
>> >> get errors due to the missing columns referenced by the default
>> >> constraints
>> >> but those probably aren't relevant for this problem.
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote
>> >> in
>> >> message news:326AFFB3-BA04-478B-B19D-A88ED4EF7BFF@.microsoft.com...
>> >> > Here is the DDL for my Fact Table and the View:
>> >> > -- Script for FACT_AR_AGEING
>> >> > CREATE TABLE [dbo].[FACT_AR_AGEING] (
>> >> > [COMPANY_ID] [int] NOT NULL ,
>> >> > [DATE_UPDATED_ID] [int] NOT NULL ,
>> >> > [BUSINESS_UNIT_ID] [int] NOT NULL ,
>> >> > [ADDRESS_BOOK_ID] [int] NOT NULL ,
>> >> > [AR_INVOICE_ID] [int] NOT NULL ,
>> >> > [INV_DOC_TYPE_ID] [int] NOT NULL ,
>> >> > [AMOUNT_OPEN] [decimal](15, 2) NOT NULL ,
>> >> > ) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > ALTER TABLE [dbo].[FACT_AR_AGEING] WITH NOCHECK ADD
>> >> > CONSTRAINT [PK__FACT_AR_AGEING__05257EFE] PRIMARY KEY CLUSTERED
>> >> > (
>> >> > [COMPANY_ID],
>> >> > [DATE_UPDATED_ID],
>> >> > [BUSINESS_UNIT_ID],
>> >> > [ADDRESS_BOOK_ID],
>> >> > [AR_INVOICE_ID],
>> >> > [INV_DOC_TYPE_ID]
>> >> > ) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > ALTER TABLE [dbo].[FACT_AR_AGEING] ADD
>> >> > CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_CREDIT_LIMIT] DEFAULT (0) FOR
>> >> > [AMOUNT_CREDIT_LIMIT],
>> >> > CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_OPEN] DEFAULT (0) FOR
>> >> > [AMOUNT_OPEN]
>> >> >
>> >> > GO
>> >> >
>> >> > CREATE INDEX [FACT_AR_AGEING29] ON
>> >> > [dbo].[FACT_AR_AGEING]([COMPANY_ID],
>> >> > [DATE_UPDATED_ID], [BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID],
>> >> > [AR_INVOICE_ID],
>> >> > [INV_DOC_TYPE_ID], [WEIGHTED_DAYS_INVOICE]) ON [INDEXES]
>> >> > GO
>> >> >
>> >> > -- Script for Indexed View that aggregates to Fiscal period.
>> >> > SET QUOTED_IDENTIFIER ON
>> >> > GO
>> >> > SET ANSI_NULLS ON
>> >> > GO
>> >> >
>> >> > CREATE VIEW dbo.IV_test
>> >> > WITH SCHEMABINDING
>> >> > AS
>> >> > SELECT A1.BUSINESS_UNIT_ID, A1.ADDRESS_BOOK_ID,
>> >> > A2.FISCAL_PERIOD_ID,
>> >> > SUM(A1.AMOUNT_OPEN) AS Amount, COUNT_BIG (*) AS [Count_Big]
>> >> > FROM dbo.FACT_AR_AGEING A1 INNER JOIN
>> >> > dbo.LU_DATE A2 ON A1.DATE_UPDATED_ID =>> >> > A2.DATE_ID
>> >> > GROUP BY A1.BUSINESS_UNIT_ID, A2.FISCAL_PERIOD_ID,
>> >> > A1.ADDRESS_BOOK_ID
>> >> >
>> >> > GO
>> >> > SET QUOTED_IDENTIFIER OFF
>> >> > GO
>> >> > SET ANSI_NULLS ON
>> >> > GO
>> >> >
>> >> > set
>> >> > ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
>> >> > on
>> >> > GO
>> >> >
>> >> > set NUMERIC_ROUNDABORT off
>> >> > GO
>> >> >
>> >> > CREATE UNIQUE CLUSTERED INDEX [IX_Main] ON
>> >> > [dbo].[IV_test]([BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID] DESC ,
>> >> > [FISCAL_PERIOD_ID]) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > set NUMERIC_ROUNDABORT off set arithabort OFF
>> >> > GO
>> >> >
>> >> > SET QUOTED_IDENTIFIER OFF
>> >> >
>> >> > SET ANSI_NULLS ON
>> >> > GO
>> >> >
>> >> > -- the following query hits the Indexed view
>> >> > SELECT FACT_AR_AGEING.BUSINESS_UNIT_ID,
>> >> > FACT_AR_AGEING.ADDRESS_BOOK_ID,
>> >> > LU_DATE.FISCAL_PERIOD_ID,
>> >> > SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount
>> >> > FROM FACT_AR_AGEING INNER JOIN
>> >> > LU_DATE ON FACT_AR_AGEING.DATE_UPDATED_ID =>> >> > LU_DATE.DATE_ID
>> >> > GROUP BY FACT_AR_AGEING.BUSINESS_UNIT_ID,
>> >> > FACT_AR_AGEING.ADDRESS_BOOK_ID,
>> >> > LU_DATE.FISCAL_PERIOD_ID
>> >> >
>> >> > -- however if I join to a second table in the query the indexed view
>> >> > is
>> >> > ignored
>> >> > SELECT FACT_AR_AGEING.BUSINESS_UNIT_ID,
>> >> > FACT_AR_AGEING.ADDRESS_BOOK_ID,
>> >> > LU_DATE.FISCAL_PERIOD_ID,
>> >> > SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount
>> >> > FROM FACT_AR_AGEING INNER JOIN
>> >> > LU_DATE ON FACT_AR_AGEING.DATE_UPDATED_ID =>> >> > LU_DATE.DATE_ID INNER JOIN
>> >> > LU_BUSINESS_UNIT ON
>> >> > FACT_AR_AGEING.BUSINESS_UNIT_ID =>> >> > LU_BUSINESS_UNIT.BUSINESS_UNIT_ID
>> >> > GROUP BY FACT_AR_AGEING.BUSINESS_UNIT_ID,
>> >> > FACT_AR_AGEING.ADDRESS_BOOK_ID,
>> >> > LU_DATE.FISCAL_PERIOD_ID
>> >> >
>> >> > What I have noticed is that this only occurs if the View has a Join
>> >> > defined,
>> >> > If I remove the join inside the View The problem doesn't exist
>> >> > -- View definition without a join
>> >> > SET QUOTED_IDENTIFIER ON
>> >> > GO
>> >> > SET ANSI_NULLS ON
>> >> > GO
>> >> >
>> >> > CREATE VIEW dbo.IV_test
>> >> > WITH SCHEMABINDING
>> >> > AS
>> >> > SELECT BUSINESS_UNIT_ID, ADDRESS_BOOK_ID, SUM(AMOUNT_OPEN) AS|||Hi Dan
The Fact contains about 12 million rows and the BU dimension contains about
4000 rows
Thanks
"Dan Guzman" wrote:
> > Unfortunately our front end tool is a ROLAP tool so we cannot hard force
> > the
> > Query to query directly from the view.
> SQL 2005 offers some relief in this regard. In some situations, plan guides
> can be used to provide more optimal plans in cases where query hints can't
> be used directly.
> I'm trying to develop a simplified test case for analysis. Can you tell me
> approximately how many rows are in your fact table and business unit
> dimension table?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote in
> message news:3BC1F966-42F9-4C84-9B12-791C00C9DD1E@.microsoft.com...
> > Hi Dan,
> > Thanks for your assistance.
> > The constraint has no effect on this issue, I removed it from my table.
> > here is the execution plan if I remove the Join to LU_DATE from my view
> > SET SHOWPLAN_ALL on
> >
> > SELECT LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD,
> > SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount,
> > LU_ADDRESS_BOOK.A_ADDRESS_BOOK_SRCCD,
> > LU_COMPANY.COMPANY_SRCCD
> > FROM FACT_AR_AGEING INNER JOIN
> > LU_BUSINESS_UNIT ON FACT_AR_AGEING.BUSINESS_UNIT_ID => > LU_BUSINESS_UNIT.BUSINESS_UNIT_ID INNER JOIN
> > LU_ADDRESS_BOOK ON FACT_AR_AGEING.ADDRESS_BOOK_ID => > LU_ADDRESS_BOOK.ADDRESS_BOOK_ID INNER JOIN
> > LU_COMPANY ON FACT_AR_AGEING.COMPANY_ID => > LU_COMPANY.COMPANY_ID
> > GROUP BY
> > LU_BUSINESS_UNIT.BUSINESS_UNIT_ID,LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD,
> > LU_ADDRESS_BOOK.ADDRESS_BOOK_ID,LU_ADDRESS_BOOK.A_ADDRESS_BOOK_SRCCD,
> > LU_COMPANY.COMPANY_ID,LU_COMPANY.COMPANY_SRCCD
> >
> >
> > |--Hash Match(Inner Join,
> > HASH:([LU_BUSINESS_UNIT].[BUSINESS_UNIT_ID])=([IV_test].[BUSINESS_UNIT_ID]))
> > |--Index
> > Scan(OBJECT:([JDE_WH_PREPD].[dbo].[LU_BUSINESS_UNIT].[IX_BUSINESS_UNIT_ID_BUSINESS_UNIT_SRCCD_]))
> > |--Hash Match(Inner Join,
> > HASH:([LU_COMPANY].[COMPANY_ID])=([IV_test].[COMPANY_ID]))
> > |--Clustered Index
> > Scan(OBJECT:([JDE_WH_PREPD].[dbo].[LU_COMPANY].[PK__LU_COMPANY__32EB7E57]))
> > |--Merge Join(Inner Join,
> > MERGE:([LU_ADDRESS_BOOK].[ADDRESS_BOOK_ID])=([IV_test].[ADDRESS_BOOK_ID]),
> > RESIDUAL:([IV_test].[ADDRESS_BOOK_ID]=[LU_ADDRESS_BOOK].[ADDRESS_BOOK_ID]))
> > |--Index
> > Scan(OBJECT:([JDE_WH_PREPD].[dbo].[LU_ADDRESS_BOOK].[LU_ADDRESS_BOOK74]),
> > ORDERED FORWARD)
> > |--Sort(ORDER BY:([IV_test].[ADDRESS_BOOK_ID] ASC))
> > |--Clustered Index
> > Scan(OBJECT:([JDE_WH_PREPD].[dbo].[IV_test].[IX_IV_test]))
> >
> > --
> >
> > As you can see the Optimizer seems to do its job.
> > But if I add a Join to my view The same query doesn't play the game.
> > Unfortunately our front end tool is a ROLAP tool so we cannot hard force
> > the
> > Query to query directly from the view.
> >
> > Thanks
> > Richard
> > "Dan Guzman" wrote:
> >
> >> Thanks for the additional DDL. The create for the following default
> >> constraints and index failed but I don't think this is relevant for this
> >> issue:
> >>
> >> ALTER TABLE [dbo].[FACT_AR_AGEING] ADD
> >> CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_CREDIT_LIMIT] DEFAULT (0) FOR
> >> [AMOUNT_CREDIT_LIMIT],
> >> CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_OPEN] DEFAULT (0) FOR
> >> [AMOUNT_OPEN]
> >> CREATE INDEX [FACT_AR_AGEING29] ON [dbo].[FACT_AR_AGEING]([COMPANY_ID],
> >> [DATE_UPDATED_ID], [BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID],
> >> [AR_INVOICE_ID],
> >> [INV_DOC_TYPE_ID], [WEIGHTED_DAYS_INVOICE]) ON [INDEXES]
> >>
> >> I did some experimenting (sans data) and could only get the view to be
> >> used
> >> by accessing the view directly and adding an optimizer hint.
> >>
> >> SELECT
> >> IV_TEST.BUSINESS_UNIT_ID,
> >> IV_TEST.ADDRESS_BOOK_ID,
> >> IV_TEST.FISCAL_PERIOD_ID,
> >> SUM(IV_TEST.Amount) AS Amount
> >> FROM dbo.IV_test WITH (NOEXPAND)
> >> INNER JOIN dbo.LU_BUSINESS_UNIT ON
> >> IV_TEST.BUSINESS_UNIT_ID = LU_BUSINESS_UNIT.BUSINESS_UNIT_ID
> >> GROUP BY
> >> IV_TEST.BUSINESS_UNIT_ID,
> >> IV_TEST.ADDRESS_BOOK_ID,
> >> IV_TEST.FISCAL_PERIOD_ID
> >>
> >> It looks to me like this is the more optimal plan so I agree with you
> >> that
> >> the optimizer could/should use the view. I assume you see a similar
> >> plan,
> >> even with substantial data loaded?
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote in
> >> message news:3FABE473-511D-479E-A83D-9EAFE1B697C7@.microsoft.com...
> >> > Hi Dan
> >> > I tried to only include columns relavant to the view
> >> > there are three other tables that I didn't include,
> >> > LU_DATE
> >> > if exists (select * from dbo.sysobjects where id => >> > object_id(N'[dbo].[LU_DATE]') and OBJECTPROPERTY(id, N'IsUserTable') => >> > 1)
> >> > drop table [dbo].[LU_DATE]
> >> > GO
> >> >
> >> > CREATE TABLE [dbo].[LU_DATE] (
> >> > [DATE_ID] [int] NOT NULL ,
> >> > [CALENDAR_DATE] [datetime] NULL ,
> >> > [CALENDAR_DATE_DESC] [varchar] (10) COLLATE
> >> > SQL_Latin1_General_CP1_CI_AS
> >> > NULL ,
> >> > [FISCAL_WEEK_ID] [int] NULL ,
> >> > [FISCAL_WEEK_DESC] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
> >> > NULL ,
> >> > [FISCAL_PERIOD_ID] [int] NULL ,
> >> > [FISCAL_PERIOD_DESC] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> >> > NULL ,
> >> > [FISCAL_QUARTER_ID] [int] NULL ,
> >> > [FISCAL_QUARTER_DESC] [varchar] (9) COLLATE
> >> > SQL_Latin1_General_CP1_CI_AS
> >> > NULL ,
> >> > [FISCAL_YEAR_ID] [int] NULL ,
> >> > [CALENDAR_WEEK_ID] [int] NULL ,
> >> > [CALENDAR_WEEK_DESC] [varchar] (30) COLLATE
> >> > SQL_Latin1_General_CP1_CI_AS
> >> > NULL ,
> >> > [CALENDAR_MONTH_ID] [int] NULL ,
> >> > [CALENDAR_MONTH_DESC] [varchar] (9) COLLATE
> >> > SQL_Latin1_General_CP1_CI_AS
> >> > NULL ,
> >> > [CALENDAR_QUARTER_ID] [int] NULL ,
> >> > [CALENDAR_QUARTER_DESC] [varchar] (9) COLLATE
> >> > SQL_Latin1_General_CP1_CI_AS
> >> > NULL ,
> >> > [CALENDAR_YEAR_ID] [int] NULL ,
> >> > [MONTH_ID] [int] NULL ,
> >> > [MONTH_DESC] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> >> > ) ON [PRIMARY]
> >> > GO
> >> >
> >> > ALTER TABLE [dbo].[LU_DATE] WITH NOCHECK ADD
> >> > CONSTRAINT [PK_LU_DATE] PRIMARY KEY CLUSTERED
> >> > (
> >> > [DATE_ID]
> >> > ) ON [PRIMARY]
> >> > GO
> >> >
> >> > CREATE INDEX [LU_DATE91] ON [dbo].[LU_DATE]([DATE_ID],
> >> > [CALENDAR_MONTH_ID]) ON [INDEXES]
> >> > GO
> >> >
> >> > CREATE INDEX [IX_CALENDAR_MONTH_ID] ON
> >> > [dbo].[LU_DATE]([CALENDAR_MONTH_ID]) ON [INDEXES]
> >> > GO
> >> > CREATE INDEX [IX_FISCAL_YEAR_ID_CALENDER_MONTH_ID] ON
> >> > [dbo].[LU_DATE]([FISCAL_YEAR_ID], [CALENDAR_MONTH_ID]) ON [INDEXES]
> >> > GO
> >> > CREATE INDEX [IX_LU_DATE_1] ON [dbo].[LU_DATE]([CALENDAR_DATE]) ON
> >> > [PRIMARY]
> >> > GO
> >> > --
> >> > --LU_BUSINESS_UNIT
> >> > --
> >> > CREATE TABLE [dbo].[LU_BUSINESS_UNIT] (
> >> > [BUSINESS_UNIT_ID] [int] NOT NULL ,
> >> > [BUSINESS_UNIT_SRCCD] [varchar] (12) COLLATE
> >> > SQL_Latin1_General_CP1_CI_AS
> >> > NOT NULL ,
> >> > [BUSINESS_UNIT_DESC] [varchar] (30) COLLATE
> >> > SQL_Latin1_General_CP1_CI_AS
> >> > NULL
> >> > ) ON [PRIMARY]
> >> > GO
> >> >
> >> > ALTER TABLE [dbo].[LU_BUSINESS_UNIT] WITH NOCHECK ADD
> >> > CONSTRAINT [PK__LU_BUSINESS_UNIT__174363E2] PRIMARY KEY CLUSTERED
> >> > (
> >> > [BUSINESS_UNIT_ID]
> >> > ) ON [PRIMARY]
> >> > GO
> >> >
> >> > CREATE INDEX [IX_BISINESS_UNIT_SRCCD] ON
> >> > [dbo].[LU_BUSINESS_UNIT]([BUSINESS_UNIT_SRCCD]) ON [INDEXES]
> >> > GO
> >> > --
> >> > --LU_ADDRESS_BOOK
> >> > --
> >> >
> >> > CREATE TABLE [dbo].[LU_ADDRESS_BOOK] (
> >> > [ADDRESS_BOOK_ID] [int] NOT NULL ,
> >> > [A_ADDRESS_BOOK_SRCCD] [int] NULL ,
> >> > [A_ADDRESS_BOOK_NAME] [varchar] (40) COLLATE
> >> > SQL_Latin1_General_CP1_CI_AS
> >> > NULL
> >> > ) ON [PRIMARY]
> >> > GO
> >> >
> >> > ALTER TABLE [dbo].[LU_ADDRESS_BOOK] WITH NOCHECK ADD
> >> > PRIMARY KEY CLUSTERED
> >> > (
> >> > [ADDRESS_BOOK_ID]
> >> > ) ON [PRIMARY]
> >> > GO
> >> >
> >> > CREATE INDEX [LU_ADDRESS_BOOK74] ON
> >> > [dbo].[LU_ADDRESS_BOOK]([ADDRESS_BOOK_ID], [A_ADDRESS_BOOK_SRCCD]) ON
> >> > [INDEXES]
> >> > GO
> >> >
> >> > CREATE INDEX [IX_ADDRESS_BOOK_SRCCD] ON
> >> > [dbo].[LU_ADDRESS_BOOK]([A_ADDRESS_BOOK_SRCCD]) ON [INDEXES]
> >> > GO
> >> >
> >> >
> >> > I hope this is enough info
> >> >
> >> > cheerz
> >> > Richard
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > "Dan Guzman" wrote:
> >> >
> >> >> Thanks for the DDL but it looks line the LU_DATE table is missing, I
> >> >> also
> >> >> get errors due to the missing columns referenced by the default
> >> >> constraints
> >> >> but those probably aren't relevant for this problem.
> >> >>
> >> >> --
> >> >> Hope this helps.
> >> >>
> >> >> Dan Guzman
> >> >> SQL Server MVP
> >> >>
> >> >> "Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote
> >> >> in
> >> >> message news:326AFFB3-BA04-478B-B19D-A88ED4EF7BFF@.microsoft.com...
> >> >> > Here is the DDL for my Fact Table and the View:
> >> >> > -- Script for FACT_AR_AGEING
> >> >> > CREATE TABLE [dbo].[FACT_AR_AGEING] (
> >> >> > [COMPANY_ID] [int] NOT NULL ,
> >> >> > [DATE_UPDATED_ID] [int] NOT NULL ,
> >> >> > [BUSINESS_UNIT_ID] [int] NOT NULL ,
> >> >> > [ADDRESS_BOOK_ID] [int] NOT NULL ,
> >> >> > [AR_INVOICE_ID] [int] NOT NULL ,
> >> >> > [INV_DOC_TYPE_ID] [int] NOT NULL ,
> >> >> > [AMOUNT_OPEN] [decimal](15, 2) NOT NULL ,
> >> >> > ) ON [PRIMARY]
> >> >> > GO
> >> >> >
> >> >> > ALTER TABLE [dbo].[FACT_AR_AGEING] WITH NOCHECK ADD
> >> >> > CONSTRAINT [PK__FACT_AR_AGEING__05257EFE] PRIMARY KEY CLUSTERED
> >> >> > (
> >> >> > [COMPANY_ID],
> >> >> > [DATE_UPDATED_ID],
> >> >> > [BUSINESS_UNIT_ID],
> >> >> > [ADDRESS_BOOK_ID],
> >> >> > [AR_INVOICE_ID],
> >> >> > [INV_DOC_TYPE_ID]
> >> >> > ) ON [PRIMARY]
> >> >> > GO
> >> >> >
> >> >> > ALTER TABLE [dbo].[FACT_AR_AGEING] ADD
> >> >> > CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_CREDIT_LIMIT] DEFAULT (0) FOR
> >> >> > [AMOUNT_CREDIT_LIMIT],
> >> >> > CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_OPEN] DEFAULT (0) FOR
> >> >> > [AMOUNT_OPEN]
> >> >> >
> >> >> > GO
> >> >> >
> >> >> > CREATE INDEX [FACT_AR_AGEING29] ON
> >> >> > [dbo].[FACT_AR_AGEING]([COMPANY_ID],
> >> >> > [DATE_UPDATED_ID], [BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID],
> >> >> > [AR_INVOICE_ID],|||Thanks for the additional info. I developed a test case where the plan
won't use the view index until the tables are loaded with data so I need to
know data volumes.
Have you tried updating table statistics with FULLSCAN?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote in
message news:6DFA3A52-A7A6-4401-9D17-59A0CA82EF76@.microsoft.com...
> Hi Dan
> The Fact contains about 12 million rows and the BU dimension contains
> about
> 4000 rows
> Thanks
> "Dan Guzman" wrote:
>> > Unfortunately our front end tool is a ROLAP tool so we cannot hard
>> > force
>> > the
>> > Query to query directly from the view.
>> SQL 2005 offers some relief in this regard. In some situations, plan
>> guides
>> can be used to provide more optimal plans in cases where query hints
>> can't
>> be used directly.
>> I'm trying to develop a simplified test case for analysis. Can you tell
>> me
>> approximately how many rows are in your fact table and business unit
>> dimension table?
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote in
>> message news:3BC1F966-42F9-4C84-9B12-791C00C9DD1E@.microsoft.com...
>> > Hi Dan,
>> > Thanks for your assistance.
>> > The constraint has no effect on this issue, I removed it from my table.
>> > here is the execution plan if I remove the Join to LU_DATE from my view
>> > SET SHOWPLAN_ALL on
>> >
>> > SELECT LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD,
>> > SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount,
>> > LU_ADDRESS_BOOK.A_ADDRESS_BOOK_SRCCD,
>> > LU_COMPANY.COMPANY_SRCCD
>> > FROM FACT_AR_AGEING INNER JOIN
>> > LU_BUSINESS_UNIT ON
>> > FACT_AR_AGEING.BUSINESS_UNIT_ID =>> > LU_BUSINESS_UNIT.BUSINESS_UNIT_ID INNER JOIN
>> > LU_ADDRESS_BOOK ON FACT_AR_AGEING.ADDRESS_BOOK_ID
>> > =>> > LU_ADDRESS_BOOK.ADDRESS_BOOK_ID INNER JOIN
>> > LU_COMPANY ON FACT_AR_AGEING.COMPANY_ID =>> > LU_COMPANY.COMPANY_ID
>> > GROUP BY
>> > LU_BUSINESS_UNIT.BUSINESS_UNIT_ID,LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD,
>> > LU_ADDRESS_BOOK.ADDRESS_BOOK_ID,LU_ADDRESS_BOOK.A_ADDRESS_BOOK_SRCCD,
>> > LU_COMPANY.COMPANY_ID,LU_COMPANY.COMPANY_SRCCD
>> >
>> >
>> > |--Hash Match(Inner Join,
>> > HASH:([LU_BUSINESS_UNIT].[BUSINESS_UNIT_ID])=([IV_test].[BUSINESS_UNIT_ID]))
>> > |--Index
>> > Scan(OBJECT:([JDE_WH_PREPD].[dbo].[LU_BUSINESS_UNIT].[IX_BUSINESS_UNIT_ID_BUSINESS_UNIT_SRCCD_]))
>> > |--Hash Match(Inner Join,
>> > HASH:([LU_COMPANY].[COMPANY_ID])=([IV_test].[COMPANY_ID]))
>> > |--Clustered Index
>> > Scan(OBJECT:([JDE_WH_PREPD].[dbo].[LU_COMPANY].[PK__LU_COMPANY__32EB7E57]))
>> > |--Merge Join(Inner Join,
>> > MERGE:([LU_ADDRESS_BOOK].[ADDRESS_BOOK_ID])=([IV_test].[ADDRESS_BOOK_ID]),
>> > RESIDUAL:([IV_test].[ADDRESS_BOOK_ID]=[LU_ADDRESS_BOOK].[ADDRESS_BOOK_ID]))
>> > |--Index
>> > Scan(OBJECT:([JDE_WH_PREPD].[dbo].[LU_ADDRESS_BOOK].[LU_ADDRESS_BOOK74]),
>> > ORDERED FORWARD)
>> > |--Sort(ORDER BY:([IV_test].[ADDRESS_BOOK_ID] ASC))
>> > |--Clustered Index
>> > Scan(OBJECT:([JDE_WH_PREPD].[dbo].[IV_test].[IX_IV_test]))
>> >
>> > --
>> >
>> > As you can see the Optimizer seems to do its job.
>> > But if I add a Join to my view The same query doesn't play the game.
>> > Unfortunately our front end tool is a ROLAP tool so we cannot hard
>> > force
>> > the
>> > Query to query directly from the view.
>> >
>> > Thanks
>> > Richard
>> > "Dan Guzman" wrote:
>> >
>> >> Thanks for the additional DDL. The create for the following default
>> >> constraints and index failed but I don't think this is relevant for
>> >> this
>> >> issue:
>> >>
>> >> ALTER TABLE [dbo].[FACT_AR_AGEING] ADD
>> >> CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_CREDIT_LIMIT] DEFAULT (0) FOR
>> >> [AMOUNT_CREDIT_LIMIT],
>> >> CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_OPEN] DEFAULT (0) FOR
>> >> [AMOUNT_OPEN]
>> >> CREATE INDEX [FACT_AR_AGEING29] ON
>> >> [dbo].[FACT_AR_AGEING]([COMPANY_ID],
>> >> [DATE_UPDATED_ID], [BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID],
>> >> [AR_INVOICE_ID],
>> >> [INV_DOC_TYPE_ID], [WEIGHTED_DAYS_INVOICE]) ON [INDEXES]
>> >>
>> >> I did some experimenting (sans data) and could only get the view to be
>> >> used
>> >> by accessing the view directly and adding an optimizer hint.
>> >>
>> >> SELECT
>> >> IV_TEST.BUSINESS_UNIT_ID,
>> >> IV_TEST.ADDRESS_BOOK_ID,
>> >> IV_TEST.FISCAL_PERIOD_ID,
>> >> SUM(IV_TEST.Amount) AS Amount
>> >> FROM dbo.IV_test WITH (NOEXPAND)
>> >> INNER JOIN dbo.LU_BUSINESS_UNIT ON
>> >> IV_TEST.BUSINESS_UNIT_ID = LU_BUSINESS_UNIT.BUSINESS_UNIT_ID
>> >> GROUP BY
>> >> IV_TEST.BUSINESS_UNIT_ID,
>> >> IV_TEST.ADDRESS_BOOK_ID,
>> >> IV_TEST.FISCAL_PERIOD_ID
>> >>
>> >> It looks to me like this is the more optimal plan so I agree with you
>> >> that
>> >> the optimizer could/should use the view. I assume you see a similar
>> >> plan,
>> >> even with substantial data loaded?
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote
>> >> in
>> >> message news:3FABE473-511D-479E-A83D-9EAFE1B697C7@.microsoft.com...
>> >> > Hi Dan
>> >> > I tried to only include columns relavant to the view
>> >> > there are three other tables that I didn't include,
>> >> > LU_DATE
>> >> > if exists (select * from dbo.sysobjects where id =>> >> > object_id(N'[dbo].[LU_DATE]') and OBJECTPROPERTY(id, N'IsUserTable')
>> >> > =>> >> > 1)
>> >> > drop table [dbo].[LU_DATE]
>> >> > GO
>> >> >
>> >> > CREATE TABLE [dbo].[LU_DATE] (
>> >> > [DATE_ID] [int] NOT NULL ,
>> >> > [CALENDAR_DATE] [datetime] NULL ,
>> >> > [CALENDAR_DATE_DESC] [varchar] (10) COLLATE
>> >> > SQL_Latin1_General_CP1_CI_AS
>> >> > NULL ,
>> >> > [FISCAL_WEEK_ID] [int] NULL ,
>> >> > [FISCAL_WEEK_DESC] [varchar] (10) COLLATE
>> >> > SQL_Latin1_General_CP1_CI_AS
>> >> > NULL ,
>> >> > [FISCAL_PERIOD_ID] [int] NULL ,
>> >> > [FISCAL_PERIOD_DESC] [varchar] (9) COLLATE
>> >> > SQL_Latin1_General_CP1_CI_AS
>> >> > NULL ,
>> >> > [FISCAL_QUARTER_ID] [int] NULL ,
>> >> > [FISCAL_QUARTER_DESC] [varchar] (9) COLLATE
>> >> > SQL_Latin1_General_CP1_CI_AS
>> >> > NULL ,
>> >> > [FISCAL_YEAR_ID] [int] NULL ,
>> >> > [CALENDAR_WEEK_ID] [int] NULL ,
>> >> > [CALENDAR_WEEK_DESC] [varchar] (30) COLLATE
>> >> > SQL_Latin1_General_CP1_CI_AS
>> >> > NULL ,
>> >> > [CALENDAR_MONTH_ID] [int] NULL ,
>> >> > [CALENDAR_MONTH_DESC] [varchar] (9) COLLATE
>> >> > SQL_Latin1_General_CP1_CI_AS
>> >> > NULL ,
>> >> > [CALENDAR_QUARTER_ID] [int] NULL ,
>> >> > [CALENDAR_QUARTER_DESC] [varchar] (9) COLLATE
>> >> > SQL_Latin1_General_CP1_CI_AS
>> >> > NULL ,
>> >> > [CALENDAR_YEAR_ID] [int] NULL ,
>> >> > [MONTH_ID] [int] NULL ,
>> >> > [MONTH_DESC] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
>> >> > NULL
>> >> > ) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > ALTER TABLE [dbo].[LU_DATE] WITH NOCHECK ADD
>> >> > CONSTRAINT [PK_LU_DATE] PRIMARY KEY CLUSTERED
>> >> > (
>> >> > [DATE_ID]
>> >> > ) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > CREATE INDEX [LU_DATE91] ON [dbo].[LU_DATE]([DATE_ID],
>> >> > [CALENDAR_MONTH_ID]) ON [INDEXES]
>> >> > GO
>> >> >
>> >> > CREATE INDEX [IX_CALENDAR_MONTH_ID] ON
>> >> > [dbo].[LU_DATE]([CALENDAR_MONTH_ID]) ON [INDEXES]
>> >> > GO
>> >> > CREATE INDEX [IX_FISCAL_YEAR_ID_CALENDER_MONTH_ID] ON
>> >> > [dbo].[LU_DATE]([FISCAL_YEAR_ID], [CALENDAR_MONTH_ID]) ON [INDEXES]
>> >> > GO
>> >> > CREATE INDEX [IX_LU_DATE_1] ON [dbo].[LU_DATE]([CALENDAR_DATE]) ON
>> >> > [PRIMARY]
>> >> > GO
>> >> > --
>> >> > --LU_BUSINESS_UNIT
>> >> > --
>> >> > CREATE TABLE [dbo].[LU_BUSINESS_UNIT] (
>> >> > [BUSINESS_UNIT_ID] [int] NOT NULL ,
>> >> > [BUSINESS_UNIT_SRCCD] [varchar] (12) COLLATE
>> >> > SQL_Latin1_General_CP1_CI_AS
>> >> > NOT NULL ,
>> >> > [BUSINESS_UNIT_DESC] [varchar] (30) COLLATE
>> >> > SQL_Latin1_General_CP1_CI_AS
>> >> > NULL
>> >> > ) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > ALTER TABLE [dbo].[LU_BUSINESS_UNIT] WITH NOCHECK ADD
>> >> > CONSTRAINT [PK__LU_BUSINESS_UNIT__174363E2] PRIMARY KEY CLUSTERED
>> >> > (
>> >> > [BUSINESS_UNIT_ID]
>> >> > ) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > CREATE INDEX [IX_BISINESS_UNIT_SRCCD] ON
>> >> > [dbo].[LU_BUSINESS_UNIT]([BUSINESS_UNIT_SRCCD]) ON [INDEXES]
>> >> > GO
>> >> > --
>> >> > --LU_ADDRESS_BOOK
>> >> > --
>> >> >
>> >> > CREATE TABLE [dbo].[LU_ADDRESS_BOOK] (
>> >> > [ADDRESS_BOOK_ID] [int] NOT NULL ,
>> >> > [A_ADDRESS_BOOK_SRCCD] [int] NULL ,
>> >> > [A_ADDRESS_BOOK_NAME] [varchar] (40) COLLATE
>> >> > SQL_Latin1_General_CP1_CI_AS
>> >> > NULL
>> >> > ) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > ALTER TABLE [dbo].[LU_ADDRESS_BOOK] WITH NOCHECK ADD
>> >> > PRIMARY KEY CLUSTERED
>> >> > (
>> >> > [ADDRESS_BOOK_ID]
>> >> > ) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > CREATE INDEX [LU_ADDRESS_BOOK74] ON
>> >> > [dbo].[LU_ADDRESS_BOOK]([ADDRESS_BOOK_ID], [A_ADDRESS_BOOK_SRCCD])
>> >> > ON
>> >> > [INDEXES]
>> >> > GO
>> >> >
>> >> > CREATE INDEX [IX_ADDRESS_BOOK_SRCCD] ON
>> >> > [dbo].[LU_ADDRESS_BOOK]([A_ADDRESS_BOOK_SRCCD]) ON [INDEXES]
>> >> > GO
>> >> >
>> >> >
>> >> > I hope this is enough info
>> >> >
>> >> > cheerz
>> >> > Richard
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > "Dan Guzman" wrote:
>> >> >
>> >> >> Thanks for the DDL but it looks line the LU_DATE table is missing,
>> >> >> I
>> >> >> also
>> >> >> get errors due to the missing columns referenced by the default
>> >> >> constraints
>> >> >> but those probably aren't relevant for this problem.
>> >> >>
>> >> >> --
>> >> >> Hope this helps.
>> >> >>
>> >> >> Dan Guzman
>> >> >> SQL Server MVP
>> >> >>
>> >> >> "Richard Greyling" <RichardGreyling@.discussions.microsoft.com>
>> >> >> wrote
>> >> >> in
>> >> >> message news:326AFFB3-BA04-478B-B19D-A88ED4EF7BFF@.microsoft.com...
>> >> >> > Here is the DDL for my Fact Table and the View:
>> >> >> > -- Script for FACT_AR_AGEING
>> >> >> > CREATE TABLE [dbo].[FACT_AR_AGEING] (
>> >> >> > [COMPANY_ID] [int] NOT NULL ,
>> >> >> > [DATE_UPDATED_ID] [int] NOT NULL ,
>> >> >> > [BUSINESS_UNIT_ID] [int] NOT NULL ,
>> >> >> > [ADDRESS_BOOK_ID] [int] NOT NULL ,
>> >> >> > [AR_INVOICE_ID] [int] NOT NULL ,
>> >> >> > [INV_DOC_TYPE_ID] [int] NOT NULL ,
>> >> >> > [AMOUNT_OPEN] [decimal](15, 2) NOT NULL ,
>> >> >> > ) ON [PRIMARY]
>> >> >> > GO
>> >> >> >
>> >> >> > ALTER TABLE [dbo].[FACT_AR_AGEING] WITH NOCHECK ADD
>> >> >> > CONSTRAINT [PK__FACT_AR_AGEING__05257EFE] PRIMARY KEY CLUSTERED
>> >> >> > (
>> >> >> > [COMPANY_ID],
>> >> >> > [DATE_UPDATED_ID],
>> >> >> > [BUSINESS_UNIT_ID],
>> >> >> > [ADDRESS_BOOK_ID],
>> >> >> > [AR_INVOICE_ID],
>> >> >> > [INV_DOC_TYPE_ID]
>> >> >> > ) ON [PRIMARY]
>> >> >> > GO
>> >> >> >
>> >> >> > ALTER TABLE [dbo].[FACT_AR_AGEING] ADD
>> >> >> > CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_CREDIT_LIMIT] DEFAULT (0)
>> >> >> > FOR
>> >> >> > [AMOUNT_CREDIT_LIMIT],
>> >> >> > CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_OPEN] DEFAULT (0) FOR
>> >> >> > [AMOUNT_OPEN]
>> >> >> >
>> >> >> > GO
>> >> >> >
>> >> >> > CREATE INDEX [FACT_AR_AGEING29] ON
>> >> >> > [dbo].[FACT_AR_AGEING]([COMPANY_ID],
>> >> >> > [DATE_UPDATED_ID], [BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID],
>> >> >> > [AR_INVOICE_ID],|||Hi, Richard.
After some experimentation, I couldn't get the indexed view to be used by
the second query. However, I was able to create an additional indexed view
including the second join and the second query plan used that indexed view.
Perhaps that workaround is acceptable in your environment.
I'll let you know if I can find out more information. I believe the
optimizer should use the original indexed view.
CREATE VIEW dbo.IV_test2
WITH SCHEMABINDING
AS
SELECT
FACT_AR_AGEING.BUSINESS_UNIT_ID,
FACT_AR_AGEING.ADDRESS_BOOK_ID,
LU_DATE.FISCAL_PERIOD_ID,
SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount,
COUNT_BIG(*) AS CountBig
FROM dbo.FACT_AR_AGEING
INNER JOIN dbo.LU_DATE ON
FACT_AR_AGEING.DATE_UPDATED_ID = LU_DATE.DATE_ID
INNER JOIN dbo.LU_BUSINESS_UNIT ON
FACT_AR_AGEING.BUSINESS_UNIT_ID = LU_BUSINESS_UNIT.BUSINESS_UNIT_ID
GROUP BY
FACT_AR_AGEING.BUSINESS_UNIT_ID,
FACT_AR_AGEING.ADDRESS_BOOK_ID,
LU_DATE.FISCAL_PERIOD_ID
GO
CREATE UNIQUE CLUSTERED INDEX [IX_Main] ON
[dbo].[IV_test2](
[BUSINESS_UNIT_ID],
[ADDRESS_BOOK_ID] DESC ,
[FISCAL_PERIOD_ID])
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote in
message news:6DFA3A52-A7A6-4401-9D17-59A0CA82EF76@.microsoft.com...
> Hi Dan
> The Fact contains about 12 million rows and the BU dimension contains
> about
> 4000 rows
> Thanks
> "Dan Guzman" wrote:
>> > Unfortunately our front end tool is a ROLAP tool so we cannot hard
>> > force
>> > the
>> > Query to query directly from the view.
>> SQL 2005 offers some relief in this regard. In some situations, plan
>> guides
>> can be used to provide more optimal plans in cases where query hints
>> can't
>> be used directly.
>> I'm trying to develop a simplified test case for analysis. Can you tell
>> me
>> approximately how many rows are in your fact table and business unit
>> dimension table?
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote in
>> message news:3BC1F966-42F9-4C84-9B12-791C00C9DD1E@.microsoft.com...
>> > Hi Dan,
>> > Thanks for your assistance.
>> > The constraint has no effect on this issue, I removed it from my table.
>> > here is the execution plan if I remove the Join to LU_DATE from my view
>> > SET SHOWPLAN_ALL on
>> >
>> > SELECT LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD,
>> > SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount,
>> > LU_ADDRESS_BOOK.A_ADDRESS_BOOK_SRCCD,
>> > LU_COMPANY.COMPANY_SRCCD
>> > FROM FACT_AR_AGEING INNER JOIN
>> > LU_BUSINESS_UNIT ON
>> > FACT_AR_AGEING.BUSINESS_UNIT_ID =>> > LU_BUSINESS_UNIT.BUSINESS_UNIT_ID INNER JOIN
>> > LU_ADDRESS_BOOK ON FACT_AR_AGEING.ADDRESS_BOOK_ID
>> > =>> > LU_ADDRESS_BOOK.ADDRESS_BOOK_ID INNER JOIN
>> > LU_COMPANY ON FACT_AR_AGEING.COMPANY_ID =>> > LU_COMPANY.COMPANY_ID
>> > GROUP BY
>> > LU_BUSINESS_UNIT.BUSINESS_UNIT_ID,LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD,
>> > LU_ADDRESS_BOOK.ADDRESS_BOOK_ID,LU_ADDRESS_BOOK.A_ADDRESS_BOOK_SRCCD,
>> > LU_COMPANY.COMPANY_ID,LU_COMPANY.COMPANY_SRCCD
>> >
>> >
>> > |--Hash Match(Inner Join,
>> > HASH:([LU_BUSINESS_UNIT].[BUSINESS_UNIT_ID])=([IV_test].[BUSINESS_UNIT_ID]))
>> > |--Index
>> > Scan(OBJECT:([JDE_WH_PREPD].[dbo].[LU_BUSINESS_UNIT].[IX_BUSINESS_UNIT_ID_BUSINESS_UNIT_SRCCD_]))
>> > |--Hash Match(Inner Join,
>> > HASH:([LU_COMPANY].[COMPANY_ID])=([IV_test].[COMPANY_ID]))
>> > |--Clustered Index
>> > Scan(OBJECT:([JDE_WH_PREPD].[dbo].[LU_COMPANY].[PK__LU_COMPANY__32EB7E57]))
>> > |--Merge Join(Inner Join,
>> > MERGE:([LU_ADDRESS_BOOK].[ADDRESS_BOOK_ID])=([IV_test].[ADDRESS_BOOK_ID]),
>> > RESIDUAL:([IV_test].[ADDRESS_BOOK_ID]=[LU_ADDRESS_BOOK].[ADDRESS_BOOK_ID]))
>> > |--Index
>> > Scan(OBJECT:([JDE_WH_PREPD].[dbo].[LU_ADDRESS_BOOK].[LU_ADDRESS_BOOK74]),
>> > ORDERED FORWARD)
>> > |--Sort(ORDER BY:([IV_test].[ADDRESS_BOOK_ID] ASC))
>> > |--Clustered Index
>> > Scan(OBJECT:([JDE_WH_PREPD].[dbo].[IV_test].[IX_IV_test]))
>> >
>> > --
>> >
>> > As you can see the Optimizer seems to do its job.
>> > But if I add a Join to my view The same query doesn't play the game.
>> > Unfortunately our front end tool is a ROLAP tool so we cannot hard
>> > force
>> > the
>> > Query to query directly from the view.
>> >
>> > Thanks
>> > Richard
>> > "Dan Guzman" wrote:
>> >
>> >> Thanks for the additional DDL. The create for the following default
>> >> constraints and index failed but I don't think this is relevant for
>> >> this
>> >> issue:
>> >>
>> >> ALTER TABLE [dbo].[FACT_AR_AGEING] ADD
>> >> CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_CREDIT_LIMIT] DEFAULT (0) FOR
>> >> [AMOUNT_CREDIT_LIMIT],
>> >> CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_OPEN] DEFAULT (0) FOR
>> >> [AMOUNT_OPEN]
>> >> CREATE INDEX [FACT_AR_AGEING29] ON
>> >> [dbo].[FACT_AR_AGEING]([COMPANY_ID],
>> >> [DATE_UPDATED_ID], [BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID],
>> >> [AR_INVOICE_ID],
>> >> [INV_DOC_TYPE_ID], [WEIGHTED_DAYS_INVOICE]) ON [INDEXES]
>> >>
>> >> I did some experimenting (sans data) and could only get the view to be
>> >> used
>> >> by accessing the view directly and adding an optimizer hint.
>> >>
>> >> SELECT
>> >> IV_TEST.BUSINESS_UNIT_ID,
>> >> IV_TEST.ADDRESS_BOOK_ID,
>> >> IV_TEST.FISCAL_PERIOD_ID,
>> >> SUM(IV_TEST.Amount) AS Amount
>> >> FROM dbo.IV_test WITH (NOEXPAND)
>> >> INNER JOIN dbo.LU_BUSINESS_UNIT ON
>> >> IV_TEST.BUSINESS_UNIT_ID = LU_BUSINESS_UNIT.BUSINESS_UNIT_ID
>> >> GROUP BY
>> >> IV_TEST.BUSINESS_UNIT_ID,
>> >> IV_TEST.ADDRESS_BOOK_ID,
>> >> IV_TEST.FISCAL_PERIOD_ID
>> >>
>> >> It looks to me like this is the more optimal plan so I agree with you
>> >> that
>> >> the optimizer could/should use the view. I assume you see a similar
>> >> plan,
>> >> even with substantial data loaded?
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote
>> >> in
>> >> message news:3FABE473-511D-479E-A83D-9EAFE1B697C7@.microsoft.com...
>> >> > Hi Dan
>> >> > I tried to only include columns relavant to the view
>> >> > there are three other tables that I didn't include,
>> >> > LU_DATE
>> >> > if exists (select * from dbo.sysobjects where id =>> >> > object_id(N'[dbo].[LU_DATE]') and OBJECTPROPERTY(id, N'IsUserTable')
>> >> > =>> >> > 1)
>> >> > drop table [dbo].[LU_DATE]
>> >> > GO
>> >> >
>> >> > CREATE TABLE [dbo].[LU_DATE] (
>> >> > [DATE_ID] [int] NOT NULL ,
>> >> > [CALENDAR_DATE] [datetime] NULL ,
>> >> > [CALENDAR_DATE_DESC] [varchar] (10) COLLATE
>> >> > SQL_Latin1_General_CP1_CI_AS
>> >> > NULL ,
>> >> > [FISCAL_WEEK_ID] [int] NULL ,
>> >> > [FISCAL_WEEK_DESC] [varchar] (10) COLLATE
>> >> > SQL_Latin1_General_CP1_CI_AS
>> >> > NULL ,
>> >> > [FISCAL_PERIOD_ID] [int] NULL ,
>> >> > [FISCAL_PERIOD_DESC] [varchar] (9) COLLATE
>> >> > SQL_Latin1_General_CP1_CI_AS
>> >> > NULL ,
>> >> > [FISCAL_QUARTER_ID] [int] NULL ,
>> >> > [FISCAL_QUARTER_DESC] [varchar] (9) COLLATE
>> >> > SQL_Latin1_General_CP1_CI_AS
>> >> > NULL ,
>> >> > [FISCAL_YEAR_ID] [int] NULL ,
>> >> > [CALENDAR_WEEK_ID] [int] NULL ,
>> >> > [CALENDAR_WEEK_DESC] [varchar] (30) COLLATE
>> >> > SQL_Latin1_General_CP1_CI_AS
>> >> > NULL ,
>> >> > [CALENDAR_MONTH_ID] [int] NULL ,
>> >> > [CALENDAR_MONTH_DESC] [varchar] (9) COLLATE
>> >> > SQL_Latin1_General_CP1_CI_AS
>> >> > NULL ,
>> >> > [CALENDAR_QUARTER_ID] [int] NULL ,
>> >> > [CALENDAR_QUARTER_DESC] [varchar] (9) COLLATE
>> >> > SQL_Latin1_General_CP1_CI_AS
>> >> > NULL ,
>> >> > [CALENDAR_YEAR_ID] [int] NULL ,
>> >> > [MONTH_ID] [int] NULL ,
>> >> > [MONTH_DESC] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
>> >> > NULL
>> >> > ) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > ALTER TABLE [dbo].[LU_DATE] WITH NOCHECK ADD
>> >> > CONSTRAINT [PK_LU_DATE] PRIMARY KEY CLUSTERED
>> >> > (
>> >> > [DATE_ID]
>> >> > ) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > CREATE INDEX [LU_DATE91] ON [dbo].[LU_DATE]([DATE_ID],
>> >> > [CALENDAR_MONTH_ID]) ON [INDEXES]
>> >> > GO
>> >> >
>> >> > CREATE INDEX [IX_CALENDAR_MONTH_ID] ON
>> >> > [dbo].[LU_DATE]([CALENDAR_MONTH_ID]) ON [INDEXES]
>> >> > GO
>> >> > CREATE INDEX [IX_FISCAL_YEAR_ID_CALENDER_MONTH_ID] ON
>> >> > [dbo].[LU_DATE]([FISCAL_YEAR_ID], [CALENDAR_MONTH_ID]) ON [INDEXES]
>> >> > GO
>> >> > CREATE INDEX [IX_LU_DATE_1] ON [dbo].[LU_DATE]([CALENDAR_DATE]) ON
>> >> > [PRIMARY]
>> >> > GO
>> >> > --
>> >> > --LU_BUSINESS_UNIT
>> >> > --
>> >> > CREATE TABLE [dbo].[LU_BUSINESS_UNIT] (
>> >> > [BUSINESS_UNIT_ID] [int] NOT NULL ,
>> >> > [BUSINESS_UNIT_SRCCD] [varchar] (12) COLLATE
>> >> > SQL_Latin1_General_CP1_CI_AS
>> >> > NOT NULL ,
>> >> > [BUSINESS_UNIT_DESC] [varchar] (30) COLLATE
>> >> > SQL_Latin1_General_CP1_CI_AS
>> >> > NULL
>> >> > ) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > ALTER TABLE [dbo].[LU_BUSINESS_UNIT] WITH NOCHECK ADD
>> >> > CONSTRAINT [PK__LU_BUSINESS_UNIT__174363E2] PRIMARY KEY CLUSTERED
>> >> > (
>> >> > [BUSINESS_UNIT_ID]
>> >> > ) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > CREATE INDEX [IX_BISINESS_UNIT_SRCCD] ON
>> >> > [dbo].[LU_BUSINESS_UNIT]([BUSINESS_UNIT_SRCCD]) ON [INDEXES]
>> >> > GO
>> >> > --
>> >> > --LU_ADDRESS_BOOK
>> >> > --
>> >> >
>> >> > CREATE TABLE [dbo].[LU_ADDRESS_BOOK] (
>> >> > [ADDRESS_BOOK_ID] [int] NOT NULL ,
>> >> > [A_ADDRESS_BOOK_SRCCD] [int] NULL ,
>> >> > [A_ADDRESS_BOOK_NAME] [varchar] (40) COLLATE
>> >> > SQL_Latin1_General_CP1_CI_AS
>> >> > NULL
>> >> > ) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > ALTER TABLE [dbo].[LU_ADDRESS_BOOK] WITH NOCHECK ADD
>> >> > PRIMARY KEY CLUSTERED
>> >> > (
>> >> > [ADDRESS_BOOK_ID]
>> >> > ) ON [PRIMARY]
>> >> > GO
>> >> >
>> >> > CREATE INDEX [LU_ADDRESS_BOOK74] ON
>> >> > [dbo].[LU_ADDRESS_BOOK]([ADDRESS_BOOK_ID], [A_ADDRESS_BOOK_SRCCD])
>> >> > ON
>> >> > [INDEXES]
>> >> > GO
>> >> >
>> >> > CREATE INDEX [IX_ADDRESS_BOOK_SRCCD] ON
>> >> > [dbo].[LU_ADDRESS_BOOK]([A_ADDRESS_BOOK_SRCCD]) ON [INDEXES]
>> >> > GO
>> >> >
>> >> >
>> >> > I hope this is enough info
>> >> >
>> >> > cheerz
>> >> > Richard
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > "Dan Guzman" wrote:
>> >> >
>> >> >> Thanks for the DDL but it looks line the LU_DATE table is missing,
>> >> >> I
>> >> >> also
>> >> >> get errors due to the missing columns referenced by the default
>> >> >> constraints
>> >> >> but those probably aren't relevant for this problem.
>> >> >>
>> >> >> --
>> >> >> Hope this helps.
>> >> >>
>> >> >> Dan Guzman
>> >> >> SQL Server MVP
>> >> >>
>> >> >> "Richard Greyling" <RichardGreyling@.discussions.microsoft.com>
>> >> >> wrote
>> >> >> in
>> >> >> message news:326AFFB3-BA04-478B-B19D-A88ED4EF7BFF@.microsoft.com...
>> >> >> > Here is the DDL for my Fact Table and the View:
>> >> >> > -- Script for FACT_AR_AGEING
>> >> >> > CREATE TABLE [dbo].[FACT_AR_AGEING] (
>> >> >> > [COMPANY_ID] [int] NOT NULL ,
>> >> >> > [DATE_UPDATED_ID] [int] NOT NULL ,
>> >> >> > [BUSINESS_UNIT_ID] [int] NOT NULL ,
>> >> >> > [ADDRESS_BOOK_ID] [int] NOT NULL ,
>> >> >> > [AR_INVOICE_ID] [int] NOT NULL ,
>> >> >> > [INV_DOC_TYPE_ID] [int] NOT NULL ,
>> >> >> > [AMOUNT_OPEN] [decimal](15, 2) NOT NULL ,
>> >> >> > ) ON [PRIMARY]
>> >> >> > GO
>> >> >> >
>> >> >> > ALTER TABLE [dbo].[FACT_AR_AGEING] WITH NOCHECK ADD
>> >> >> > CONSTRAINT [PK__FACT_AR_AGEING__05257EFE] PRIMARY KEY CLUSTERED
>> >> >> > (
>> >> >> > [COMPANY_ID],
>> >> >> > [DATE_UPDATED_ID],
>> >> >> > [BUSINESS_UNIT_ID],
>> >> >> > [ADDRESS_BOOK_ID],
>> >> >> > [AR_INVOICE_ID],
>> >> >> > [INV_DOC_TYPE_ID]
>> >> >> > ) ON [PRIMARY]
>> >> >> > GO
>> >> >> >
>> >> >> > ALTER TABLE [dbo].[FACT_AR_AGEING] ADD
>> >> >> > CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_CREDIT_LIMIT] DEFAULT (0)
>> >> >> > FOR
>> >> >> > [AMOUNT_CREDIT_LIMIT],
>> >> >> > CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_OPEN] DEFAULT (0) FOR
>> >> >> > [AMOUNT_OPEN]
>> >> >> >
>> >> >> > GO
>> >> >> >
>> >> >> > CREATE INDEX [FACT_AR_AGEING29] ON
>> >> >> > [dbo].[FACT_AR_AGEING]([COMPANY_ID],
>> >> >> > [DATE_UPDATED_ID], [BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID],
>> >> >> > [AR_INVOICE_ID],|||Hi Dan,
Thanks for your assistance, the only way I could get any joy was to remove
all joins from the view. The difficulty with the view that you created is
that you can still only join one other table to the view in your query, and
once you add a table to the view definition, all columns that will be used in
that table must also be added to the join (i.e. If you add
BUSINESS_UNIT_SRCCD, and/or BUSINESS_UNIT_DESC to the Query, they must be
included in the view definition) for the optimizer to use the view.
SELECT FACT_AR_AGEING.BUSINESS_UNIT_ID, FACT_AR_AGEING.ADDRESS_BOOK_ID,
LU_DATE.FISCAL_PERIOD_ID,
SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount,
LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD
FROM FACT_AR_AGEING INNER JOIN
LU_DATE ON FACT_AR_AGEING.DATE_UPDATED_ID =LU_DATE.DATE_ID INNER JOIN
LU_BUSINESS_UNIT ON FACT_AR_AGEING.BUSINESS_UNIT_ID =LU_BUSINESS_UNIT.BUSINESS_UNIT_ID
GROUP BY FACT_AR_AGEING.BUSINESS_UNIT_ID, FACT_AR_AGEING.ADDRESS_BOOK_ID,
LU_DATE.FISCAL_PERIOD_ID,
LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD
Do you think SQL2005 will solve this issue?
"Dan Guzman" wrote:
> Hi, Richard.
> After some experimentation, I couldn't get the indexed view to be used by
> the second query. However, I was able to create an additional indexed view
> including the second join and the second query plan used that indexed view.
> Perhaps that workaround is acceptable in your environment.
> I'll let you know if I can find out more information. I believe the
> optimizer should use the original indexed view.
> CREATE VIEW dbo.IV_test2
> WITH SCHEMABINDING
> AS
> SELECT
> FACT_AR_AGEING.BUSINESS_UNIT_ID,
> FACT_AR_AGEING.ADDRESS_BOOK_ID,
> LU_DATE.FISCAL_PERIOD_ID,
> SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount,
> COUNT_BIG(*) AS CountBig
> FROM dbo.FACT_AR_AGEING
> INNER JOIN dbo.LU_DATE ON
> FACT_AR_AGEING.DATE_UPDATED_ID = LU_DATE.DATE_ID
> INNER JOIN dbo.LU_BUSINESS_UNIT ON
> FACT_AR_AGEING.BUSINESS_UNIT_ID = LU_BUSINESS_UNIT.BUSINESS_UNIT_ID
> GROUP BY
> FACT_AR_AGEING.BUSINESS_UNIT_ID,
> FACT_AR_AGEING.ADDRESS_BOOK_ID,
> LU_DATE.FISCAL_PERIOD_ID
> GO
> CREATE UNIQUE CLUSTERED INDEX [IX_Main] ON
> [dbo].[IV_test2](
> [BUSINESS_UNIT_ID],
> [ADDRESS_BOOK_ID] DESC ,
> [FISCAL_PERIOD_ID])
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote in
> message news:6DFA3A52-A7A6-4401-9D17-59A0CA82EF76@.microsoft.com...
> > Hi Dan
> > The Fact contains about 12 million rows and the BU dimension contains
> > about
> > 4000 rows
> >
> > Thanks
> >
> > "Dan Guzman" wrote:
> >
> >> > Unfortunately our front end tool is a ROLAP tool so we cannot hard
> >> > force
> >> > the
> >> > Query to query directly from the view.
> >>
> >> SQL 2005 offers some relief in this regard. In some situations, plan
> >> guides
> >> can be used to provide more optimal plans in cases where query hints
> >> can't
> >> be used directly.
> >>
> >> I'm trying to develop a simplified test case for analysis. Can you tell
> >> me
> >> approximately how many rows are in your fact table and business unit
> >> dimension table?
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote in
> >> message news:3BC1F966-42F9-4C84-9B12-791C00C9DD1E@.microsoft.com...
> >> > Hi Dan,
> >> > Thanks for your assistance.
> >> > The constraint has no effect on this issue, I removed it from my table.
> >> > here is the execution plan if I remove the Join to LU_DATE from my view
> >> > SET SHOWPLAN_ALL on
> >> >
> >> > SELECT LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD,
> >> > SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount,
> >> > LU_ADDRESS_BOOK.A_ADDRESS_BOOK_SRCCD,
> >> > LU_COMPANY.COMPANY_SRCCD
> >> > FROM FACT_AR_AGEING INNER JOIN
> >> > LU_BUSINESS_UNIT ON
> >> > FACT_AR_AGEING.BUSINESS_UNIT_ID => >> > LU_BUSINESS_UNIT.BUSINESS_UNIT_ID INNER JOIN
> >> > LU_ADDRESS_BOOK ON FACT_AR_AGEING.ADDRESS_BOOK_ID
> >> > => >> > LU_ADDRESS_BOOK.ADDRESS_BOOK_ID INNER JOIN
> >> > LU_COMPANY ON FACT_AR_AGEING.COMPANY_ID => >> > LU_COMPANY.COMPANY_ID
> >> > GROUP BY
> >> > LU_BUSINESS_UNIT.BUSINESS_UNIT_ID,LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD,
> >> > LU_ADDRESS_BOOK.ADDRESS_BOOK_ID,LU_ADDRESS_BOOK.A_ADDRESS_BOOK_SRCCD,
> >> > LU_COMPANY.COMPANY_ID,LU_COMPANY.COMPANY_SRCCD
> >> >
> >> >
> >> > |--Hash Match(Inner Join,
> >> > HASH:([LU_BUSINESS_UNIT].[BUSINESS_UNIT_ID])=([IV_test].[BUSINESS_UNIT_ID]))
> >> > |--Index
> >> > Scan(OBJECT:([JDE_WH_PREPD].[dbo].[LU_BUSINESS_UNIT].[IX_BUSINESS_UNIT_ID_BUSINESS_UNIT_SRCCD_]))
> >> > |--Hash Match(Inner Join,
> >> > HASH:([LU_COMPANY].[COMPANY_ID])=([IV_test].[COMPANY_ID]))
> >> > |--Clustered Index
> >> > Scan(OBJECT:([JDE_WH_PREPD].[dbo].[LU_COMPANY].[PK__LU_COMPANY__32EB7E57]))
> >> > |--Merge Join(Inner Join,
> >> > MERGE:([LU_ADDRESS_BOOK].[ADDRESS_BOOK_ID])=([IV_test].[ADDRESS_BOOK_ID]),
> >> > RESIDUAL:([IV_test].[ADDRESS_BOOK_ID]=[LU_ADDRESS_BOOK].[ADDRESS_BOOK_ID]))
> >> > |--Index
> >> > Scan(OBJECT:([JDE_WH_PREPD].[dbo].[LU_ADDRESS_BOOK].[LU_ADDRESS_BOOK74]),
> >> > ORDERED FORWARD)
> >> > |--Sort(ORDER BY:([IV_test].[ADDRESS_BOOK_ID] ASC))
> >> > |--Clustered Index
> >> > Scan(OBJECT:([JDE_WH_PREPD].[dbo].[IV_test].[IX_IV_test]))
> >> >
> >> > --
> >> >
> >> > As you can see the Optimizer seems to do its job.
> >> > But if I add a Join to my view The same query doesn't play the game.
> >> > Unfortunately our front end tool is a ROLAP tool so we cannot hard
> >> > force
> >> > the
> >> > Query to query directly from the view.
> >> >
> >> > Thanks
> >> > Richard
> >> > "Dan Guzman" wrote:
> >> >
> >> >> Thanks for the additional DDL. The create for the following default
> >> >> constraints and index failed but I don't think this is relevant for
> >> >> this
> >> >> issue:
> >> >>
> >> >> ALTER TABLE [dbo].[FACT_AR_AGEING] ADD
> >> >> CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_CREDIT_LIMIT] DEFAULT (0) FOR
> >> >> [AMOUNT_CREDIT_LIMIT],
> >> >> CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_OPEN] DEFAULT (0) FOR
> >> >> [AMOUNT_OPEN]
> >> >> CREATE INDEX [FACT_AR_AGEING29] ON
> >> >> [dbo].[FACT_AR_AGEING]([COMPANY_ID],
> >> >> [DATE_UPDATED_ID], [BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID],
> >> >> [AR_INVOICE_ID],
> >> >> [INV_DOC_TYPE_ID], [WEIGHTED_DAYS_INVOICE]) ON [INDEXES]
> >> >>
> >> >> I did some experimenting (sans data) and could only get the view to be
> >> >> used
> >> >> by accessing the view directly and adding an optimizer hint.
> >> >>
> >> >> SELECT
> >> >> IV_TEST.BUSINESS_UNIT_ID,
> >> >> IV_TEST.ADDRESS_BOOK_ID,
> >> >> IV_TEST.FISCAL_PERIOD_ID,
> >> >> SUM(IV_TEST.Amount) AS Amount
> >> >> FROM dbo.IV_test WITH (NOEXPAND)
> >> >> INNER JOIN dbo.LU_BUSINESS_UNIT ON
> >> >> IV_TEST.BUSINESS_UNIT_ID = LU_BUSINESS_UNIT.BUSINESS_UNIT_ID
> >> >> GROUP BY
> >> >> IV_TEST.BUSINESS_UNIT_ID,
> >> >> IV_TEST.ADDRESS_BOOK_ID,
> >> >> IV_TEST.FISCAL_PERIOD_ID
> >> >>
> >> >> It looks to me like this is the more optimal plan so I agree with you
> >> >> that
> >> >> the optimizer could/should use the view. I assume you see a similar
> >> >> plan,
> >> >> even with substantial data loaded?
> >> >>
> >> >> --
> >> >> Hope this helps.
> >> >>
> >> >> Dan Guzman
> >> >> SQL Server MVP
> >> >>
> >> >> "Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote
> >> >> in
> >> >> message news:3FABE473-511D-479E-A83D-9EAFE1B697C7@.microsoft.com...
> >> >> > Hi Dan
> >> >> > I tried to only include columns relavant to the view
> >> >> > there are three other tables that I didn't include,
> >> >> > LU_DATE
> >> >> > if exists (select * from dbo.sysobjects where id => >> >> > object_id(N'[dbo].[LU_DATE]') and OBJECTPROPERTY(id, N'IsUserTable')
> >> >> > => >> >> > 1)
> >> >> > drop table [dbo].[LU_DATE]
> >> >> > GO
> >> >> >
> >> >> > CREATE TABLE [dbo].[LU_DATE] (
> >> >> > [DATE_ID] [int] NOT NULL ,
> >> >> > [CALENDAR_DATE] [datetime] NULL ,
> >> >> > [CALENDAR_DATE_DESC] [varchar] (10) COLLATE
> >> >> > SQL_Latin1_General_CP1_CI_AS
> >> >> > NULL ,
> >> >> > [FISCAL_WEEK_ID] [int] NULL ,
> >> >> > [FISCAL_WEEK_DESC] [varchar] (10) COLLATE
> >> >> > SQL_Latin1_General_CP1_CI_AS
> >> >> > NULL ,
> >> >> > [FISCAL_PERIOD_ID] [int] NULL ,
> >> >> > [FISCAL_PERIOD_DESC] [varchar] (9) COLLATE
> >> >> > SQL_Latin1_General_CP1_CI_AS
> >> >> > NULL ,
> >> >> > [FISCAL_QUARTER_ID] [int] NULL ,
> >> >> > [FISCAL_QUARTER_DESC] [varchar] (9) COLLATE
> >> >> > SQL_Latin1_General_CP1_CI_AS
> >> >> > NULL ,
> >> >> > [FISCAL_YEAR_ID] [int] NULL ,
> >> >> > [CALENDAR_WEEK_ID] [int] NULL ,
> >> >> > [CALENDAR_WEEK_DESC] [varchar] (30) COLLATE
> >> >> > SQL_Latin1_General_CP1_CI_AS
> >> >> > NULL ,
> >> >> > [CALENDAR_MONTH_ID] [int] NULL ,
> >> >> > [CALENDAR_MONTH_DESC] [varchar] (9) COLLATE
> >> >> > SQL_Latin1_General_CP1_CI_AS
> >> >> > NULL ,
> >> >> > [CALENDAR_QUARTER_ID] [int] NULL ,
> >> >> > [CALENDAR_QUARTER_DESC] [varchar] (9) COLLATE
> >> >> > SQL_Latin1_General_CP1_CI_AS
> >> >> > NULL ,
> >> >> > [CALENDAR_YEAR_ID] [int] NULL ,
> >> >> > [MONTH_ID] [int] NULL ,
> >> >> > [MONTH_DESC] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
> >> >> > NULL
> >> >> > ) ON [PRIMARY]
> >> >> > GO
> >> >> >
> >> >> > ALTER TABLE [dbo].[LU_DATE] WITH NOCHECK ADD
> >> >> > CONSTRAINT [PK_LU_DATE] PRIMARY KEY CLUSTERED
> >> >> > (
> >> >> > [DATE_ID]
> >> >> > ) ON [PRIMARY]
> >> >> > GO
> >> >> >
> >> >> > CREATE INDEX [LU_DATE91] ON [dbo].[LU_DATE]([DATE_ID],
> >> >> > [CALENDAR_MONTH_ID]) ON [INDEXES]
> >> >> > GO
> >> >> >
> >> >> > CREATE INDEX [IX_CALENDAR_MONTH_ID] ON
> >> >> > [dbo].[LU_DATE]([CALENDAR_MONTH_ID]) ON [INDEXES]
> >> >> > GO
> >> >> > CREATE INDEX [IX_FISCAL_YEAR_ID_CALENDER_MONTH_ID] ON
> >> >> > [dbo].[LU_DATE]([FISCAL_YEAR_ID], [CALENDAR_MONTH_ID]) ON [INDEXES]
> >> >> > GO
> >> >> > CREATE INDEX [IX_LU_DATE_1] ON [dbo].[LU_DATE]([CALENDAR_DATE]) ON
> >> >> > [PRIMARY]
> >> >> > GO
> >> >> > --
> >> >> > --LU_BUSINESS_UNIT
> >> >> > --
> >> >> > CREATE TABLE [dbo].[LU_BUSINESS_UNIT] (
> >> >> > [BUSINESS_UNIT_ID] [int] NOT NULL ,
> >> >> > [BUSINESS_UNIT_SRCCD] [varchar] (12) COLLATE
> >> >> > SQL_Latin1_General_CP1_CI_AS
> >> >> > NOT NULL ,
> >> >> > [BUSINESS_UNIT_DESC] [varchar] (30) COLLATE
> >> >> > SQL_Latin1_General_CP1_CI_AS
> >> >> > NULL
> >> >> > ) ON [PRIMARY]
> >> >> > GO
> >> >> >
> >> >> > ALTER TABLE [dbo].[LU_BUSINESS_UNIT] WITH NOCHECK ADD
> >> >> > CONSTRAINT [PK__LU_BUSINESS_UNIT__174363E2] PRIMARY KEY CLUSTERED
> >> >> > (
> >> >> > [BUSINESS_UNIT_ID]
> >> >> > ) ON [PRIMARY]
> >> >> > GO
> >> >> >
> >> >> > CREATE INDEX [IX_BISINESS_UNIT_SRCCD] ON
> >> >> > [dbo].[LU_BUSINESS_UNIT]([BUSINESS_UNIT_SRCCD]) ON [INDEXES]
> >> >> > GO
> >> >> > --
> >> >> > --LU_ADDRESS_BOOK
> >> >> > --
> >> >> >
> >> >> > CREATE TABLE [dbo].[LU_ADDRESS_BOOK] (
> >> >> > [ADDRESS_BOOK_ID] [int] NOT NULL ,
> >> >> > [A_ADDRESS_BOOK_SRCCD] [int] NULL ,
> >> >> > [A_ADDRESS_BOOK_NAME] [varchar] (40) COLLATE
> >> >> > SQL_Latin1_General_CP1_CI_AS
> >> >> > NULL
> >> >> > ) ON [PRIMARY]
> >> >> > GO
> >> >> >
> >> >> > ALTER TABLE [dbo].[LU_ADDRESS_BOOK] WITH NOCHECK ADD
> >> >> > PRIMARY KEY CLUSTERED
> >> >> > (
> >> >> > [ADDRESS_BOOK_ID]
> >> >> > ) ON [PRIMARY]
> >> >> > GO
> >> >> >
> >> >> > CREATE INDEX [LU_ADDRESS_BOOK74] ON
> >> >> > [dbo].[LU_ADDRESS_BOOK]([ADDRESS_BOOK_ID], [A_ADDRESS_BOOK_SRCCD])
> >> >> > ON
> >> >> > [INDEXES]
> >> >> > GO|||> Do you think SQL2005 will solve this issue?
In my testing, no. I did observe that the SQL 2005 optimizer uses different
threshholds in choosing the view over direct table access in the first
query; SQL Server 2005 required more data in LU_BUSINESS_UNIT before the
view was used.
Do you have a 2005 environment you can test with?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote in
message news:A7DA6B57-797B-4383-AEFD-23870EDFDCF0@.microsoft.com...
> Hi Dan,
> Thanks for your assistance, the only way I could get any joy was to remove
> all joins from the view. The difficulty with the view that you created is
> that you can still only join one other table to the view in your query,
> and
> once you add a table to the view definition, all columns that will be used
> in
> that table must also be added to the join (i.e. If you add
> BUSINESS_UNIT_SRCCD, and/or BUSINESS_UNIT_DESC to the Query, they must be
> included in the view definition) for the optimizer to use the view.
> SELECT FACT_AR_AGEING.BUSINESS_UNIT_ID,
> FACT_AR_AGEING.ADDRESS_BOOK_ID,
> LU_DATE.FISCAL_PERIOD_ID,
> SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount,
> LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD
> FROM FACT_AR_AGEING INNER JOIN
> LU_DATE ON FACT_AR_AGEING.DATE_UPDATED_ID => LU_DATE.DATE_ID INNER JOIN
> LU_BUSINESS_UNIT ON FACT_AR_AGEING.BUSINESS_UNIT_ID => LU_BUSINESS_UNIT.BUSINESS_UNIT_ID
> GROUP BY FACT_AR_AGEING.BUSINESS_UNIT_ID, FACT_AR_AGEING.ADDRESS_BOOK_ID,
> LU_DATE.FISCAL_PERIOD_ID,
> LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD
> Do you think SQL2005 will solve this issue?
>
> "Dan Guzman" wrote:
>> Hi, Richard.
>> After some experimentation, I couldn't get the indexed view to be used by
>> the second query. However, I was able to create an additional indexed
>> view
>> including the second join and the second query plan used that indexed
>> view.
>> Perhaps that workaround is acceptable in your environment.
>> I'll let you know if I can find out more information. I believe the
>> optimizer should use the original indexed view.
>> CREATE VIEW dbo.IV_test2
>> WITH SCHEMABINDING
>> AS
>> SELECT
>> FACT_AR_AGEING.BUSINESS_UNIT_ID,
>> FACT_AR_AGEING.ADDRESS_BOOK_ID,
>> LU_DATE.FISCAL_PERIOD_ID,
>> SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount,
>> COUNT_BIG(*) AS CountBig
>> FROM dbo.FACT_AR_AGEING
>> INNER JOIN dbo.LU_DATE ON
>> FACT_AR_AGEING.DATE_UPDATED_ID = LU_DATE.DATE_ID
>> INNER JOIN dbo.LU_BUSINESS_UNIT ON
>> FACT_AR_AGEING.BUSINESS_UNIT_ID = LU_BUSINESS_UNIT.BUSINESS_UNIT_ID
>> GROUP BY
>> FACT_AR_AGEING.BUSINESS_UNIT_ID,
>> FACT_AR_AGEING.ADDRESS_BOOK_ID,
>> LU_DATE.FISCAL_PERIOD_ID
>> GO
>> CREATE UNIQUE CLUSTERED INDEX [IX_Main] ON
>> [dbo].[IV_test2](
>> [BUSINESS_UNIT_ID],
>> [ADDRESS_BOOK_ID] DESC ,
>> [FISCAL_PERIOD_ID])
>> GO
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote in
>> message news:6DFA3A52-A7A6-4401-9D17-59A0CA82EF76@.microsoft.com...
>> > Hi Dan
>> > The Fact contains about 12 million rows and the BU dimension contains
>> > about
>> > 4000 rows
>> >
>> > Thanks
>> >
>> > "Dan Guzman" wrote:
>> >
>> >> > Unfortunately our front end tool is a ROLAP tool so we cannot hard
>> >> > force
>> >> > the
>> >> > Query to query directly from the view.
>> >>
>> >> SQL 2005 offers some relief in this regard. In some situations, plan
>> >> guides
>> >> can be used to provide more optimal plans in cases where query hints
>> >> can't
>> >> be used directly.
>> >>
>> >> I'm trying to develop a simplified test case for analysis. Can you
>> >> tell
>> >> me
>> >> approximately how many rows are in your fact table and business unit
>> >> dimension table?
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "Richard Greyling" <RichardGreyling@.discussions.microsoft.com> wrote
>> >> in
>> >> message news:3BC1F966-42F9-4C84-9B12-791C00C9DD1E@.microsoft.com...
>> >> > Hi Dan,
>> >> > Thanks for your assistance.
>> >> > The constraint has no effect on this issue, I removed it from my
>> >> > table.
>> >> > here is the execution plan if I remove the Join to LU_DATE from my
>> >> > view
>> >> > SET SHOWPLAN_ALL on
>> >> >
>> >> > SELECT LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD,
>> >> > SUM(FACT_AR_AGEING.AMOUNT_OPEN) AS Amount,
>> >> > LU_ADDRESS_BOOK.A_ADDRESS_BOOK_SRCCD,
>> >> > LU_COMPANY.COMPANY_SRCCD
>> >> > FROM FACT_AR_AGEING INNER JOIN
>> >> > LU_BUSINESS_UNIT ON
>> >> > FACT_AR_AGEING.BUSINESS_UNIT_ID =>> >> > LU_BUSINESS_UNIT.BUSINESS_UNIT_ID INNER JOIN
>> >> > LU_ADDRESS_BOOK ON
>> >> > FACT_AR_AGEING.ADDRESS_BOOK_ID
>> >> > =>> >> > LU_ADDRESS_BOOK.ADDRESS_BOOK_ID INNER JOIN
>> >> > LU_COMPANY ON FACT_AR_AGEING.COMPANY_ID =>> >> > LU_COMPANY.COMPANY_ID
>> >> > GROUP BY
>> >> > LU_BUSINESS_UNIT.BUSINESS_UNIT_ID,LU_BUSINESS_UNIT.BUSINESS_UNIT_SRCCD,
>> >> > LU_ADDRESS_BOOK.ADDRESS_BOOK_ID,LU_ADDRESS_BOOK.A_ADDRESS_BOOK_SRCCD,
>> >> > LU_COMPANY.COMPANY_ID,LU_COMPANY.COMPANY_SRCCD
>> >> >
>> >> >
>> >> > |--Hash Match(Inner Join,
>> >> > HASH:([LU_BUSINESS_UNIT].[BUSINESS_UNIT_ID])=([IV_test].[BUSINESS_UNIT_ID]))
>> >> > |--Index
>> >> > Scan(OBJECT:([JDE_WH_PREPD].[dbo].[LU_BUSINESS_UNIT].[IX_BUSINESS_UNIT_ID_BUSINESS_UNIT_SRCCD_]))
>> >> > |--Hash Match(Inner Join,
>> >> > HASH:([LU_COMPANY].[COMPANY_ID])=([IV_test].[COMPANY_ID]))
>> >> > |--Clustered Index
>> >> > Scan(OBJECT:([JDE_WH_PREPD].[dbo].[LU_COMPANY].[PK__LU_COMPANY__32EB7E57]))
>> >> > |--Merge Join(Inner Join,
>> >> > MERGE:([LU_ADDRESS_BOOK].[ADDRESS_BOOK_ID])=([IV_test].[ADDRESS_BOOK_ID]),
>> >> > RESIDUAL:([IV_test].[ADDRESS_BOOK_ID]=[LU_ADDRESS_BOOK].[ADDRESS_BOOK_ID]))
>> >> > |--Index
>> >> > Scan(OBJECT:([JDE_WH_PREPD].[dbo].[LU_ADDRESS_BOOK].[LU_ADDRESS_BOOK74]),
>> >> > ORDERED FORWARD)
>> >> > |--Sort(ORDER BY:([IV_test].[ADDRESS_BOOK_ID] ASC))
>> >> > |--Clustered Index
>> >> > Scan(OBJECT:([JDE_WH_PREPD].[dbo].[IV_test].[IX_IV_test]))
>> >> >
>> >> > --
>> >> >
>> >> > As you can see the Optimizer seems to do its job.
>> >> > But if I add a Join to my view The same query doesn't play the game.
>> >> > Unfortunately our front end tool is a ROLAP tool so we cannot hard
>> >> > force
>> >> > the
>> >> > Query to query directly from the view.
>> >> >
>> >> > Thanks
>> >> > Richard
>> >> > "Dan Guzman" wrote:
>> >> >
>> >> >> Thanks for the additional DDL. The create for the following
>> >> >> default
>> >> >> constraints and index failed but I don't think this is relevant for
>> >> >> this
>> >> >> issue:
>> >> >>
>> >> >> ALTER TABLE [dbo].[FACT_AR_AGEING] ADD
>> >> >> CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_CREDIT_LIMIT] DEFAULT (0)
>> >> >> FOR
>> >> >> [AMOUNT_CREDIT_LIMIT],
>> >> >> CONSTRAINT [DF_FACT_AR_AGEING_AMOUNT_OPEN] DEFAULT (0) FOR
>> >> >> [AMOUNT_OPEN]
>> >> >> CREATE INDEX [FACT_AR_AGEING29] ON
>> >> >> [dbo].[FACT_AR_AGEING]([COMPANY_ID],
>> >> >> [DATE_UPDATED_ID], [BUSINESS_UNIT_ID], [ADDRESS_BOOK_ID],
>> >> >> [AR_INVOICE_ID],
>> >> >> [INV_DOC_TYPE_ID], [WEIGHTED_DAYS_INVOICE]) ON [INDEXES]
>> >> >>
>> >> >> I did some experimenting (sans data) and could only get the view to
>> >> >> be
>> >> >> used
>> >> >> by accessing the view directly and adding an optimizer hint.
>> >> >>
>> >> >> SELECT
>> >> >> IV_TEST.BUSINESS_UNIT_ID,
>> >> >> IV_TEST.ADDRESS_BOOK_ID,
>> >> >> IV_TEST.FISCAL_PERIOD_ID,
>> >> >> SUM(IV_TEST.Amount) AS Amount
>> >> >> FROM dbo.IV_test WITH (NOEXPAND)
>> >> >> INNER JOIN dbo.LU_BUSINESS_UNIT ON
>> >> >> IV_TEST.BUSINESS_UNIT_ID = LU_BUSINESS_UNIT.BUSINESS_UNIT_ID
>> >> >> GROUP BY
>> >> >> IV_TEST.BUSINESS_UNIT_ID,
>> >> >> IV_TEST.ADDRESS_BOOK_ID,
>> >> >> IV_TEST.FISCAL_PERIOD_ID
>> >> >>
>> >> >> It looks to me like this is the more optimal plan so I agree with
>> >> >> you
>> >> >> that
>> >> >> the optimizer could/should use the view. I assume you see a
>> >> >> similar
>> >> >> plan,
>> >> >> even with substantial data loaded?
>> >> >>
>> >> >> --
>> >> >> Hope this helps.
>> >> >>
>> >> >> Dan Guzman
>> >> >> SQL Server MVP
>> >> >>
>> >> >> "Richard Greyling" <RichardGreyling@.discussions.microsoft.com>
>> >> >> wrote
>> >> >> in
>> >> >> message news:3FABE473-511D-479E-A83D-9EAFE1B697C7@.microsoft.com...
>> >> >> > Hi Dan
>> >> >> > I tried to only include columns relavant to the view
>> >> >> > there are three other tables that I didn't include,
>> >> >> > LU_DATE
>> >> >> > if exists (select * from dbo.sysobjects where id =>> >> >> > object_id(N'[dbo].[LU_DATE]') and OBJECTPROPERTY(id,
>> >> >> > N'IsUserTable')
>> >> >> > =>> >> >> > 1)
>> >> >> > drop table [dbo].[LU_DATE]
>> >> >> > GO
>> >> >> >
>> >> >> > CREATE TABLE [dbo].[LU_DATE] (
>> >> >> > [DATE_ID] [int] NOT NULL ,
>> >> >> > [CALENDAR_DATE] [datetime] NULL ,
>> >> >> > [CALENDAR_DATE_DESC] [varchar] (10) COLLATE
>> >> >> > SQL_Latin1_General_CP1_CI_AS
>> >> >> > NULL ,
>> >> >> > [FISCAL_WEEK_ID] [int] NULL ,
>> >> >> > [FISCAL_WEEK_DESC] [varchar] (10) COLLATE
>> >> >> > SQL_Latin1_General_CP1_CI_AS
>> >> >> > NULL ,
>> >> >> > [FISCAL_PERIOD_ID] [int] NULL ,
>> >> >> > [FISCAL_PERIOD_DESC] [varchar] (9) COLLATE
>> >> >> > SQL_Latin1_General_CP1_CI_AS
>> >> >> > NULL ,
>> >> >> > [FISCAL_QUARTER_ID] [int] NULL ,
>> >> >> > [FISCAL_QUARTER_DESC] [varchar] (9) COLLATE
>> >> >> > SQL_Latin1_General_CP1_CI_AS
>> >> >> > NULL ,
>> >> >> > [FISCAL_YEAR_ID] [int] NULL ,
>> >> >> > [CALENDAR_WEEK_ID] [int] NULL ,
>> >> >> > [CALENDAR_WEEK_DESC] [varchar] (30) COLLATE
>> >> >> > SQL_Latin1_General_CP1_CI_AS
>> >> >> > NULL ,
>> >> >> > [CALENDAR_MONTH_ID] [int] NULL ,
>> >> >> > [CALENDAR_MONTH_DESC] [varchar] (9) COLLATE
>> >> >> > SQL_Latin1_General_CP1_CI_AS
>> >> >> > NULL ,
>> >> >> > [CALENDAR_QUARTER_ID] [int] NULL ,
>> >> >> > [CALENDAR_QUARTER_DESC] [varchar] (9) COLLATE
>> >> >> > SQL_Latin1_General_CP1_CI_AS
>> >> >> > NULL ,
>> >> >> > [CALENDAR_YEAR_ID] [int] NULL ,
>> >> >> > [MONTH_ID] [int] NULL ,
>> >> >> > [MONTH_DESC] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
>> >> >> > NULL
>> >> >> > ) ON [PRIMARY]
>> >> >> > GO
>> >> >> >
>> >> >> > ALTER TABLE [dbo].[LU_DATE] WITH NOCHECK ADD
>> >> >> > CONSTRAINT [PK_LU_DATE] PRIMARY KEY CLUSTERED
>> >> >> > (
>> >> >> > [DATE_ID]
>> >> >> > ) ON [PRIMARY]
>> >> >> > GO
>> >> >> >
>> >> >> > CREATE INDEX [LU_DATE91] ON [dbo].[LU_DATE]([DATE_ID],
>> >> >> > [CALENDAR_MONTH_ID]) ON [INDEXES]
>> >> >> > GO
>> >> >> >
>> >> >> > CREATE INDEX [IX_CALENDAR_MONTH_ID] ON
>> >> >> > [dbo].[LU_DATE]([CALENDAR_MONTH_ID]) ON [INDEXES]
>> >> >> > GO
>> >> >> > CREATE INDEX [IX_FISCAL_YEAR_ID_CALENDER_MONTH_ID] ON
>> >> >> > [dbo].[LU_DATE]([FISCAL_YEAR_ID], [CALENDAR_MONTH_ID]) ON
>> >> >> > [INDEXES]
>> >> >> > GO
>> >> >> > CREATE INDEX [IX_LU_DATE_1] ON [dbo].[LU_DATE]([CALENDAR_DATE])
>> >> >> > ON
>> >> >> > [PRIMARY]
>> >> >> > GO
>> >> >> > --
>> >> >> > --LU_BUSINESS_UNIT
>> >> >> > --
>> >> >> > CREATE TABLE [dbo].[LU_BUSINESS_UNIT] (
>> >> >> > [BUSINESS_UNIT_ID] [int] NOT NULL ,
>> >> >> > [BUSINESS_UNIT_SRCCD] [varchar] (12) COLLATE
>> >> >> > SQL_Latin1_General_CP1_CI_AS
>> >> >> > NOT NULL ,
>> >> >> > [BUSINESS_UNIT_DESC] [varchar] (30) COLLATE
>> >> >> > SQL_Latin1_General_CP1_CI_AS
>> >> >> > NULL
>> >> >> > ) ON [PRIMARY]
>> >> >> > GO
>> >> >> >
>> >> >> > ALTER TABLE [dbo].[LU_BUSINESS_UNIT] WITH NOCHECK ADD
>> >> >> > CONSTRAINT [PK__LU_BUSINESS_UNIT__174363E2] PRIMARY KEY
>> >> >> > CLUSTERED
>> >> >> > (
>> >> >> > [BUSINESS_UNIT_ID]
>> >> >> > ) ON [PRIMARY]
>> >> >> > GO
>> >> >> >
>> >> >> > CREATE INDEX [IX_BISINESS_UNIT_SRCCD] ON
>> >> >> > [dbo].[LU_BUSINESS_UNIT]([BUSINESS_UNIT_SRCCD]) ON [INDEXES]
>> >> >> > GO
>> >> >> > --
>> >> >> > --LU_ADDRESS_BOOK
>> >> >> > --
>> >> >> >
>> >> >> > CREATE TABLE [dbo].[LU_ADDRESS_BOOK] (
>> >> >> > [ADDRESS_BOOK_ID] [int] NOT NULL ,
>> >> >> > [A_ADDRESS_BOOK_SRCCD] [int] NULL ,
>> >> >> > [A_ADDRESS_BOOK_NAME] [varchar] (40) COLLATE
>> >> >> > SQL_Latin1_General_CP1_CI_AS
>> >> >> > NULL
>> >> >> > ) ON [PRIMARY]
>> >> >> > GO
>> >> >> >
>> >> >> > ALTER TABLE [dbo].[LU_ADDRESS_BOOK] WITH NOCHECK ADD
>> >> >> > PRIMARY KEY CLUSTERED
>> >> >> > (
>> >> >> > [ADDRESS_BOOK_ID]
>> >> >> > ) ON [PRIMARY]
>> >> >> > GO
>> >> >> >
>> >> >> > CREATE INDEX [LU_ADDRESS_BOOK74] ON
>> >> >> > [dbo].[LU_ADDRESS_BOOK]([ADDRESS_BOOK_ID],
>> >> >> > [A_ADDRESS_BOOK_SRCCD])
>> >> >> > ON
>> >> >> > [INDEXES]
>> >> >> > GO