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 o
n
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 yrmnt
h
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/2
7/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/2
7/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], &
#91;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_INDXGR
P]
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:
> As long as your view and underlying tables are setup correctly (primary ke
y
> and check constraint), the execution plan will contain a startup expressio
n
> to eliminate unneeded tables during execution.
>
> 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...
>|||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,Re
v_Amt From [dbo].[Product_Fact_TmpTmSk88]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd
_Sk,Product_Sk,Unit_Rate,Prod_Qty,Re
v_Amt From [dbo].[Product_Fact_TmpTmSk87]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd
_Sk,Product_Sk,Unit_Rate,Prod_Qty,Re
v_Amt From [dbo].[Product_Fact_TmpTmSk86]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd
_Sk,Product_Sk,Unit_Rate,Prod_Qty,Re
v_Amt From [dbo].[Product_Fact_TmpTmSk85]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd
_Sk,Product_Sk,Unit_Rate,Prod_Qty,Re
v_Amt From [dbo].[Product_Fact_TmpTmSk84]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd
_Sk,Product_Sk,Unit_Rate,Prod_Qty,Re
v_Amt From [dbo].[Product_Fact_TmpTmSk83]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd
_Sk,Product_Sk,Unit_Rate,Prod_Qty,Re
v_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_Ca
lls,Billable_MOU,Billable_Revenue,Bi
lled_Calls,Billed_MOU,Billed_Revenue From [dbo].[Usage_Fact_TmSk88]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Ca
lls,Billable_MOU,Billable_Revenue,Bi
lled_Calls,Billed_MOU,Billed_Revenue From [dbo].[Usage_Fact_TmSk87]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Ca
lls,Billable_MOU,Billable_Revenue,Bi
lled_Calls,Billed_MOU,Billed_Revenue From [dbo].[Usage_Fact_TmSk86]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Ca
lls,Billable_MOU,Billable_Revenue,Bi
lled_Calls,Billed_MOU,Billed_Revenue From [dbo].[Usage_Fact_TmSk85]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Ca
lls,Billable_MOU,Billable_Revenue,Bi
lled_Calls,Billed_MOU,Billed_Revenue From [dbo].[Usage_Fact_TmSk84]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Ca
lls,Billable_MOU,Billable_Revenue,Bi
lled_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:
[vbcol=seagreen]
> /****** Object: Table [dbo].[Product_Fact_TmpTmSk88] Script Da
te:
> 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 Da
te:
> 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_INDX
GRP]
> GO
> CREATE INDEX [Idx_PfTmsk88_ProductSK] ON
> [dbo].[Product_Fact_TmpTmSk88]([Product_Sk]) ON [PRD_INDXG
RP]
> GO
> CREATE UNIQUE INDEX [UK_Usage_Fact_TmpTmSk88] ON
> [dbo].[Usage_Fact_TmSk88]([Main_Btn_Sk], [Usage_Sk]) ON &#
91;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 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_C
alls,Billable_MOU,Billable_Revenue,B
illed_Calls,Billed_MOU,Billed_Revenue)
Select Top 1000
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Ca
lls,Billable_MOU,Billable_Revenue,Bi
lled_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_S
k]) ON
[PRIMARY]
ALTER TABLE [dbo].[UsgFact_TmSk88] WITH CHECK ADD CHECK ([Time_S
k] = 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_C
alls,Billable_MOU,Billable_Revenue,B
illed_Calls,Billed_MOU,Billed_Revenue)
Select Top 1000
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Ca
lls,Billable_MOU,Billable_Revenue,Bi
lled_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_S
k]) ON
[PRIMARY]
ALTER TABLE [dbo].[UsgFact_TmSk87] WITH CHECK ADD CHECK ([Time_S
k] = 87)
Create View [dbo].[UsgFact] With SchemaBinding
As
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Ca
lls,Billable_MOU,Billable_Revenue,Bi
lled_Calls,Billed_MOU,Billed_Revenue From [dbo].[UsgFact_TmSk88]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Ca
lls,Billable_MOU,Billable_Revenue,Bi
lled_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_Tm
pTmSk88]))
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_Y
rMnth] 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_Tm
pTmSk88]),
SEEK
[UsgFact_TmSk88].[Time_Sk]=[b].[Time_SK]) ORDERED FORW
ARD)
|--Filter(WHERE
STARTUP EXPR([b].[Time_SK]=87)))
|--Clustered Index
Seek(OBJECT
[CVPT_B3].[dbo].[UsgFact_TmSk87].[PK_UsgFact_Tm
pTmSk87]),
SEEK
[UsgFact_TmSk87].[Time_Sk]=[b].[Time_SK]) ORDERED FORW
ARD)
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_Y
rMnth] 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_Tm
pTmSk88]),
SEEK
[UsgFact_TmSk88].[Time_Sk]=[b].[Time_SK]) ORDERED FORW
ARD)
|--Filter(WHERE
STARTUP EXPR([b].[Time_SK]=87)))
|--Clustered Index
Seek(OBJECT
[CVPT_B3].[dbo].[UsgFact_TmSk87].[PK_UsgFact_Tm
pTmSk87]),
SEEK
[UsgFact_TmSk87].[Time_Sk]=[b].[Time_SK]) ORDERED FORW
ARD)
Sr Apps Developer
"Dan Guzman" wrote:
> As long as your view and underlying tables are setup correctly (primary ke
y
> and check constraint), the execution plan will contain a startup expressio
n
> to eliminate unneeded tables during execution.
>
> 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...
>|||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...[vbcol=seagreen]
> 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_Ca
lls,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_Ca
lls,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_Ca
lls,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_Ca
lls,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_Ca
lls,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_Ca
lls,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:
>
behavior to eliminate the other Origin.