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...
>
>
|||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...
>
>
|||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...
>
>
|||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...[vbcol=seagreen]
> 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:
|||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...
>
>
|||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...[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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...