Partitioned Views in Sql 2000

Been trying to start a new model with Partitioned Views. Its basically
Monthly data in Fact Tables with Time_Sk as Chk Constraint and the a View on
Top of 12-24 tables. Thing i notice is that the query with Time_Sk In (1,2)
gets me the data faster than Time_Sk In (Select Value from Table where yrmnth
between '200701' and '200702') or a Join with TimeDim.
I did look up a few notes in here, where in it says that the plan would say
that all the tables are scanned but in truth only the wanted table is
scanned. But how would the time taken to run these 2 queries varry so much...
Any ideas ' Options '
--
Sr Apps Developer> I did look up a few notes in here, where in it says that the plan would
> say
> that all the tables are scanned but in truth only the wanted table is
> scanned.
As long as your view and underlying tables are setup correctly (primary key
and check constraint), the execution plan will contain a startup expression
to eliminate unneeded tables during execution.
> But how would the time taken to run these 2 queries varry so much...
I expect this is due to different execution plans. Can you post DDL
(simplified CREATE TABLE and VIEW) that shows the issue? I recall that
there might be cases where elimination doesn't occur via joins but indexes
can still be used to efficiently return data.
Hope this helps.
Dan Guzman
SQL Server MVP
"sabin" <sabin@.discussions.microsoft.com> wrote in message
news:D9C7FF35-961C-481E-BF88-9D8933CD0332@.microsoft.com...
> Been trying to start a new model with Partitioned Views. Its basically
> Monthly data in Fact Tables with Time_Sk as Chk Constraint and the a View
> on
> Top of 12-24 tables. Thing i notice is that the query with Time_Sk In
> (1,2)
> gets me the data faster than Time_Sk In (Select Value from Table where
> yrmnth
> between '200701' and '200702') or a Join with TimeDim.
> I did look up a few notes in here, where in it says that the plan would
> say
> that all the tables are scanned but in truth only the wanted table is
> scanned. But how would the time taken to run these 2 queries varry so
> much...
> Any ideas ' Options '
> --
> Sr Apps Developer|||/****** Object: Table [dbo].[Product_Fact_TmpTmSk88] Script Date:
7/27/2007 11:20:25 AM ******/
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Product_Fact_TmpTmSk88]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[Product_Fact_TmpTmSk88]
GO
/****** Object: Table [dbo].[Usage_Fact_TmSk88] Script Date: 7/27/2007
11:20:25 AM ******/
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Usage_Fact_TmSk88]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[Usage_Fact_TmSk88]
GO
/****** Object: Table [dbo].[Product_Fact_TmpTmSk88] Script Date:
7/27/2007 11:20:27 AM ******/
CREATE TABLE [dbo].[Product_Fact_TmpTmSk88] (
[Fact_Sk] [numeric](18, 0) IDENTITY (150000000, 1) NOT NULL ,
[Time_Sk] [numeric](18, 0) NOT NULL ,
[Main_Btn_Sk] [numeric](18, 0) NOT NULL ,
[Entity_Sk] [numeric](18, 0) NOT NULL ,
[AssocChgCd_Sk] [numeric](18, 0) NOT NULL ,
[Product_Sk] [numeric](18, 0) NOT NULL ,
[Unit_Rate] [varchar] (13) NOT NULL ,
[Prod_Qty] [int] NULL ,
[Rev_Amt] [decimal](38, 2) NULL ,
[DateStamp] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Usage_Fact_TmSk88] Script Date: 7/27/2007
11:20:29 AM ******/
CREATE TABLE [dbo].[Usage_Fact_TmSk88] (
[Fact_Sk] [numeric](18, 0) IDENTITY (170000000, 1) NOT NULL ,
[Time_Sk] [numeric](18, 0) NOT NULL ,
[Main_Btn_Sk] [numeric](18, 0) NOT NULL ,
[Usage_Sk] [numeric](18, 0) NOT NULL ,
[Billable_Calls] [decimal](11, 0) NULL ,
[Billable_MOU] [decimal](11, 0) NULL ,
[Billable_Revenue] [decimal](13, 2) NULL ,
[Billed_Calls] [decimal](18, 0) NULL ,
[Billed_MOU] [decimal](11, 0) NULL ,
[Billed_Revenue] [decimal](13, 2) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Product_Fact_TmpTmSk88] WITH NOCHECK ADD
CONSTRAINT [PK_Product_Fact_TmpTmSk88] PRIMARY KEY CLUSTERED
(
[Time_Sk],
[Fact_Sk]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Usage_Fact_TmSk88] WITH NOCHECK ADD
CONSTRAINT [PK_Usage_Fact_TmpTmSk88] PRIMARY KEY CLUSTERED
(
[Time_Sk],
[Fact_Sk]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Product_Fact_TmpTmSk88] WITH NOCHECK ADD
CHECK ([Time_Sk] = 88)
GO
ALTER TABLE [dbo].[Usage_Fact_TmSk88] WITH NOCHECK ADD
CHECK ([Time_Sk] = 88)
GO
CREATE UNIQUE INDEX [UK_Product_Fact_TmpTmSk88] ON
[dbo].[Product_Fact_TmpTmSk88]([Main_Btn_Sk], [Entity_Sk], [AssocChgCd_Sk],
[Product_Sk], [Unit_Rate]) ON [PRD_INDXGRP]
GO
CREATE INDEX [Idx_PfTmsk88_TimeSK] ON
[dbo].[Product_Fact_TmpTmSk88]([Time_Sk]) ON [PRD_INDXGRP]
GO
CREATE INDEX [Idx_PfTmsk88_MainBTNSK] ON
[dbo].[Product_Fact_TmpTmSk88]([Main_Btn_Sk]) ON [PRD_INDXGRP]
GO
CREATE INDEX [Idx_PfTmsk88_ProductSK] ON
[dbo].[Product_Fact_TmpTmSk88]([Product_Sk]) ON [PRD_INDXGRP]
GO
CREATE UNIQUE INDEX [UK_Usage_Fact_TmpTmSk88] ON
[dbo].[Usage_Fact_TmSk88]([Main_Btn_Sk], [Usage_Sk]) ON [PRD_INDXGRP]
GO
CREATE INDEX [Idx_UfTmsk88_MainBtnSk] ON
[dbo].[Usage_Fact_TmSk88]([Main_Btn_Sk]) ON [PRD_INDXGRP]
GO
CREATE INDEX [Idx_UfTmsk88_UsageSk] ON
[dbo].[Usage_Fact_TmSk88]([Usage_Sk]) ON [PRD_INDXGRP]
GO
I did some simple checks as to run the Fact table with a join to just the
dimension table...If i were to use a join or a subquery, it runs a scan on
all the fact tables...BUt if i run the query on usage_fact with a where
condition as time_sk in (81,82), it just looks at the 81 and 82 table
Sr Apps Developer
"Dan Guzman" wrote:
> > I did look up a few notes in here, where in it says that the plan would
> > say
> > that all the tables are scanned but in truth only the wanted table is
> > scanned.
> As long as your view and underlying tables are setup correctly (primary key
> and check constraint), the execution plan will contain a startup expression
> to eliminate unneeded tables during execution.
> > But how would the time taken to run these 2 queries varry so much...
> I expect this is due to different execution plans. Can you post DDL
> (simplified CREATE TABLE and VIEW) that shows the issue? I recall that
> there might be cases where elimination doesn't occur via joins but indexes
> can still be used to efficiently return data.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "sabin" <sabin@.discussions.microsoft.com> wrote in message
> news:D9C7FF35-961C-481E-BF88-9D8933CD0332@.microsoft.com...
> > Been trying to start a new model with Partitioned Views. Its basically
> > Monthly data in Fact Tables with Time_Sk as Chk Constraint and the a View
> > on
> > Top of 12-24 tables. Thing i notice is that the query with Time_Sk In
> > (1,2)
> > gets me the data faster than Time_Sk In (Select Value from Table where
> > yrmnth
> > between '200701' and '200702') or a Join with TimeDim.
> >
> > I did look up a few notes in here, where in it says that the plan would
> > say
> > that all the tables are scanned but in truth only the wanted table is
> > scanned. But how would the time taken to run these 2 queries varry so
> > much...
> >
> > Any ideas ' Options '
> > --
> > Sr Apps Developer
>|||the view creation ddl...
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: View dbo.Product_Fact Script Date: 7/27/2007 11:21:18 AM
******/
CREATE View [dbo].[Product_Fact] With SchemaBinding
AS
Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Product_Sk,Unit_Rate,Prod_Qty,Rev_Amt From [dbo].[Product_Fact_TmpTmSk88]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Product_Sk,Unit_Rate,Prod_Qty,Rev_Amt From [dbo].[Product_Fact_TmpTmSk87]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Product_Sk,Unit_Rate,Prod_Qty,Rev_Amt From [dbo].[Product_Fact_TmpTmSk86]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Product_Sk,Unit_Rate,Prod_Qty,Rev_Amt From [dbo].[Product_Fact_TmpTmSk85]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Product_Sk,Unit_Rate,Prod_Qty,Rev_Amt From [dbo].[Product_Fact_TmpTmSk84]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Product_Sk,Unit_Rate,Prod_Qty,Rev_Amt From [dbo].[Product_Fact_TmpTmSk83]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Product_Sk,Unit_Rate,Prod_Qty,Rev_Amt From [dbo].[Product_Fact_TmpTmSk82]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: View dbo.Usage_Fact Script Date: 7/27/2007 11:21:18 AM
******/
CREATE View [dbo].[Usage_Fact] With SchemaBinding
As
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billable_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Billed_Revenue From [dbo].[Usage_Fact_TmSk88]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billable_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Billed_Revenue From [dbo].[Usage_Fact_TmSk87]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billable_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Billed_Revenue From [dbo].[Usage_Fact_TmSk86]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billable_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Billed_Revenue From [dbo].[Usage_Fact_TmSk85]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billable_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Billed_Revenue From [dbo].[Usage_Fact_TmSk84]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billable_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Billed_Revenue From [dbo].[Usage_Fact_TmSk83]
/*
Union All
Select * From [dbo].[Usage_Fact_TmSk82]
Union All
Select * From [dbo].[Usage_Fact_TmSk81]
Union All
Select * From [dbo].[Usage_Fact_TmSk80]
Union All
Select * From [dbo].[Usage_Fact_TmSk79]
Union All
Select * From [dbo].[Usage_Fact_TmSk78]
Union All
Select * From [dbo].[Usage_Fact_TmSk77]
*/
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--
Sr Apps Developer
"sabin" wrote:
> /****** Object: Table [dbo].[Product_Fact_TmpTmSk88] Script Date:
> 7/27/2007 11:20:25 AM ******/
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Product_Fact_TmpTmSk88]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[Product_Fact_TmpTmSk88]
> GO
> /****** Object: Table [dbo].[Usage_Fact_TmSk88] Script Date: 7/27/2007
> 11:20:25 AM ******/
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Usage_Fact_TmSk88]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[Usage_Fact_TmSk88]
> GO
> /****** Object: Table [dbo].[Product_Fact_TmpTmSk88] Script Date:
> 7/27/2007 11:20:27 AM ******/
> CREATE TABLE [dbo].[Product_Fact_TmpTmSk88] (
> [Fact_Sk] [numeric](18, 0) IDENTITY (150000000, 1) NOT NULL ,
> [Time_Sk] [numeric](18, 0) NOT NULL ,
> [Main_Btn_Sk] [numeric](18, 0) NOT NULL ,
> [Entity_Sk] [numeric](18, 0) NOT NULL ,
> [AssocChgCd_Sk] [numeric](18, 0) NOT NULL ,
> [Product_Sk] [numeric](18, 0) NOT NULL ,
> [Unit_Rate] [varchar] (13) NOT NULL ,
> [Prod_Qty] [int] NULL ,
> [Rev_Amt] [decimal](38, 2) NULL ,
> [DateStamp] [datetime] NULL
> ) ON [PRIMARY]
> GO
> /****** Object: Table [dbo].[Usage_Fact_TmSk88] Script Date: 7/27/2007
> 11:20:29 AM ******/
> CREATE TABLE [dbo].[Usage_Fact_TmSk88] (
> [Fact_Sk] [numeric](18, 0) IDENTITY (170000000, 1) NOT NULL ,
> [Time_Sk] [numeric](18, 0) NOT NULL ,
> [Main_Btn_Sk] [numeric](18, 0) NOT NULL ,
> [Usage_Sk] [numeric](18, 0) NOT NULL ,
> [Billable_Calls] [decimal](11, 0) NULL ,
> [Billable_MOU] [decimal](11, 0) NULL ,
> [Billable_Revenue] [decimal](13, 2) NULL ,
> [Billed_Calls] [decimal](18, 0) NULL ,
> [Billed_MOU] [decimal](11, 0) NULL ,
> [Billed_Revenue] [decimal](13, 2) NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Product_Fact_TmpTmSk88] WITH NOCHECK ADD
> CONSTRAINT [PK_Product_Fact_TmpTmSk88] PRIMARY KEY CLUSTERED
> (
> [Time_Sk],
> [Fact_Sk]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Usage_Fact_TmSk88] WITH NOCHECK ADD
> CONSTRAINT [PK_Usage_Fact_TmpTmSk88] PRIMARY KEY CLUSTERED
> (
> [Time_Sk],
> [Fact_Sk]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Product_Fact_TmpTmSk88] WITH NOCHECK ADD
> CHECK ([Time_Sk] = 88)
> GO
> ALTER TABLE [dbo].[Usage_Fact_TmSk88] WITH NOCHECK ADD
> CHECK ([Time_Sk] = 88)
> GO
> CREATE UNIQUE INDEX [UK_Product_Fact_TmpTmSk88] ON
> [dbo].[Product_Fact_TmpTmSk88]([Main_Btn_Sk], [Entity_Sk], [AssocChgCd_Sk],
> [Product_Sk], [Unit_Rate]) ON [PRD_INDXGRP]
> GO
> CREATE INDEX [Idx_PfTmsk88_TimeSK] ON
> [dbo].[Product_Fact_TmpTmSk88]([Time_Sk]) ON [PRD_INDXGRP]
> GO
> CREATE INDEX [Idx_PfTmsk88_MainBTNSK] ON
> [dbo].[Product_Fact_TmpTmSk88]([Main_Btn_Sk]) ON [PRD_INDXGRP]
> GO
> CREATE INDEX [Idx_PfTmsk88_ProductSK] ON
> [dbo].[Product_Fact_TmpTmSk88]([Product_Sk]) ON [PRD_INDXGRP]
> GO
> CREATE UNIQUE INDEX [UK_Usage_Fact_TmpTmSk88] ON
> [dbo].[Usage_Fact_TmSk88]([Main_Btn_Sk], [Usage_Sk]) ON [PRD_INDXGRP]
> GO
> CREATE INDEX [Idx_UfTmsk88_MainBtnSk] ON
> [dbo].[Usage_Fact_TmSk88]([Main_Btn_Sk]) ON [PRD_INDXGRP]
> GO
> CREATE INDEX [Idx_UfTmsk88_UsageSk] ON
> [dbo].[Usage_Fact_TmSk88]([Usage_Sk]) ON [PRD_INDXGRP]
> GO
>
>
> I did some simple checks as to run the Fact table with a join to just the
> dimension table...If i were to use a join or a subquery, it runs a scan on
> all the fact tables...BUt if i run the query on usage_fact with a where
> condition as time_sk in (81,82), it just looks at the 81 and 82 table
>
> --
> Sr Apps Developer
>
> "Dan Guzman" wrote:
> > > I did look up a few notes in here, where in it says that the plan would
> > > say
> > > that all the tables are scanned but in truth only the wanted table is
> > > scanned.
> >
> > As long as your view and underlying tables are setup correctly (primary key
> > and check constraint), the execution plan will contain a startup expression
> > to eliminate unneeded tables during execution.
> >
> > > But how would the time taken to run these 2 queries varry so much...
> >
> > I expect this is due to different execution plans. Can you post DDL
> > (simplified CREATE TABLE and VIEW) that shows the issue? I recall that
> > there might be cases where elimination doesn't occur via joins but indexes
> > can still be used to efficiently return data.
> >
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "sabin" <sabin@.discussions.microsoft.com> wrote in message
> > news:D9C7FF35-961C-481E-BF88-9D8933CD0332@.microsoft.com...
> > > Been trying to start a new model with Partitioned Views. Its basically
> > > Monthly data in Fact Tables with Time_Sk as Chk Constraint and the a View
> > > on
> > > Top of 12-24 tables. Thing i notice is that the query with Time_Sk In
> > > (1,2)
> > > gets me the data faster than Time_Sk In (Select Value from Table where
> > > yrmnth
> > > between '200701' and '200702') or a Join with TimeDim.
> > >
> > > I did look up a few notes in here, where in it says that the plan would
> > > say
> > > that all the tables are scanned but in truth only the wanted table is
> > > scanned. But how would the time taken to run these 2 queries varry so
> > > much...
> > >
> > > Any ideas ' Options '
> > > --
> > > Sr Apps Developer
> >|||I went ahead a created a sample with just 1000 rows in each table...2 tables.
-- TimeSk 88
CREATE TABLE [dbo].[UsgFact_TmSk88] (
[Fact_Sk] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Time_Sk] [numeric](18, 0) NOT NULL ,
[Main_Btn_Sk] [numeric](18, 0) NOT NULL ,
[Usage_Sk] [numeric](18, 0) NOT NULL ,
[Billable_Calls] [decimal](11, 0) NULL ,
[Billable_MOU] [decimal](11, 0) NULL ,
[Billable_Revenue] [decimal](13, 2) NULL ,
[Billed_Calls] [decimal](18, 0) NULL ,
[Billed_MOU] [decimal](11, 0) NULL ,
[Billed_Revenue] [decimal](13, 2) NULL
) ON [PRIMARY]
Insert Into UsgFact_TmSk88
(Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billable_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Billed_Revenue)
Select Top 1000
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billable_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Billed_Revenue From Usage_Fact_Tmsk88 ;
ALTER TABLE [dbo].[UsgFact_TmSk88] WITH CHECK ADD CONSTRAINT
[PK_UsgFact_TmpTmSk88] PRIMARY KEY CLUSTERED ([Time_Sk],[Fact_Sk]) ON
[PRIMARY]
ALTER TABLE [dbo].[UsgFact_TmSk88] WITH CHECK ADD CHECK ([Time_Sk] = 88)
-- TimeSk 87
CREATE TABLE [dbo].[UsgFact_TmSk87] (
[Fact_Sk] [numeric](18, 0) IDENTITY (20000000, 1) NOT NULL ,
[Time_Sk] [numeric](18, 0) NOT NULL ,
[Main_Btn_Sk] [numeric](18, 0) NOT NULL ,
[Usage_Sk] [numeric](18, 0) NOT NULL ,
[Billable_Calls] [decimal](11, 0) NULL ,
[Billable_MOU] [decimal](11, 0) NULL ,
[Billable_Revenue] [decimal](13, 2) NULL ,
[Billed_Calls] [decimal](18, 0) NULL ,
[Billed_MOU] [decimal](11, 0) NULL ,
[Billed_Revenue] [decimal](13, 2) NULL
) ON [PRIMARY]
--sp_help Usage_Fact_Tmsk87
Insert Into UsgFact_TmSk87
(Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billable_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Billed_Revenue)
Select Top 1000
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billable_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Billed_Revenue From Usage_Fact_Tmsk87 ;
ALTER TABLE [dbo].[UsgFact_TmSk87] WITH CHECK ADD CONSTRAINT
[PK_UsgFact_TmpTmSk87] PRIMARY KEY CLUSTERED ([Time_Sk],[Fact_Sk]) ON
[PRIMARY]
ALTER TABLE [dbo].[UsgFact_TmSk87] WITH CHECK ADD CHECK ([Time_Sk] = 87)
Create View [dbo].[UsgFact] With SchemaBinding
As
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billable_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Billed_Revenue From [dbo].[UsgFact_TmSk88]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billable_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Billed_Revenue From [dbo].[UsgFact_TmSk87]
SET SHOWPLAN_ALL On
Select a.* From UsgFact a Where Time_Sk In (88,83);
Select a.* From UsgFact a Where Time_Sk In (88,83);
|--Compute
Scalar(DEFINE:([UsgFact_TmSk88].[Time_Sk]=[UsgFact_TmSk88].[Time_Sk],
[UsgFact_TmSk88].[Main_Btn_Sk]=[UsgFact_TmSk88].[Main_Btn_Sk],
[UsgFact_TmSk88].[Usage_Sk]=[UsgFact_TmSk88].[Usage_Sk],
[UsgFact_TmSk88].[Billable_Calls]=[UsgFact_TmSk88].[B
|--Clustered Index
Scan(OBJECT:([CVPT_B3].[dbo].[UsgFact_TmSk88].[PK_UsgFact_TmpTmSk88]))
Select a.* From UsgFact a Inner Join Time_Dimension b On b.Time_Sk =a.Time_Sk and b.Year_Month Between '200704' and '200704';
Select a.* From UsgFact a Inner Join Time_Dimension b On b.Time_Sk =a.Time_Sk and b.Year_Month Between '200704' and '200704';
|--Nested Loops(Inner Join, OUTER REFERENCES:([b].[Time_SK]))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([CVPT_B3].[dbo].[Time_Dimension] AS [b]))
| |--Index
Seek(OBJECT:([CVPT_B3].[dbo].[Time_Dimension].[Idx_TimeDim_YrMnth] AS [b]),
SEEK:([b].[Year_Month]='200704') ORDERED FORWARD)
|--Concatenation
|--Filter(WHERE:(STARTUP EXPR([b].[Time_SK]=88)))
| |--Clustered Index
Seek(OBJECT:([CVPT_B3].[dbo].[UsgFact_TmSk88].[PK_UsgFact_TmpTmSk88]),
SEEK:([UsgFact_TmSk88].[Time_Sk]=[b].[Time_SK]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([b].[Time_SK]=87)))
|--Clustered Index
Seek(OBJECT:([CVPT_B3].[dbo].[UsgFact_TmSk87].[PK_UsgFact_TmpTmSk87]),
SEEK:([UsgFact_TmSk87].[Time_Sk]=[b].[Time_SK]) ORDERED FORWARD)
Select a.* From UsgFact a Inner Join Time_Dimension b On b.Time_Sk =a.Time_Sk and b.Year_Month = '200704';
Select a.* From UsgFact a Inner Join Time_Dimension b On b.Time_Sk =a.Time_Sk and b.Year_Month = '200704';
|--Nested Loops(Inner Join, OUTER REFERENCES:([b].[Time_SK]))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([CVPT_B3].[dbo].[Time_Dimension] AS [b]))
| |--Index
Seek(OBJECT:([CVPT_B3].[dbo].[Time_Dimension].[Idx_TimeDim_YrMnth] AS [b]),
SEEK:([b].[Year_Month]='200704') ORDERED FORWARD)
|--Concatenation
|--Filter(WHERE:(STARTUP EXPR([b].[Time_SK]=88)))
| |--Clustered Index
Seek(OBJECT:([CVPT_B3].[dbo].[UsgFact_TmSk88].[PK_UsgFact_TmpTmSk88]),
SEEK:([UsgFact_TmSk88].[Time_Sk]=[b].[Time_SK]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([b].[Time_SK]=87)))
|--Clustered Index
Seek(OBJECT:([CVPT_B3].[dbo].[UsgFact_TmSk87].[PK_UsgFact_TmpTmSk87]),
SEEK:([UsgFact_TmSk87].[Time_Sk]=[b].[Time_SK]) ORDERED FORWARD)
Sr Apps Developer
"Dan Guzman" wrote:
> > I did look up a few notes in here, where in it says that the plan would
> > say
> > that all the tables are scanned but in truth only the wanted table is
> > scanned.
> As long as your view and underlying tables are setup correctly (primary key
> and check constraint), the execution plan will contain a startup expression
> to eliminate unneeded tables during execution.
> > But how would the time taken to run these 2 queries varry so much...
> I expect this is due to different execution plans. Can you post DDL
> (simplified CREATE TABLE and VIEW) that shows the issue? I recall that
> there might be cases where elimination doesn't occur via joins but indexes
> can still be used to efficiently return data.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "sabin" <sabin@.discussions.microsoft.com> wrote in message
> news:D9C7FF35-961C-481E-BF88-9D8933CD0332@.microsoft.com...
> > Been trying to start a new model with Partitioned Views. Its basically
> > Monthly data in Fact Tables with Time_Sk as Chk Constraint and the a View
> > on
> > Top of 12-24 tables. Thing i notice is that the query with Time_Sk In
> > (1,2)
> > gets me the data faster than Time_Sk In (Select Value from Table where
> > yrmnth
> > between '200701' and '200702') or a Join with TimeDim.
> >
> > I did look up a few notes in here, where in it says that the plan would
> > say
> > that all the tables are scanned but in truth only the wanted table is
> > scanned. But how would the time taken to run these 2 queries varry so
> > much...
> >
> > Any ideas ' Options '
> > --
> > Sr Apps Developer
>|||Thanks for the DDL, Sabin.
I noticed that your first script specified NOCHECK for the CHECK
constraints. This is probably just be an issue with the script (I see you
have CHECK in the second subsequent post and startup expressions in the
plans) but I want to point out that the constraint must be trusted in order
for the optimizer to avoid accessing tables unnecessarily. You can check
the CnstIsNotTrusted object property to ensure constraints are trusted. For
example
SELECT
OBJECT_NAME(parent_obj) AS TableName,
name AS CheckConstraintName,
OBJECTPROPERTYEX(id, 'CnstIsNotTrusted') AS CnstIsNotTrusted
FROM sysobjects
WHERE type = 'C'
The above query should return 0 for trusted constraints. Also, be sure you
specify the check constraints option during bulk load operations so that you
don't inadvertently change the constraint to non-trusted. It's a good idea
to explicitly name constraints to facilitate maintenance:
ALTER TABLE dbo.UsgFact_TmSk88
ADD CONSTRAINT CK_UsgFact_TmpTmSk88_Time_Sk
CHECK ([Time_Sk] = 88)
I ran your scripts for your simplified 2 fact table case and also created a
time dimension table the best I could by reverse engineering the execution
plans:
CREATE TABLE dbo.Time_Dimension
(
Time_Sk numeric(18, 0) NOT NULL,
Year_Month char(6) NOT NULL
)
CREATE INDEX Idx_TimeDim_YrMnth ON dbo.Time_Dimension(Year_Month)
DECLARE @.Time_Sk numeric(18, 0)
SET @.Time_Sk = 88
WHILE @.Time_Sk > 0
BEGIN
INSERT INTO dbo.Time_Dimension
VALUES(@.Time_Sk, CONVERT(char(6), DATEADD(month, 88 - @.Time_Sk,
'20070401'), 112));
SET @.Time_Sk = @.Time_Sk - 1
END
GO
I noticed that your plans showed a bookmark lookup for the Time_Dimension
table so it appears that table does not have a clustered index. Assuming
you have a non-clustered primary key on Time_Sk, I suggest you either change
it to clustered or add Time_Sk to your existing Idx_TimeDim_YrMnth index. I
think that will improve join performance.
Performance of a query with join obviously won't be as fast as the query
without a join due to the additional overhead. However I wouldn't expect
the difference to be prohibitive as long as proper indexes are in place.
I see that you are using numeric with scale 0 for your dimension keys. I
think a better choice would be int (or bigint). Integer types require less
overhead for both storage and query processing. That will also improve your
join performance.
Hope this helps.
Dan Guzman
SQL Server MVP
"sabin" <sabin@.discussions.microsoft.com> wrote in message
news:77938497-7262-4FDA-BAE4-F2AB5B5EA2A3@.microsoft.com...
> the view creation ddl...
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> /****** Object: View dbo.Product_Fact Script Date: 7/27/2007 11:21:18
> AM
> ******/
> CREATE View [dbo].[Product_Fact] With SchemaBinding
> AS
> Select
> Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Product_Sk,Unit_Rate,Prod_Qty,Rev_Amt
> From [dbo].[Product_Fact_TmpTmSk88]
> Union All Select
> Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Product_Sk,Unit_Rate,Prod_Qty,Rev_Amt
> From [dbo].[Product_Fact_TmpTmSk87]
> Union All Select
> Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Product_Sk,Unit_Rate,Prod_Qty,Rev_Amt
> From [dbo].[Product_Fact_TmpTmSk86]
> Union All Select
> Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Product_Sk,Unit_Rate,Prod_Qty,Rev_Amt
> From [dbo].[Product_Fact_TmpTmSk85]
> Union All Select
> Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Product_Sk,Unit_Rate,Prod_Qty,Rev_Amt
> From [dbo].[Product_Fact_TmpTmSk84]
> Union All Select
> Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Product_Sk,Unit_Rate,Prod_Qty,Rev_Amt
> From [dbo].[Product_Fact_TmpTmSk83]
> Union All Select
> Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Product_Sk,Unit_Rate,Prod_Qty,Rev_Amt
> From [dbo].[Product_Fact_TmpTmSk82]
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> /****** Object: View dbo.Usage_Fact Script Date: 7/27/2007 11:21:18 AM
> ******/
>
> CREATE View [dbo].[Usage_Fact] With SchemaBinding
> As
> Select
> Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billable_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Billed_Revenue
> From [dbo].[Usage_Fact_TmSk88]
> Union All
> Select
> Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billable_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Billed_Revenue
> From [dbo].[Usage_Fact_TmSk87]
> Union All
> Select
> Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billable_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Billed_Revenue
> From [dbo].[Usage_Fact_TmSk86]
> Union All
> Select
> Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billable_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Billed_Revenue
> From [dbo].[Usage_Fact_TmSk85]
> Union All
> Select
> Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billable_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Billed_Revenue
> From [dbo].[Usage_Fact_TmSk84]
> Union All
> Select
> Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billable_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Billed_Revenue
> From [dbo].[Usage_Fact_TmSk83]
> /*
> Union All
> Select * From [dbo].[Usage_Fact_TmSk82]
> Union All
> Select * From [dbo].[Usage_Fact_TmSk81]
> Union All
> Select * From [dbo].[Usage_Fact_TmSk80]
> Union All
> Select * From [dbo].[Usage_Fact_TmSk79]
> Union All
> Select * From [dbo].[Usage_Fact_TmSk78]
> Union All
> Select * From [dbo].[Usage_Fact_TmSk77]
> */
>
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> --
> Sr Apps Developer
>
> "sabin" wrote:
>> /****** Object: Table [dbo].[Product_Fact_TmpTmSk88] Script Date:
>> 7/27/2007 11:20:25 AM ******/
>> if exists (select * from dbo.sysobjects where id =>> object_id(N'[dbo].[Product_Fact_TmpTmSk88]') and OBJECTPROPERTY(id,
>> N'IsUserTable') = 1)
>> drop table [dbo].[Product_Fact_TmpTmSk88]
>> GO
>> /****** Object: Table [dbo].[Usage_Fact_TmSk88] Script Date:
>> 7/27/2007
>> 11:20:25 AM ******/
>> if exists (select * from dbo.sysobjects where id =>> object_id(N'[dbo].[Usage_Fact_TmSk88]') and OBJECTPROPERTY(id,
>> N'IsUserTable') = 1)
>> drop table [dbo].[Usage_Fact_TmSk88]
>> GO
>> /****** Object: Table [dbo].[Product_Fact_TmpTmSk88] Script Date:
>> 7/27/2007 11:20:27 AM ******/
>> CREATE TABLE [dbo].[Product_Fact_TmpTmSk88] (
>> [Fact_Sk] [numeric](18, 0) IDENTITY (150000000, 1) NOT NULL ,
>> [Time_Sk] [numeric](18, 0) NOT NULL ,
>> [Main_Btn_Sk] [numeric](18, 0) NOT NULL ,
>> [Entity_Sk] [numeric](18, 0) NOT NULL ,
>> [AssocChgCd_Sk] [numeric](18, 0) NOT NULL ,
>> [Product_Sk] [numeric](18, 0) NOT NULL ,
>> [Unit_Rate] [varchar] (13) NOT NULL ,
>> [Prod_Qty] [int] NULL ,
>> [Rev_Amt] [decimal](38, 2) NULL ,
>> [DateStamp] [datetime] NULL
>> ) ON [PRIMARY]
>> GO
>> /****** Object: Table [dbo].[Usage_Fact_TmSk88] Script Date:
>> 7/27/2007
>> 11:20:29 AM ******/
>> CREATE TABLE [dbo].[Usage_Fact_TmSk88] (
>> [Fact_Sk] [numeric](18, 0) IDENTITY (170000000, 1) NOT NULL ,
>> [Time_Sk] [numeric](18, 0) NOT NULL ,
>> [Main_Btn_Sk] [numeric](18, 0) NOT NULL ,
>> [Usage_Sk] [numeric](18, 0) NOT NULL ,
>> [Billable_Calls] [decimal](11, 0) NULL ,
>> [Billable_MOU] [decimal](11, 0) NULL ,
>> [Billable_Revenue] [decimal](13, 2) NULL ,
>> [Billed_Calls] [decimal](18, 0) NULL ,
>> [Billed_MOU] [decimal](11, 0) NULL ,
>> [Billed_Revenue] [decimal](13, 2) NULL
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[Product_Fact_TmpTmSk88] WITH NOCHECK ADD
>> CONSTRAINT [PK_Product_Fact_TmpTmSk88] PRIMARY KEY CLUSTERED
>> (
>> [Time_Sk],
>> [Fact_Sk]
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[Usage_Fact_TmSk88] WITH NOCHECK ADD
>> CONSTRAINT [PK_Usage_Fact_TmpTmSk88] PRIMARY KEY CLUSTERED
>> (
>> [Time_Sk],
>> [Fact_Sk]
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[Product_Fact_TmpTmSk88] WITH NOCHECK ADD
>> CHECK ([Time_Sk] = 88)
>> GO
>> ALTER TABLE [dbo].[Usage_Fact_TmSk88] WITH NOCHECK ADD
>> CHECK ([Time_Sk] = 88)
>> GO
>> CREATE UNIQUE INDEX [UK_Product_Fact_TmpTmSk88] ON
>> [dbo].[Product_Fact_TmpTmSk88]([Main_Btn_Sk], [Entity_Sk],
>> [AssocChgCd_Sk],
>> [Product_Sk], [Unit_Rate]) ON [PRD_INDXGRP]
>> GO
>> CREATE INDEX [Idx_PfTmsk88_TimeSK] ON
>> [dbo].[Product_Fact_TmpTmSk88]([Time_Sk]) ON [PRD_INDXGRP]
>> GO
>> CREATE INDEX [Idx_PfTmsk88_MainBTNSK] ON
>> [dbo].[Product_Fact_TmpTmSk88]([Main_Btn_Sk]) ON [PRD_INDXGRP]
>> GO
>> CREATE INDEX [Idx_PfTmsk88_ProductSK] ON
>> [dbo].[Product_Fact_TmpTmSk88]([Product_Sk]) ON [PRD_INDXGRP]
>> GO
>> CREATE UNIQUE INDEX [UK_Usage_Fact_TmpTmSk88] ON
>> [dbo].[Usage_Fact_TmSk88]([Main_Btn_Sk], [Usage_Sk]) ON [PRD_INDXGRP]
>> GO
>> CREATE INDEX [Idx_UfTmsk88_MainBtnSk] ON
>> [dbo].[Usage_Fact_TmSk88]([Main_Btn_Sk]) ON [PRD_INDXGRP]
>> GO
>> CREATE INDEX [Idx_UfTmsk88_UsageSk] ON
>> [dbo].[Usage_Fact_TmSk88]([Usage_Sk]) ON [PRD_INDXGRP]
>> GO
>>
>>
>> I did some simple checks as to run the Fact table with a join to just
>> the
>> dimension table...If i were to use a join or a subquery, it runs a scan
>> on
>> all the fact tables...BUt if i run the query on usage_fact with a where
>> condition as time_sk in (81,82), it just looks at the 81 and 82 table
>>
>> --
>> Sr Apps Developer
>>
>> "Dan Guzman" wrote:
>> > > I did look up a few notes in here, where in it says that the plan
>> > > would
>> > > say
>> > > that all the tables are scanned but in truth only the wanted table is
>> > > scanned.
>> >
>> > As long as your view and underlying tables are setup correctly (primary
>> > key
>> > and check constraint), the execution plan will contain a startup
>> > expression
>> > to eliminate unneeded tables during execution.
>> >
>> > > But how would the time taken to run these 2 queries varry so much...
>> >
>> > I expect this is due to different execution plans. Can you post DDL
>> > (simplified CREATE TABLE and VIEW) that shows the issue? I recall that
>> > there might be cases where elimination doesn't occur via joins but
>> > indexes
>> > can still be used to efficiently return data.
>> >
>> >
>> > --
>> > Hope this helps.
>> >
>> > Dan Guzman
>> > SQL Server MVP
>> >
>> > "sabin" <sabin@.discussions.microsoft.com> wrote in message
>> > news:D9C7FF35-961C-481E-BF88-9D8933CD0332@.microsoft.com...
>> > > Been trying to start a new model with Partitioned Views. Its
>> > > basically
>> > > Monthly data in Fact Tables with Time_Sk as Chk Constraint and the a
>> > > View
>> > > on
>> > > Top of 12-24 tables. Thing i notice is that the query with Time_Sk In
>> > > (1,2)
>> > > gets me the data faster than Time_Sk In (Select Value from Table
>> > > where
>> > > yrmnth
>> > > between '200701' and '200702') or a Join with TimeDim.
>> > >
>> > > I did look up a few notes in here, where in it says that the plan
>> > > would
>> > > say
>> > > that all the tables are scanned but in truth only the wanted table is
>> > > scanned. But how would the time taken to run these 2 queries varry so
>> > > much...
>> > >
>> > > Any ideas ' Options '
>> > > --
>> > > Sr Apps Developer
>> >