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
>> >

Partitioned View Pruning Issue

I'm

using Sql Server 2000 Standard Edition,

service pack 3. I'm fairly new to

partitioned views and merge replication.

We have a 3-year set of monthly

tables with about 1 million rows per table from some code we inherited. To ease development, I'm trying to create a

local partitioned view. We wanted it to be UPDATE-able and be able to select

data by a date range or by id without incurring very much cost. The problem is

that my SELECT statements correctly prune the unneeded tables while my INSERT, UPDATE ,

and DELETE statements do not.

Here's our basic structure:

CREATE

TABLE [dbo].[Call_2004_01] (
[id] [varchar] (64) COLLATE

SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[callStartedOn] [datetime] NOT

NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT

[DF_Call_2004_01_rowguid] DEFAULT (newid()),
CONSTRAINT

[PK_Call_2004_01] PRIMARY KEY CLUSTERED
(
[id]
)

WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_Call_2004_01_id]

CHECK ([id] >= '200401' and [id] < '200402')
) ON

[PRIMARY]

CREATE TABLE [dbo].[Call_2004_02] (
[id] [varchar] (64)

COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[callStartedOn]

[datetime] NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL

CONSTRAINT [DF_Call_2004_02_rowguid] DEFAULT (newid()),
CONSTRAINT

[PK_Call_2004_02] PRIMARY KEY CLUSTERED
(
[id]
)

WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_Call_2004_02_id]

CHECK ([id] >= '200402' and [id] < '200403')
) ON

[PRIMARY]

CREATE VIEW
Call
AS
SELECT * FROM

dbo.Call_2004_02
UNION ALL
SELECT * FROM

dbo.Call_2004_01
GO

The ids themselves contain the date information,

so we decided to use the id as the partition key. (I originally had the date and

the id as the primary key and it still didn't work.)

If you

run:

SET STATISTICS IO ON
SELECT * FROM Call WHERE [id] =

'20040104-fake-asdf'

You'll get the exact query plan and IO you would

expect. It only shows the Call_2004_01 table. However, if you run:

SET

STATISTICS IO ON
UPDATE Call SET [callStartedOn] = '2004-01-01 00:00:00.000'

WHERE [id] = '20040104-fake-asdf'

or

SET STATISTICS IO

ON
DELETE FROM Call WHERE [id] = '20040104-fake-asdf'

It scans all of

the tables in the view. If I add an index on top of the primary key, the

STATISTICS IO comes back as 0's for the unused table, but the query plan still

shows execution on them.

To add injury to insult, we have merge

replication set up for these tables. So, when the INSERT, UPDATE, or DELETE statement

occurs on the tables that it isn't supposed to run on, the triggers for those

tables fire. This becomes apparent when you look at the STATISTICS IO, query

plan, and/or the fact that a simple query which should take less than 1 second

is now taking at least 8.

The problem goes away, of course, when you

specify the table name instead of the view.

So, what am I missing here?

At this point, I'm just grasping at straws.

Also, I tried upgrading to service pack 4 just now and that didn't change anything.|||

It is a bug in SQL Server 2000 due to auto-parameterization. It has been fixed in SQL Server 2005 so your examples will work fine there. You can make it work in SQL Server 2000 by adding some bogus parameter to the WHERE clause of the UPDATE & DELETE statement.

declare @.p int

SET STATISTICS IO ON

UPDATE Call SET [callStartedOn] = '2004-01-01 00:00:00.000'

WHERE [id] = '20040104-fake-asdf' and @.p is null

go

declare @.p int

SET STATISTICS IO ON

DELETE FROM Call WHERE [id] = '20040104-fake-asdf' and @.p is null

go

To summarize, the partition pruning will not happen in cases where auto-parameterization of the statement occurs. This typically happens in cases where you use constants/literals in WHERE clause.

|||

Thanks. I'll give that a try when I get back to work tomorrow. However, I had originally been trying with a variable when I noticed the problem. Something like

DECLARE @.callId varchar(64)
SET @.callId = '20040104-fake-asdf'
DELETE FROM Call WHERE [id] = @.callId

Maybe the bogus data will help.

|||The bogus data helped some. Now it's behaving the same way it would if I manually created additional indexes on the PK. However, it's still not properly pruning the data out of the view. Here's the query I'm running and the output I'm getting.

DECLARE @.p bit
SET @.p = NULL

DECLARE @.callId varchar(64)
SET @.callId = '2004010100281315'

DELETE FROM Call WHERE [id] = @.callId AND @.p IS NULL

STATISTICS IO (I deleted quite a bit):
Table 'Call_2006_12'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
... [All other tables between are the same as the above] ...
Table 'Call_2004_02'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'Call_2004_01'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

(0 row(s) affected)

Table 'sysmergearticles'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.
Table 'MSmerge_replinfo'. Scan count 22, logical reads 44, physical reads 0, read-ahead reads 0.
Table 'sysmergesubscriptions'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'MSmerge_tombstone'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'MSmerge_contents'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sysmergearticles'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.

(15 row(s) affected)

[the above (starting with the first "Table 'sysmergearticles'") repeats 35 more times]

SHOWPLAN_TEXT Results (again, edited for size):
|--Sequence
|--Clustered Index Delete(OBJECT:([Callbright].[dbo].[Call_2004_01].[PK_Call_2004_01]))
| |--Filter(WHERE:(STARTUP EXPR([@.p]=NULL)))
| |--Filter(WHERE:(STARTUP EXPR([@.callId]<'200402' AND [@.callId]>='200401')))
| |--Clustered Index Seek(OBJECT:([Callbright].[dbo].[Call_2004_01].[PK_Call_2004_01]), SEEK:([Call_2004_01].[id]=[@.callId]) ORDERED FORWARD)
... [All other tables between are the same as the above] ...
|--Clustered Index Delete(OBJECT:([Callbright].[dbo].[Call_2006_12].[PK_Call_2006_12]))
|--Filter(WHERE:(STARTUP EXPR([@.p]=NULL)))
|--Filter(WHERE:(STARTUP EXPR([@.callId]<'200701' AND [@.callId]>='200612')))
|--Clustered Index Seek(OBJECT:([Callbright].[dbo].[Call_2006_12].[PK_Call_2006_12]), SEEK:([Call_2006_12].[id]=[@.callId]) ORDERED FORWARD)

Also, I should mention that the Clustered Index Delete takes 2% of the execution time and performs 1 execution while the Clustered Index Seek takes 1% of the execution time and performs 0 executions on the tables that should be pruned.

Any ideas?|||

There are two ways in which the partitions can be eliminated and it depends on the type of query.

1. Partition elimination at compile-time - This will happen usually if you specify the partition key value as a constant in the WHERE clause. You can observe this behavior in the showplan output (estimated or actual execution plan)

2. Partition elimination at run-time - This will happen in cases where the partition key value is not known at compile-time or the plan needs to handle cases where the key could change between executions and so on. In this case, you will not see the partition(s) being eliminated in the showplan output (estimated or actual). You will however see STARTUP EXPR filters in the various branches. You can think of this as short-circuit mechanisms that gets evaluated at run-time and prevents that branch from executing.

So given these different plan strategies, the best way to determine which partition(s) are being eliminated is to look at the SET STATISTICS IO output and look for read counters with zero values to identify the partition(s) that are being eliminated. Another technique is to look at SET STATISTICS PROFILE ON (or SET STATISTICS XML PROFILE ON in SQL Server 2005) output. In this case, you will get the actual execution plan along with the execution statistics. You would specifically look at rows in the SET STATISTICS PROFILE output (In SQL Server 2000) for rows which have EXECUTES = 0 and/or ROWS = 0. This will tell you the partition(s) that were eliminated.

To summarize, from your showplan output the partition elimination is happening fine and it uses mechanism #2 described above. As for the replication tables, my guess is that it is a result of the replication trigger on the table that is propogating the deletes. Hope this helps.

|||I would have thought that if the partition was actually doing any pruning that the merge subscription triggers on the pruned tables wouldn't be firing. Is that wrong?|||Here's my query and SET STATISTICS PROFILE ON output. As you can see, it's still performing 3 executions per "pruned" table (and one of those is a delete). Shouldn't this be all zeros except for the Call_2004_01 table?

SET STATISTICS PROFILE ON
DECLARE @.bogusVariable bit
DELETE FROM Call WHERE [id] = '2004010100000000001' AND @.bogusVariable IS NULL

0 1 DELETE FROM Call WHERE [id] = '2004010100000000001' AND @.bogusVariable IS NULL 436 1 0 NULL NULL NULL NULL 1.0 NULL NULL NULL 0.4912329 NULL NULL DELETE 0 NULL
0 1 |--Sequence 436 2 1 Sequence Sequence NULL NULL 1.0 0.0 3.6000001E-5 56 0.4912329 NULL NULL PLAN_ROW 0 1.0
0 1 |--Clustered Index Delete(OBJECT:([DB].[dbo].[Call_2004_01].[PK_Call_2004_01])) 436 3 2 Clustered Index Delete Delete OBJECT:([DB].[dbo].[Call_2004_01].[PK_Call_2004_01]) NULL 1.0 1.0161194E-2 0.000001 56 1.3445652E-2 NULL NULL PLAN_ROW 0 1.0
0 1 | |--Filter(WHERE:(STARTUP EXPR([@.bogusVariable]=NULL))) 436 5 3 Filter Filter WHERE:(STARTUP EXPR([@.bogusVariable]=NULL)) NULL 1.0 0.0 2.7999999E-7 311 3.2833579E-3 [Bmk1006] NULL PLAN_ROW 0 1.0
0 1 | |--Clustered Index Seek(OBJECT:([DB].[dbo].[Call_2004_01].[PK_Call_2004_01]), SEEK:([Call_2004_01].[id]='2004010100000000001') ORDERED FORWARD) 436 6 5 Clustered Index Seek Clustered Index Seek OBJECT:([DB].[dbo].[Call_2004_01].[PK_Call_2004_01]), SEEK:([Call_2004_01].[id]='2004010100000000001') ORDERED FORWARD [Bmk1006] 1.0 3.2034749E-3 7.9603E-5 311 3.2830781E-3 [Bmk1006] NULL PLAN_ROW 0 1.0
0 1 |--Clustered Index Delete(OBJECT:([DB].[dbo].[Call_2004_02].[PK_Call_2004_02])) 436 9 2 Clustered Index Delete Delete OBJECT:([DB].[dbo].[Call_2004_02].[PK_Call_2004_02]) NULL 1.0 1.0161523E-2 0.000001 56 1.3445981E-2 NULL NULL PLAN_ROW 0 1.0
0 1 | |--Filter(WHERE:(STARTUP EXPR([@.bogusVariable]=NULL))) 436 11 9 Filter Filter WHERE:(STARTUP EXPR([@.bogusVariable]=NULL)) NULL 1.0 0.0 2.7999999E-7 307 3.2833579E-3 [Bmk1008] NULL PLAN_ROW 0 1.0
0 1 | |--Filter(WHERE:(STARTUP EXPR(0))) 436 12 11 Filter Filter WHERE:(STARTUP EXPR(0)) NULL 1.0 3.2034749E-3 7.9603E-5 307 3.2830781E-3 [Bmk1008] NULL PLAN_ROW 0 1.0
0 0 | |--Clustered Index Seek(OBJECT:([DB].[dbo].[Call_2004_02].[PK_Call_2004_02]), SEEK:([Call_2004_02].[id]='2004010100000000001') ORDERED FORWARD) 436 13 12 Clustered Index Seek Clustered Index Seek OBJECT:([DB].[dbo].[Call_2004_02].[PK_Call_2004_02]), SEEK:([Call_2004_02].[id]='2004010100000000001') ORDERED FORWARD [Bmk1008] 1.0 3.2034749E-3 7.9603E-5 307 3.2830781E-3 [Bmk1008] NULL PLAN_ROW 0 1.0
... [The rest of the tables are here and mirror the above statement] ...
0 1 |--Clustered Index Delete(OBJECT:([DB].[dbo].[Call_2006_12].[PK_Call_2006_12])) 436 281 2 Clustered Index Delete Delete OBJECT:([DB].[dbo].[Call_2006_12].[PK_Call_2006_12]) NULL 1.0 1.6756756E-2 0.000001 56 2.0041214E-2 NULL NULL PLAN_ROW 0 1.0
0 1 |--Filter(WHERE:(STARTUP EXPR([@.bogusVariable]=NULL))) 436 283 281 Filter Filter WHERE:(STARTUP EXPR([@.bogusVariable]=NULL)) NULL 1.0 0.0 2.7999999E-7 324 3.2833579E-3 [Bmk1552] NULL PLAN_ROW 0 1.0
0 1 |--Filter(WHERE:(STARTUP EXPR(0))) 436 284 283 Filter Filter WHERE:(STARTUP EXPR(0)) NULL 1.0 3.2034749E-3 7.9603E-5 324 3.2830781E-3 [Bmk1552] NULL PLAN_ROW 0 1.0
0 0 |--Clustered Index Seek(OBJECT:([DB].[dbo].[Call_2006_12].[PK_Call_2006_12]), SEEK:([Call_2006_12].[id]='2004010100000000001') ORDERED FORWARD) 436 285 284 Clustered Index Seek Clustered Index Seek OBJECT:([DB].[dbo].[Call_2006_12].[PK_Call_2006_12]), SEEK:([Call_2006_12].[id]='2004010100000000001') ORDERED FORWARD [Bmk1552] 1.0 3.2034749E-3 7.9603E-5 324 3.2830781E-3 [Bmk1552] NULL PLAN_ROW 0 1.0

After this, there's a bunch of stuff for merge replication which really means the triggers on the "pruned" tables fired. To me, this indicates some kind of a pruning error (the table definitions are wrong, sql server 2000 is wrong, etc.). Am I wrong in thinking that? Does Sql Server 2000 execute insert/update/delete statements this way by design?|||

Don't look at the DELETE operator executes only. Start with the clustered index seek for each partition and you can see how many rows it produces & the number of executes. This way you can figure out if a parent operator (delete in this case) did any work. I tried your example with some sample data and following DELETE statement:

-- I added one row in each partition:

DECLARE @.p bit
DELETE FROM Call WHERE [id] = '20040104-fake-asdf' and @.p is null

-- set statistics io output

/*

Table 'Call_2004_01'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'Call_2004_02'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
*/

-- set statistics profile partial output

/*

Rows Executes StmtText

- -- -
1 1 DELETE FROM Call WHERE [id] = '20040104-fake-asdf' COLLATE SQL_Latin1_General_CP1_CI_AS and @.p is null
1 1 |--Sequence
0 1 |--Clustered Index Delete(OBJECT:([MyTestDB].[dbo].[Call_2004_02].[PK_Call_2004_02]))
0 1 | |--Filter(WHERE:(STARTUP EXPR([@.p]=NULL)))
0 1 | |--Filter(WHERE:(STARTUP EXPR(0)))
0 0 | |--Clustered Index Seek(OBJECT:([MyTestDB].[dbo].[Call_2004_02].[PK_Call_2004_02]), SEEK:([Call_2004_02].[id]='20040104-fake-asdf') ORDERED FORWARD)
1 1 |--Clustered Index Delete(OBJECT:([MyTestDB].[dbo].[Call_2004_01].[PK_Call_2004_01]))
1 1 |--Filter(WHERE:(STARTUP EXPR([@.p]=NULL)))
1 1 |--Clustered Index Seek(OBJECT:([MyTestDB].[dbo].[Call_2004_01].[PK_Call_2004_01]), SEEK:([Call_2004_01].[id]='20040104-fake-asdf') ORDERED FORWARD)

*/

Now, zero rows went through the delete operators but we will still fire the triggers on the partition tables. The triggers should be intelligent enough to perform no work in this case. This is also no different than doing DELETE t WHERE 1=0 which will still fire the trigger. This may seem kind of strange but this is how triggers work. There is one exception in case of distributed partitioned views where the triggers may not fire on the remote tables. Hope this helps explain the behavior you are seeing.

And you should start a different thread in the replication newsgroup about their trigger behavior in this case.

|||

I guess I figured that the view would prevent any execution on a table that didn't meet the constraints, but I can see how your example is related to this situation. The subscription triggers are smart enough not to perform any inserts, but they do perform enough work to cause problems. I'd considered modifying them to make them do even less work (where it would check the Inserted and Deleted tables for any rows before executing), but maintenance on those triggers seemed painful since they're auto-generated, so instead, I added INSTEAD OF triggers to the view that prune the tables the way I expected them to be pruned.

Thanks.

partitioned view goofiness

ok, i have a partitoned view of 5 years worth or
partitioned monthly data. each undertable has a check
constraint on a date column
when i run this I get a proper execution plan (note i am
reviewing the actual execution plan, not the estimated)
select * from viewname
where datecol = cast('20041101' as datetime)
but when I run either of these, my execution plans show I
hit all 60 undertables (I tried with cast AND convert and
get the same)
declare @.dt datetime
set @.dt = '20041101'
select * from viewname
where datecol = @.dt
declare @.dt datetime
set @.dt = cast('20041101' as datetime)
select * from viewname
where datecol = @.dt
what am I doing wrong? I want to put the date as a
parameter in a stored procedure but the results are
terrible because the server hits all 60 undertables.
Help pleaseHi
Although it is not the same example as documented in "The Guru's Guide to
SQL Server Architecture and Internals" by Ken Henderson ISBN 0-201-70047-6
goes does talk about query plans for partitioned views and he shows that
although you may see the other partitions in the plan they should have 0
executions.
John
"Buzzer" <anonymous@.discussions.microsoft.com> wrote in message
news:062801c53953$76239780$a501280a@.phx.gbl...
> ok, i have a partitoned view of 5 years worth or
> partitioned monthly data. each undertable has a check
> constraint on a date column
> when i run this I get a proper execution plan (note i am
> reviewing the actual execution plan, not the estimated)
> select * from viewname
> where datecol = cast('20041101' as datetime)
> but when I run either of these, my execution plans show I
> hit all 60 undertables (I tried with cast AND convert and
> get the same)
> declare @.dt datetime
> set @.dt = '20041101'
> select * from viewname
> where datecol = @.dt
> declare @.dt datetime
> set @.dt = cast('20041101' as datetime)
> select * from viewname
> where datecol = @.dt
>
> what am I doing wrong? I want to put the date as a
> parameter in a stored procedure but the results are
> terrible because the server hits all 60 undertables.
> Help please
>|||Sorry John, that is not the case. Each undertable
contributes a healthy percentage to the overall
performance of the execution plan and the query. Yes as
you mentioned, the rows are zero, but each has a cost.
The problem I believe revolves around the datetime issue.
When I compare the actual execution plans of using "cast
('20041101' as datetime)" versus "@.dt" in the where
clause, the difference is about 90% to 10% where the 90%
is the for the variable @.dt.
Maybe someone from Microsoft can help. Anyone?

>--Original Message--
>Hi
>Although it is not the same example as documented in "The
Guru's Guide to
>SQL Server Architecture and Internals" by Ken Henderson
ISBN 0-201-70047-6
>goes does talk about query plans for partitioned views
and he shows that
>although you may see the other partitions in the plan
they should have 0
>executions.
>John
>"Buzzer" <anonymous@.discussions.microsoft.com> wrote in
message
>news:062801c53953$76239780$a501280a@.phx.gbl...
I
and
>
>.
>|||On Tue, 5 Apr 2005 05:20:05 -0700, "Buzzer"
<anonymous@.discussions.microsoft.com> wrote:
>Sorry John, that is not the case. Each undertable
>contributes a healthy percentage to the overall
>performance of the execution plan and the query. Yes as
>you mentioned, the rows are zero, but each has a cost.
>The problem I believe revolves around the datetime issue.
>When I compare the actual execution plans of using "cast
>('20041101' as datetime)" versus "@.dt" in the where
>clause, the difference is about 90% to 10% where the 90%
>is the for the variable @.dt.
>Maybe someone from Microsoft can help. Anyone?
Well, it's not "data sniffing" correctly.
I wonder if you put each of the cases into a separate stored procedure
and do your query plans and comparisons, if that would work better.
Josh|||[Sorry about responding to the reply, but the original is not
on msnews.microsoft.com]
The "show execution plan" percentages are estimates, so unless
you have confirmed the 90%/10% figure you quote with real
timings and table access, it's meaningless.
Also, you say "each has a cost", but the fact is that "show execution
plan" does not show actual costs. It only shows estimated cost.
The actual cost of access to tables that are not accessed is probably
zero.
If you have *actual* costs to show there is a difference here, as
opposed to estimated costs, let us know. Estimated costs are not
something you need to worry about.
The reason you see this is fairly simple. The optimizer
can figure out which table contains '20041101', so it shows
the specific table that will be accessed.
When you use @.dt, the optimizer still knows the first thing to do
is find out what table to access, but the optimizer doesn't evaluate
@.dt itself - it creates a plan where the first step is to choose the
correct table. Most likely, the only real difference in cost here
is that a tiny bit of the work (picking the table) is done by the
optimizer in one case and the query processor in the other. More
than likely, you will have the same actual cost in each case.
Just don't assume that the costs and percentages in "show
[actual] execution plan" are for real. The only actual values
this shows are for Rowcount and Executes. The rest are
the same estimates you get with estimated execution plan.
Steve Kass
Drew University
JRStern wrote:

>On Tue, 5 Apr 2005 05:20:05 -0700, "Buzzer"
><anonymous@.discussions.microsoft.com> wrote:
>
>Well, it's not "data sniffing" correctly.
>I wonder if you put each of the cases into a separate stored procedure
>and do your query plans and comparisons, if that would work better.
>Josh
>
>