Parsing web URL's in SQL

Hello everyone,
I was wondering if anyone could help me out. Basically I have several
sharepoint sites for applications that I run. Each applications
sharepoint site needs to display information about the application
that is located in a SQL database. So for example:
www.qwerty.com/application1/home.html
www.qwerty.com/application2/home.html
www.qwerty.com/application3/home.html
I need to find a way that SQL, Sharepoint, some code, would take the
web address, parse, and truncate it to only read "Application1."
Then I could write a SQL statement to get the data from Application1's
database to display on the sharepoint site. However I have several
application pages, and I would like to be able to have some type of
code that would automatically do it for all the applications, instead
of manually coding/quiering for the data for each application
page.....it would just take too long.
if anyone has any ideas, feel free to share. Thank you in advance, for
all your time and help it is greatly appreciated!
--A4orce84
If the part you want is always the next to last section, then this
would appear to provide what is needed. A bit convoluted though!
CREATE TABLE Demo (url varchar(100) not null)
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
SELECT REVERSE(
SUBSTRING(REVERSE(url),
charindex('/', REVERSE(url)) +1,
charindex('/', REVERSE(url), charindex('/',
REVERSE(url)) + 1) -
charindex('/', REVERSE(url)) - 1))
FROM Demo
application1
application2
application3
banana
Roy Harvey
Beacon Falls, CT
On Fri, 08 Jun 2007 09:01:28 -0700, Asif_Ahmad@.dell.com wrote:

>Hello everyone,
>I was wondering if anyone could help me out. Basically I have several
>sharepoint sites for applications that I run. Each applications
>sharepoint site needs to display information about the application
>that is located in a SQL database. So for example:
>www.qwerty.com/application1/home.html
>www.qwerty.com/application2/home.html
>www.qwerty.com/application3/home.html
>I need to find a way that SQL, Sharepoint, some code, would take the
>web address, parse, and truncate it to only read "Application1."
>Then I could write a SQL statement to get the data from Application1's
>database to display on the sharepoint site. However I have several
>application pages, and I would like to be able to have some type of
>code that would automatically do it for all the applications, instead
>of manually coding/quiering for the data for each application
>page.....it would just take too long.
>if anyone has any ideas, feel free to share. Thank you in advance, for
>all your time and help it is greatly appreciated!
>--A4orce84
|||Ron,
Is this if you only know the number of applications you have? I think
there are several hundred I am using particularly. It also appears
from your code that you have to enter the physical address into the
code by your:
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
I wanted a way that it would do it automatically, that way if servers
change and things get moved around, or they add more applications it
would be easy to keep maintaining the SQL. Or I may have completely
mis-interpreted your code, either way thank you for your help. Any
additional information you could provide would be helpful!
|||I assumed that the URLs you needed to parse would already be in a SQL
Server database table. (Why else would you be trying to parse a URL
in SQL Server?) So my CREATE TABLE and INSERT statements were simply
there to create some test data for demonstration purposes. The number
of URLs in your table is irrelevant to the parsing process.
What I intended to demonstrate was a way to pick out the next to last
section of the URL, which was what I thought you were aiming for. I
believe the code provided does that.
Good luck!
Roy Harvey
Beacon Falls, CT
On Sun, 10 Jun 2007 15:04:14 -0700, Asif_Ahmad@.dell.com wrote:

>Ron,
>Is this if you only know the number of applications you have? I think
>there are several hundred I am using particularly. It also appears
>from your code that you have to enter the physical address into the
>code by your:
>
>INSERT Demo values ('www.qwerty.com/application1/home.html')
>INSERT Demo values ('www.qwerty.com/application2/home.html')
>INSERT Demo values ('www.qwerty.com/application3/home.html')
>INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
>
>I wanted a way that it would do it automatically, that way if servers
>change and things get moved around, or they add more applications it
>would be easy to keep maintaining the SQL. Or I may have completely
>mis-interpreted your code, either way thank you for your help. Any
>additional information you could provide would be helpful!

Parsing web URL's in SQL

Hello everyone,
I was wondering if anyone could help me out. Basically I have several
sharepoint sites for applications that I run. Each applications
sharepoint site needs to display information about the application
that is located in a SQL database. So for example:
www.qwerty.com/application1/home.html
www.qwerty.com/application2/home.html
www.qwerty.com/application3/home.html
I need to find a way that SQL, Sharepoint, some code, would take the
web address, parse, and truncate it to only read "Application1."
Then I could write a SQL statement to get the data from Application1's
database to display on the sharepoint site. However I have several
application pages, and I would like to be able to have some type of
code that would automatically do it for all the applications, instead
of manually coding/quiering for the data for each application
page.....it would just take too long.
if anyone has any ideas, feel free to share. Thank you in advance, for
all your time and help it is greatly appreciated!
--A4orce84If the part you want is always the next to last section, then this
would appear to provide what is needed. A bit convoluted though!
CREATE TABLE Demo (url varchar(100) not null)
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
SELECT REVERSE(
SUBSTRING(REVERSE(url),
charindex('/', REVERSE(url)) +1,
charindex('/', REVERSE(url), charindex('/',
REVERSE(url)) + 1) -
charindex('/', REVERSE(url)) - 1))
FROM Demo
--
application1
application2
application3
banana
Roy Harvey
Beacon Falls, CT
On Fri, 08 Jun 2007 09:01:28 -0700, Asif_Ahmad@.dell.com wrote:
>Hello everyone,
>I was wondering if anyone could help me out. Basically I have several
>sharepoint sites for applications that I run. Each applications
>sharepoint site needs to display information about the application
>that is located in a SQL database. So for example:
>www.qwerty.com/application1/home.html
>www.qwerty.com/application2/home.html
>www.qwerty.com/application3/home.html
>I need to find a way that SQL, Sharepoint, some code, would take the
>web address, parse, and truncate it to only read "Application1."
>Then I could write a SQL statement to get the data from Application1's
>database to display on the sharepoint site. However I have several
>application pages, and I would like to be able to have some type of
>code that would automatically do it for all the applications, instead
>of manually coding/quiering for the data for each application
>page.....it would just take too long.
>if anyone has any ideas, feel free to share. Thank you in advance, for
>all your time and help it is greatly appreciated!
>--A4orce84|||Ron,
Is this if you only know the number of applications you have? I think
there are several hundred I am using particularly. It also appears
from your code that you have to enter the physical address into the
code by your:
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
I wanted a way that it would do it automatically, that way if servers
change and things get moved around, or they add more applications it
would be easy to keep maintaining the SQL. Or I may have completely
mis-interpreted your code, either way thank you for your help. Any
additional information you could provide would be helpful!|||I assumed that the URLs you needed to parse would already be in a SQL
Server database table. (Why else would you be trying to parse a URL
in SQL Server?) So my CREATE TABLE and INSERT statements were simply
there to create some test data for demonstration purposes. The number
of URLs in your table is irrelevant to the parsing process.
What I intended to demonstrate was a way to pick out the next to last
section of the URL, which was what I thought you were aiming for. I
believe the code provided does that.
Good luck!
Roy Harvey
Beacon Falls, CT
On Sun, 10 Jun 2007 15:04:14 -0700, Asif_Ahmad@.dell.com wrote:
>Ron,
>Is this if you only know the number of applications you have? I think
>there are several hundred I am using particularly. It also appears
>from your code that you have to enter the physical address into the
>code by your:
>
>INSERT Demo values ('www.qwerty.com/application1/home.html')
>INSERT Demo values ('www.qwerty.com/application2/home.html')
>INSERT Demo values ('www.qwerty.com/application3/home.html')
>INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
>
>I wanted a way that it would do it automatically, that way if servers
>change and things get moved around, or they add more applications it
>would be easy to keep maintaining the SQL. Or I may have completely
>mis-interpreted your code, either way thank you for your help. Any
>additional information you could provide would be helpful!

Parsing web URL's in SQL

Hello everyone,
I was wondering if anyone could help me out. Basically I have several
sharepoint sites for applications that I run. Each applications
sharepoint site needs to display information about the application
that is located in a SQL database. So for example:
www.qwerty.com/application1/home.html
www.qwerty.com/application2/home.html
www.qwerty.com/application3/home.html
I need to find a way that SQL, Sharepoint, some code, would take the
web address, parse, and truncate it to only read "Application1."
Then I could write a SQL statement to get the data from Application1's
database to display on the sharepoint site. However I have several
application pages, and I would like to be able to have some type of
code that would automatically do it for all the applications, instead
of manually coding/quiering for the data for each application
page.....it would just take too long.
if anyone has any ideas, feel free to share. Thank you in advance, for
all your time and help it is greatly appreciated!
--A4orce84If the part you want is always the next to last section, then this
would appear to provide what is needed. A bit convoluted though!
CREATE TABLE Demo (url varchar(100) not null)
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
SELECT REVERSE(
SUBSTRING(REVERSE(url),
charindex('/', REVERSE(url)) +1,
charindex('/', REVERSE(url), charindex('/',
REVERSE(url)) + 1) -
charindex('/', REVERSE(url)) - 1))
FROM Demo
application1
application2
application3
banana
Roy Harvey
Beacon Falls, CT
On Fri, 08 Jun 2007 09:01:28 -0700, Asif_Ahmad@.dell.com wrote:

>Hello everyone,
>I was wondering if anyone could help me out. Basically I have several
>sharepoint sites for applications that I run. Each applications
>sharepoint site needs to display information about the application
>that is located in a SQL database. So for example:
>www.qwerty.com/application1/home.html
>www.qwerty.com/application2/home.html
>www.qwerty.com/application3/home.html
>I need to find a way that SQL, Sharepoint, some code, would take the
>web address, parse, and truncate it to only read "Application1."
>Then I could write a SQL statement to get the data from Application1's
>database to display on the sharepoint site. However I have several
>application pages, and I would like to be able to have some type of
>code that would automatically do it for all the applications, instead
>of manually coding/quiering for the data for each application
>page.....it would just take too long.
>if anyone has any ideas, feel free to share. Thank you in advance, for
>all your time and help it is greatly appreciated!
>--A4orce84|||Ron,
Is this if you only know the number of applications you have? I think
there are several hundred I am using particularly. It also appears
from your code that you have to enter the physical address into the
code by your:
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
I wanted a way that it would do it automatically, that way if servers
change and things get moved around, or they add more applications it
would be easy to keep maintaining the SQL. Or I may have completely
mis-interpreted your code, either way thank you for your help. Any
additional information you could provide would be helpful!|||I assumed that the URLs you needed to parse would already be in a SQL
Server database table. (Why else would you be trying to parse a URL
in SQL Server?) So my CREATE TABLE and INSERT statements were simply
there to create some test data for demonstration purposes. The number
of URLs in your table is irrelevant to the parsing process.
What I intended to demonstrate was a way to pick out the next to last
section of the URL, which was what I thought you were aiming for. I
believe the code provided does that.
Good luck!
Roy Harvey
Beacon Falls, CT
On Sun, 10 Jun 2007 15:04:14 -0700, Asif_Ahmad@.dell.com wrote:

>Ron,
>Is this if you only know the number of applications you have? I think
>there are several hundred I am using particularly. It also appears
>from your code that you have to enter the physical address into the
>code by your:
>
>INSERT Demo values ('www.qwerty.com/application1/home.html')
>INSERT Demo values ('www.qwerty.com/application2/home.html')
>INSERT Demo values ('www.qwerty.com/application3/home.html')
>INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
>
>I wanted a way that it would do it automatically, that way if servers
>change and things get moved around, or they add more applications it
>would be easy to keep maintaining the SQL. Or I may have completely
>mis-interpreted your code, either way thank you for your help. Any
>additional information you could provide would be helpful!

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