Parameterized Expression: view

Showing posts with label view. Show all posts

Friday, March 30, 2012

Partial Success

Log Explorer from Lumigent absolutely rocks. I was able to view the three
major delete transactions, generate sql scripts for them, and run them on
the db. I've now got files back in sharepoint.
The sharepoint strucure does not look like the users are saying it should,
so I'm either executing the scripts in the wrong order or they're not
shooting me straight. But I can deal with that. ;-)
Thanks a million for all of your help.
"Jack" <anonymous@.microsoft.com> wrote in message
news:%23N8QDhV7GHA.4604@.TK2MSFTNGP03.phx.gbl...
>I goofed and did not have a maintenance plan running. Someone deleted a
>folder in Sharepoint with thousands of documents.
> Since no backup has been run, the transaction log has not been truncated.
> Is there any way to roll back all transactions since Monday, or am I
> hosed?
>
I'm happy for you, Jack. :-)
I'm also a bit surprised that the transaction still existed in the log. Either you were *very* lucky
that the log records are still around. Or, a db backup was actually taken of the database at some
point in time. In any case, I have a feeling that you will look over your backup strategy now... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jack" <anonymous@.microsoft.com> wrote in message news:OKGZU8Y7GHA.3836@.TK2MSFTNGP02.phx.gbl...
> Log Explorer from Lumigent absolutely rocks. I was able to view the three major delete
> transactions, generate sql scripts for them, and run them on the db. I've now got files back in
> sharepoint.
> The sharepoint strucure does not look like the users are saying it should, so I'm either executing
> the scripts in the wrong order or they're not shooting me straight. But I can deal with that.
> ;-)
> Thanks a million for all of your help.
>
> "Jack" <anonymous@.microsoft.com> wrote in message news:%23N8QDhV7GHA.4604@.TK2MSFTNGP03.phx.gbl...
>

Partial Success

Log Explorer from Lumigent absolutely rocks. I was able to view the three
major delete transactions, generate sql scripts for them, and run them on
the db. I've now got files back in sharepoint.
The sharepoint strucure does not look like the users are saying it should,
so I'm either executing the scripts in the wrong order or they're not
shooting me straight. But I can deal with that. ;-)
Thanks a million for all of your help.
"Jack" <anonymous@.microsoft.com> wrote in message
news:%23N8QDhV7GHA.4604@.TK2MSFTNGP03.phx.gbl...
>I goofed and did not have a maintenance plan running. Someone deleted a
>folder in Sharepoint with thousands of documents.
> Since no backup has been run, the transaction log has not been truncated.
> Is there any way to roll back all transactions since Monday, or am I
> hosed?
>I'm happy for you, Jack. :-)
I'm also a bit surprised that the transaction still existed in the log. Eith
er you were *very* lucky
that the log records are still around. Or, a db backup was actually taken of
the database at some
point in time. In any case, I have a feeling that you will look over your ba
ckup strategy now... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jack" <anonymous@.microsoft.com> wrote in message news:OKGZU8Y7GHA.3836@.TK2MSFTNGP02.phx.gbl
..
> Log Explorer from Lumigent absolutely rocks. I was able to view the three
major delete
> transactions, generate sql scripts for them, and run them on the db. I've
now got files back in
> sharepoint.
> The sharepoint strucure does not look like the users are saying it should,
so I'm either executing
> the scripts in the wrong order or they're not shooting me straight. But I
can deal with that.
> ;-)
> Thanks a million for all of your help.
>
> "Jack" <anonymous@.microsoft.com> wrote in message news:%23N8QDhV7GHA.4604@.
TK2MSFTNGP03.phx.gbl...
>

Monday, March 26, 2012

Parent-Child View without using Cursors

I would like to create a View (we'll call it FamilyView) using two tables, that I can then query against.
For example:

Parent
{
ID_PK,
Name,
PhoneNum,
Address
}

Child
{
ID_PK,
ParentID_FK,
Name
}

The view would return a dataset like this:

Parent.Name, Parent.PhoneNum, Parent.Address, Child.Name1, Child.Name2, Child.Name3... Child.NameN

William Smith, (555)555-5555, 123 Main Street, Susie, Peter, Bill Jr, Fred
Jason Jones, (666)666-6666, 54332 South Ave, Brian, Steven
Kay McPeak, (777)777-7777, 9876 Division NW, Kathy, Sally, Karen, Deb, Becky, Kendra, Ann, Edward

with an unknown number of children for each parent.

Then I would like to be able to query against this view with something like this:

SELECT * FROM FamilyView Where Child2 = 'Peter'

I have no idea how to write the SQL for this View. Is it possible?
Is this possible without using a cursor?

Thanks for any advice you all can give me.
BrianWhat Version of SQL Server?

If it's 2005, you can use CTE (Common Table Expreassions)

If it's 2000, you probably need to use a udf that returns a table|||It's SQL Server 2000.

Could you give me an example of how a UDF would be used to solve this please?|||So you want to find where the family tree for a child somewhere in the middle?|||I want to display each "family" in a single row in a result set and then be able to filter those families where the second child listed is 'Peter' (for example) and view only the families where Peter is the name of the second child. Does that make sense?

My real problem is a little more complex, but I thought that if I used this example it would eliminate a lot of explanation of the problem domain.|||Here you go, either a sproc, or a udf for set based stuff

CREATE TABLE Parent (
ID_PK int IDENTITY(1,1)
, [Name] varchar(20)
, PhoneNum varchar(20)
, Address varchar(30))

CREATE TABLE Child (
ID_PK int
, ParentID_FK int)
GO

INSERT INTO Parent([Name],PhoneNum, Address)
SELECT 'Annie', '111-111-1111', '1st Street' UNION ALL
SELECT 'Bob', '222-222-2222', '2nd Street' UNION ALL
SELECT 'Cathy', '333-333-3333', '3rd Street' UNION ALL
SELECT 'Don', '444-444-4444', '4th Street' UNION ALL
SELECT 'Emily', '555-555-5555', '5th Street' UNION ALL
SELECT 'Frank', '666-666-6666', '6th Street' UNION ALL
SELECT 'Georgette', '777-777-7777', '7th Street' UNION ALL
SELECT 'Harry', '888-888-8888', '8th Street'

INSERT INTO Child(ID_PK, ParentID_FK)
SELECT 1, null UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 2 UNION ALL
SELECT 4, 3 UNION ALL
SELECT 5, null UNION ALL
SELECT 6, 5 UNION ALL
SELECT 7, 6 UNION ALL
SELECT 8, 7
GO

SELECT * FROM Parent p LEFT JOIN Child c ON p.ID_PK = c.ID_PK
GO

CREATE FUNCTION udf_FindTree (@.Child varchar(20))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @.p int, @.p_save int, @.rs varchar(8000)
SELECT @.p = 0, @.p_save = 0
SELECT @.p = ParentID_FK FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = @.Child
--Loop Until @.@.rowcount = 0
WHILE Exists (SELECT ParentID_FK FROM Child c WHERE ID_PK = @.p)
BEGIN
SELECT @.p_save = @.p
SELECT @.p = ParentID_FK FROM Child c WHERE ID_PK = @.p_save
-- The Last assignement is the top Parent
END
--Now Walk from the top Down until @.@.rowcount = 0
SELECT @.p = @.p_save
SELECT @.rs = [Name] + ' ' + PhoneNum + ' ' + Address FROM Parent WHERE ID_PK = @.p
WHILE EXISTS (SELECT ID_PK FROM Child WHERE ParentID_FK = @.p)
BEGIN
SELECT @.rs = @.rs + ' ' + COALESCE([Name],'') FROM Parent WHERE ID_PK = @.p
SELECT @.p = ID_PK FROM Child WHERE ParentID_FK = @.p
END
RETURN @.rs
END
GO

SELECT dbo.udf_FindTree('Cathy')
GO

SELECT * FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = 'Cathy'
GO

CREATE PROC usp_FindTree @.Child varchar(20)
AS
SET NOCOUNT ON
DECLARE @.p int, @.p_save int, @.rs varchar(8000)
SELECT @.p = 0, @.p_save = 0
SELECT @.p = ParentID_FK FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = @.Child
--Loop Until @.@.rowcount = 0
WHILE Exists (SELECT ParentID_FK FROM Child c WHERE ID_PK = @.p)
BEGIN
SELECT @.p_save = @.p
SELECT @.p = ParentID_FK FROM Child c WHERE ID_PK = @.p_save
-- The Last assignement is the top Parent
END
--Now Walk from the top Down until @.@.rowcount = 0
SELECT @.p = @.p_save
SELECT @.rs = [Name] + ' ' + PhoneNum + ' ' + Address FROM Parent WHERE ID_PK = @.p
WHILE EXISTS (SELECT ID_PK FROM Child WHERE ParentID_FK = @.p)
BEGIN
SELECT @.rs = @.rs + ' ' + COALESCE([Name],'') FROM Parent WHERE ID_PK = @.p
SELECT @.p = ID_PK FROM Child WHERE ParentID_FK = @.p
END
SELECT @.rs AS rs
SET NOCOUNT OFF
GO

EXEC usp_FindTree 'Cathy'
GO

DROP PROC usp_FindTree
DROP Function udf_FindTree
DROP TABLE Parent, Child
GO|||You could even do

SELECT DISTINCT dbo.udf_FindTree([name]) FROM Parent
GO|||Your work here has actually taught me quite a bit about UDFs and I appreciate that very much, Thank you!

But what I'm looking for is something closer to what this SQL generates.

USE Northwind
GO

SELECT OrderID,
coalesce(MAX(CASE OD.rowno WHEN 1 THEN P.ProductName END), '') AS Product1,
coalesce(MAX(CASE OD.rowno WHEN 2 THEN P.ProductName END), '') AS Product2,
coalesce(MAX(CASE OD.rowno WHEN 3 THEN P.ProductName END), '') AS Product3,
coalesce(MAX(CASE OD.rowno WHEN 4 THEN P.ProductName END), '') AS Product4,
coalesce(MAX(CASE OD.rowno WHEN 5 THEN P.ProductName END), '') AS Product5,
coalesce(MAX(CASE OD.rowno WHEN 6 THEN P.ProductName END), '') AS Product6,
coalesce(MAX(CASE OD.rowno WHEN 7 THEN P.ProductName END), '') AS Product7
FROM (SELECT a.OrderID, a.ProductID,
rowno = (SELECT COUNT(*)
FROM [Order Details] b
WHERE b.OrderID = a.OrderID
AND b.ProductID <= a.ProductID)
FROM [Order Details] a) AS OD
JOIN Products P ON P.ProductID = OD.ProductID
GROUP BY OD.OrderID
ORDER BY OD.OrderID

Use Northwind database and assume [Order Details] as parent and [Products] as the child. See how all the data between the two tables are displayed in one row (but separate NAMED columns: Product1, Product2, ... etc.)? That's what I'm looking for. If I could write this code into a View (Which I can't) then I could query against the returned dataset like this.

SELECT * FROM OrderProductView WHERE Product1 = 'Chang'

and I would get all the same columns, but only including the rows with OrderIDs: {10255, 10258, 10264, etc}.

The problem with the above code is that I HAVE to know the number of "child" (Product) elements expected per order at design time. Also, the CASE construct is not valid in a View.|||So you don't care about a tree, just a key and all it's attributive rows?

Maybe something like

http://weblogs.sqlteam.com/brettk/archive/2005/02/23/4171.aspx|||Yes! This appears to be exactly what I've been looking for. Thank you, thank you, thank you.
I was beginning to think this could only be executed in code outside the SQL.
I need to play with this a little to fully understand it all, but I think this will give me the results I need.
Thank you Brett for your patience and all your help!|||Just cut and paste the code example to see how it works

Good Luck

...oh, and you can buy me a margarita and we'll call it even|||Next time I'm in the Jersey area I might do just that. I really appreciate it.
And if you're ever in Grand Rapids...|||One more question...
My query is now too big to store in a local variable... I've managed to write the generated query to a file. Is there anyway I can execute this query from a text file?

Friday, March 23, 2012

Parent Child hierarchy view

Is it possible with a datagrid to display a hierarchy view of a dataset
which is held in a single table that have an ID and Parent ID field
giving multiple levels of a hierarchy?
If it is possible how do we do it?
Regards
<<<Bryan>>Found a very useful url
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_structure_objects_v1_3cok.asp

Wednesday, March 21, 2012

Partitioning - Execution Plan

Hello group,
I'm considering creating a Partitioned view on a Sales table that
contains up to 20 Millions records. I'm doing some test and have a result
I'm questionning when looking at the Execution Plan generated.
First, let me explain a bit my structure. In my Sales table, I have
Ex-Factory sales information and Externally provided information. There
distinguised using an identifier (0=Ex Factory, 1=External).
I've created 8 Tables
SalesTSA1999 Check Constraint (Origin=1 And BilledDate
BETWEEN '19990101' And '19991231')
SalesTSA2000 Check Constraint (Origin=1 And BilledDate
BETWEEN '20000101' And '20001231')
SalesTSA2001 Check Constraint (Origin=1 And BilledDate
BETWEEN '20010101' And '20011231')
SalesTSA2002 Check Constraint (Origin=1 And BilledDate
BETWEEN '20020101' And '20021231')
SalesXFactory1999 Check Constraint (Origin=0 And BilledDate BETWEEN
'19990101' And '19991231')
SalesXFactory2000 Check Constraint (Origin=0 And BilledDate BETWEEN
'20000101' And '20001231')
SalesXFactory2001 Check Constraint (Origin=0 And BilledDate BETWEEN
'20010101' And '20011231')
SalesXFactory2002 Check Constraint (Origin=0 And BilledDate BETWEEN
'20020101' And '20021231')
and a view that include all theses tables using a UNION ALL:
viewSalesPartitioned
Then, I'm issuing the following Query:
Select Sum(Amount)
From viewSalesPartitioned
Where Origin=1 AND BilledDate BETWEEN '20020101' AND '20021231' --No worry
about Time, it's all set to 00:00:00
When looking at the Execution Plan in Query Analyzer I can see the execution
plan is "almost" getting the perfect query. I mean, on each table I can see
a "Filter Cost 0%" Except for SalesXFactory2002 where there's a table scan
with a cost of 6%. In my opinion it should do a filter like on the other
tables based on the check constraint on the column Origin. Maybe there's
something I don't understand properly (I'm just starting using the Execution
Plan analysis which is sometimes a bit confusing for me...)
Any help would be appreciated. Thanks!Christian Hamel wrote:
> Hello group,
> I'm considering creating a Partitioned view on a Sales table that
> contains up to 20 Millions records. I'm doing some test and have a
> result I'm questionning when looking at the Execution Plan generated.
> First, let me explain a bit my structure. In my Sales table, I have
> Ex-Factory sales information and Externally provided information.
> There distinguised using an identifier (0=Ex Factory, 1=External).
> I've created 8 Tables
> SalesTSA1999 Check Constraint (Origin=1 And BilledDate
> BETWEEN '19990101' And '19991231')
> SalesTSA2000 Check Constraint (Origin=1 And BilledDate
> BETWEEN '20000101' And '20001231')
> SalesTSA2001 Check Constraint (Origin=1 And BilledDate
> BETWEEN '20010101' And '20011231')
> SalesTSA2002 Check Constraint (Origin=1 And BilledDate
> BETWEEN '20020101' And '20021231')
> SalesXFactory1999 Check Constraint (Origin=0 And BilledDate
> BETWEEN '19990101' And '19991231')
> SalesXFactory2000 Check Constraint (Origin=0 And BilledDate
> BETWEEN '20000101' And '20001231')
> SalesXFactory2001 Check Constraint (Origin=0 And BilledDate
> BETWEEN '20010101' And '20011231')
> SalesXFactory2002 Check Constraint (Origin=0 And BilledDate
> BETWEEN '20020101' And '20021231')
> and a view that include all theses tables using a UNION ALL:
> viewSalesPartitioned
> Then, I'm issuing the following Query:
> Select Sum(Amount)
> From viewSalesPartitioned
> Where Origin=1 AND BilledDate BETWEEN '20020101' AND '20021231' --No
> worry about Time, it's all set to 00:00:00
> When looking at the Execution Plan in Query Analyzer I can see the
> execution plan is "almost" getting the perfect query. I mean, on
> each table I can see a "Filter Cost 0%" Except for SalesXFactory2002
> where there's a table scan with a cost of 6%. In my opinion it
> should do a filter like on the other tables based on the check
> constraint on the column Origin. Maybe there's something I don't
> understand properly (I'm just starting using the Execution Plan
> analysis which is sometimes a bit confusing for me...) Any help would
> be appreciated. Thanks!
Indexing on a character or bit value that allows only two values will
normally not add any performance benefit because the column selectivity
is too low. You might be better off using the date as the patitioning
column without the Origin. You could also try adding a hint to force SQL
Server to use the clustered index.
I would also change the check constraints to avoid any possibility of a
time portion triggering an error:
Check Constraint (Origin=1 And BilledDate >= '20000101' And BilledDate <
'20010101')
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"Christian Hamel" <chamel@.NoSpAmMeRaLlOwEd.CoM> wrote in message
news:O%23AEf7exFHA.3720@.TK2MSFTNGP11.phx.gbl...
> Hello group,
> I'm considering creating a Partitioned view on a Sales table that
> contains up to 20 Millions records. I'm doing some test and have a result
> I'm questionning when looking at the Execution Plan generated.
> First, let me explain a bit my structure. In my Sales table, I have
> Ex-Factory sales information and Externally provided information. There
> distinguised using an identifier (0=Ex Factory, 1=External).
> I've created 8 Tables
> SalesTSA1999 Check Constraint (Origin=1 And BilledDate
> BETWEEN '19990101' And '19991231')
> SalesTSA2000 Check Constraint (Origin=1 And BilledDate
> BETWEEN '20000101' And '20001231')
> SalesTSA2001 Check Constraint (Origin=1 And BilledDate
> BETWEEN '20010101' And '20011231')
> SalesTSA2002 Check Constraint (Origin=1 And BilledDate
> BETWEEN '20020101' And '20021231')
> SalesXFactory1999 Check Constraint (Origin=0 And BilledDate BETWEEN
> '19990101' And '19991231')
> SalesXFactory2000 Check Constraint (Origin=0 And BilledDate BETWEEN
> '20000101' And '20001231')
> SalesXFactory2001 Check Constraint (Origin=0 And BilledDate BETWEEN
> '20010101' And '20011231')
> SalesXFactory2002 Check Constraint (Origin=0 And BilledDate BETWEEN
> '20020101' And '20021231')
> and a view that include all theses tables using a UNION ALL:
> viewSalesPartitioned
> Then, I'm issuing the following Query:
> Select Sum(Amount)
> From viewSalesPartitioned
> Where Origin=1 AND BilledDate BETWEEN '20020101' AND '20021231' --No worry
> about Time, it's all set to 00:00:00
> When looking at the Execution Plan in Query Analyzer I can see the
> execution
> plan is "almost" getting the perfect query. I mean, on each table I can
> see
> a "Filter Cost 0%" Except for SalesXFactory2002 where there's a table scan
> with a cost of 6%. In my opinion it should do a filter like on the other
> tables based on the check constraint on the column Origin. Maybe there's
> something I don't understand properly (I'm just starting using the
> Execution
> Plan analysis which is sometimes a bit confusing for me...)
> Any help would be appreciated. Thanks!
>
Partitioned views only support a single partitioning column.
So the check constraints should be just on the date. And in the query plan
you shouldn't even see the other partitions. What you're currently seeing
is not true partition elimination. The query is just hitting each PK index
and quickly eliminating the partitions which don't contain the that column.
Partitioned views allow the data in a large table to be split into smaller
member tables. The data is partitioned between the member tables based on
ranges of data values in one of the columns. The data ranges for each member
table are defined in a CHECK constraint specified on the partitioning
column.
http://msdn2.microsoft.com/ms248875
If you add an index on Origin, or perhaps add Origin as well as BilledDate
to your primary key, you should be able to eliminate the table scan. You
will get partition elimination to aviod hitting the other time period, but
normal index s

behavior to eliminate the other Origin.
David|||My TinyInt column is not part of the index.
I thought that since there was a Check constraint on the table that allow
only a specific value, SQL Server would be kind enough to don't scan the
records based on the constraint and the value specified in my query.
Thanks for the reply.
"David Gugick" <david.gugick-nospam@.quest.com> a crit dans le message de
news: %23NC0dEfxFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Christian Hamel wrote:
> Indexing on a character or bit value that allows only two values will
> normally not add any performance benefit because the column selectivity is
> too low. You might be better off using the date as the patitioning column
> without the Origin. You could also try adding a hint to force SQL Server
> to use the clustered index.
> I would also change the check constraints to avoid any possibility of a
> time portion triggering an error:
> Check Constraint (Origin=1 And BilledDate >= '20000101' And BilledDate <
> '20010101')
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Ok, I see then. I'll check what I can do.
Thank you very much. I'll go to bed more intelligent tonight :)
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> a crit dans
le message de news: uzk3sGfxFHA.1032@.TK2MSFTNGP12.phx.gbl...
> "Christian Hamel" <chamel@.NoSpAmMeRaLlOwEd.CoM> wrote in message
> news:O%23AEf7exFHA.3720@.TK2MSFTNGP11.phx.gbl...
> Partitioned views only support a single partitioning column.
> So the check constraints should be just on the date. And in the query
> plan you shouldn't even see the other partitions. What you're currently
> seeing is not true partition elimination. The query is just hitting each
> PK index and quickly eliminating the partitions which don't contain the
> that column.
> Partitioned views allow the data in a large table to be split into smaller
> member tables. The data is partitioned between the member tables based on
> ranges of data values in one of the columns. The data ranges for each
> member table are defined in a CHECK constraint specified on the
> partitioning column.
> http://msdn2.microsoft.com/ms248875
> If you add an index on Origin, or perhaps add Origin as well as BilledDate
> to your primary key, you should be able to eliminate the table scan. You
> will get partition elimination to aviod hitting the other time period, but
> normal index s

behavior to eliminate the other Origin.
> David
>
>

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:

> 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.
>
> I expect this is due to different execution plans. Can you post DDL
> (simplified CREATE TABLE and VIEW) that shows the issue? I recall that
> there might be cases where elimination doesn't occur via joins but indexes
> can still be used to efficiently return data.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "sabin" <sabin@.discussions.microsoft.com> wrote in message
> news:D9C7FF35-961C-481E-BF88-9D8933CD0332@.microsoft.com...
>
|||the view creation ddl...
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: View dbo.Product_Fact Script Date: 7/27/2007 11:21:18 AM
******/
CREATE View [dbo].[Product_Fact] With SchemaBinding
AS
Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Produc t_Sk,Unit_Rate,Prod_Qty,Rev_Amt From [dbo].[Product_Fact_TmpTmSk88]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Produc t_Sk,Unit_Rate,Prod_Qty,Rev_Amt From [dbo].[Product_Fact_TmpTmSk87]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Produc t_Sk,Unit_Rate,Prod_Qty,Rev_Amt From [dbo].[Product_Fact_TmpTmSk86]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Produc t_Sk,Unit_Rate,Prod_Qty,Rev_Amt From [dbo].[Product_Fact_TmpTmSk85]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Produc t_Sk,Unit_Rate,Prod_Qty,Rev_Amt From [dbo].[Product_Fact_TmpTmSk84]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Produc t_Sk,Unit_Rate,Prod_Qty,Rev_Amt From [dbo].[Product_Fact_TmpTmSk83]
Union All Select
Time_Sk,Main_Btn_Sk,Entity_Sk,AssocChgCd_Sk,Produc t_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,Billab le_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Bi lled_Revenue From [dbo].[Usage_Fact_TmSk88]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billab le_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Bi lled_Revenue From [dbo].[Usage_Fact_TmSk87]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billab le_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Bi lled_Revenue From [dbo].[Usage_Fact_TmSk86]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billab le_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Bi lled_Revenue From [dbo].[Usage_Fact_TmSk85]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billab le_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Bi lled_Revenue From [dbo].[Usage_Fact_TmSk84]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billab le_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Bi lled_Revenue From [dbo].[Usage_Fact_TmSk83]
/*
Union All
Select * From [dbo].[Usage_Fact_TmSk82]
Union All
Select * From [dbo].[Usage_Fact_TmSk81]
Union All
Select * From [dbo].[Usage_Fact_TmSk80]
Union All
Select * From [dbo].[Usage_Fact_TmSk79]
Union All
Select * From [dbo].[Usage_Fact_TmSk78]
Union All
Select * From [dbo].[Usage_Fact_TmSk77]
*/
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Sr Apps Developer
"sabin" wrote:
[vbcol=seagreen]
> /****** Object: Table [dbo].[Product_Fact_TmpTmSk88] Script 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 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,Billa ble_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,B illed_Revenue)
Select Top 1000
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billab le_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Bi lled_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,Billa ble_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,B illed_Revenue)
Select Top 1000
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billab le_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Bi lled_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,Billab le_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Bi lled_Revenue From [dbo].[UsgFact_TmSk88]
Union All
Select
Time_Sk,Main_Btn_Sk,Usage_Sk,Billable_Calls,Billab le_MOU,Billable_Revenue,Billed_Calls,Billed_MOU,Bi lled_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