distributed partitioned views

Hi everyone,
I have some doubts about distributed partitioned views.
When we create a distributed partitioned view whcih include three server, do we have tocreate this same distributed partitioned view in that three server in order to make each server to see adn especially modify it ?

Thanks

Yes, you need to create the DPV on each server if you want to be able to modify the data on one or more of the servers from each one. Ex:

-- server1

create view dpv

as

select ... from dbo.tbl1

union all

select ... from server2.dbo.tbl2

union all

select ... from server3.dbo.tbl3

-- server2

create view dpv

as

select ... from server1.dbo.tbl1

union all

select ... from dbo.tbl2

union all

select ... from server3.dbo.tbl3

-- server3

create view dpv

as

select ... from server1.dbo.tbl1

union all

select ... from server2.dbo.tbl2

union all

select ... from dbo.tbl3

Distributed partitioned views

I'm considering distributing a large table which we
now have, for performance reasons.
We've been doing a join on this large table (11 million
rows), and I don't know whether it would help much to
partition the table across four machines.
When you perform a join on a distributed partitioned view,
where does the "work" take place. ie. If the table just
gets reconstructed at the site of the view, and then the
join operation takes place, I might actually *suffer* in
performance because of the network overhead.
Could someone advise me on this? I may be wrong about how
it works, and I'd like to understand it before undertaking
the task of distributing it.
Thanks in advance,
AndrewAndrew,
you will gain the performance enhancement as you imagined -- the join
condition will direct the work to the member table via the view. You can
verify this with the execution plan.
The key is, that your distribution will do this for all your important
joins. You may have different join conditions. If the partitioning column
is not always in the joining key, you will have to engage multiple member
tables. If this happens not two often, it might be acceptable. So here you
have to make the judgment call.
hth
Quentin
"Andrew" <a@.b.com> wrote in message
news:153501c38c0d$783e7b10$a101280a@.phx.gbl...
> I'm considering distributing a large table which we
> now have, for performance reasons.
> We've been doing a join on this large table (11 million
> rows), and I don't know whether it would help much to
> partition the table across four machines.
> When you perform a join on a distributed partitioned view,
> where does the "work" take place. ie. If the table just
> gets reconstructed at the site of the view, and then the
> join operation takes place, I might actually *suffer* in
> performance because of the network overhead.
> Could someone advise me on this? I may be wrong about how
> it works, and I'd like to understand it before undertaking
> the task of distributing it.
> Thanks in advance,
> Andrew
>|||Thanks Quentin,
I'm glad to hear that the work itself gets distributed.
It seems from your answer that the partitioning column
should be the column on which the join is performed for
optimal performance... did I read that right?
Also, I understand you want to keep as much "closely
related" data together as possible. Unfortunately, there
are several tables on which my large table will be joined,
and I'd like to keep them on the host where I create the
distributed view.
Is this going to be a problem? Or is it advisable to
partition the smaller tables as well?
Thanks in advance,
Andrew
>--Original Message--
>Andrew,
>you will gain the performance enhancement as you
imagined -- the join
>condition will direct the work to the member table via
the view. You can
>verify this with the execution plan.
>The key is, that your distribution will do this for all
your important
>joins. You may have different join conditions. If the
partitioning column
>is not always in the joining key, you will have to
engage multiple member
>tables. If this happens not two often, it might be
acceptable. So here you
>have to make the judgment call.
>hth
>Quentin
>
>"Andrew" <a@.b.com> wrote in message
>news:153501c38c0d$783e7b10$a101280a@.phx.gbl...
>> I'm considering distributing a large table which we
>> now have, for performance reasons.
>> We've been doing a join on this large table (11 million
>> rows), and I don't know whether it would help much to
>> partition the table across four machines.
>> When you perform a join on a distributed partitioned
view,
>> where does the "work" take place. ie. If the table
just
>> gets reconstructed at the site of the view, and then
the
>> join operation takes place, I might actually *suffer*
in
>> performance because of the network overhead.
>> Could someone advise me on this? I may be wrong about
how
>> it works, and I'd like to understand it before
undertaking
>> the task of distributing it.
>> Thanks in advance,
>> Andrew
>
>.
>|||Andrew,
> It seems from your answer that the partitioning column
> should be the column on which the join is performed for
> optimal performance... did I read that right?
You are right. Think of your table you want to partition by lastname, where
a-m go to server1 and the rest goes to server2. If you join or search by
firstname, what will sql server do? it of course will have to go into both
server and search the data for every entry of firstname. Had you
partitioned by firstname, the query will go to server1 finding all records
with firstnames with value a-m, and finding all the others in server2.
> Also, I understand you want to keep as much "closely
> related" data together as possible. Unfortunately, there
> are several tables on which my large table will be joined,
> and I'd like to keep them on the host where I create the
> distributed view.
>
I don't see any problem. The way you want use distributed partitioned view
to improve performance is to effectively retrieve and write -- reducing
physical read and load index/data into memory with reduced paging. For
small tables, this does not present a problem and you can keep them in a
convenient db.
> >--Original Message--
> >Andrew,
> >
> >you will gain the performance enhancement as you
> imagined -- the join
> >condition will direct the work to the member table via
> the view. You can
> >verify this with the execution plan.
> >
> >The key is, that your distribution will do this for all
> your important
> >joins. You may have different join conditions. If the
> partitioning column
> >is not always in the joining key, you will have to
> engage multiple member
> >tables. If this happens not two often, it might be
> acceptable. So here you
> >have to make the judgment call.
> >
> >hth
> >
> >Quentin
> >
> >
> >"Andrew" <a@.b.com> wrote in message
> >news:153501c38c0d$783e7b10$a101280a@.phx.gbl...
> >> I'm considering distributing a large table which we
> >> now have, for performance reasons.
> >>
> >> We've been doing a join on this large table (11 million
> >> rows), and I don't know whether it would help much to
> >> partition the table across four machines.
> >>
> >> When you perform a join on a distributed partitioned
> view,
> >> where does the "work" take place. ie. If the table
> just
> >> gets reconstructed at the site of the view, and then
> the
> >> join operation takes place, I might actually *suffer*
> in
> >> performance because of the network overhead.
> >>
> >> Could someone advise me on this? I may be wrong about
> how
> >> it works, and I'd like to understand it before
> undertaking
> >> the task of distributing it.
> >>
> >> Thanks in advance,
> >>
> >> Andrew
> >>
> >
> >
> >.
> >|||Quentin,
Thanks, you've been a great help. I'll get to designing
this thing.
All the best,
Andrew
>--Original Message--
>Andrew,
>> It seems from your answer that the partitioning column
>> should be the column on which the join is performed for
>> optimal performance... did I read that right?
>You are right. Think of your table you want to
partition by lastname, where
>a-m go to server1 and the rest goes to server2. If you
join or search by
>firstname, what will sql server do? it of course will
have to go into both
>server and search the data for every entry of
firstname. Had you
>partitioned by firstname, the query will go to server1
finding all records
>with firstnames with value a-m, and finding all the
others in server2.
>> Also, I understand you want to keep as much "closely
>> related" data together as possible. Unfortunately,
there
>> are several tables on which my large table will be
joined,
>> and I'd like to keep them on the host where I create
the
>> distributed view.
>I don't see any problem. The way you want use
distributed partitioned view
>to improve performance is to effectively retrieve and
write -- reducing
>physical read and load index/data into memory with
reduced paging. For
>small tables, this does not present a problem and you
can keep them in a
>convenient db.
>> >--Original Message--
>> >Andrew,
>> >
>> >you will gain the performance enhancement as you
>> imagined -- the join
>> >condition will direct the work to the member table via
>> the view. You can
>> >verify this with the execution plan.
>> >
>> >The key is, that your distribution will do this for
all
>> your important
>> >joins. You may have different join conditions. If
the
>> partitioning column
>> >is not always in the joining key, you will have to
>> engage multiple member
>> >tables. If this happens not two often, it might be
>> acceptable. So here you
>> >have to make the judgment call.
>> >
>> >hth
>> >
>> >Quentin
>> >
>> >
>> >"Andrew" <a@.b.com> wrote in message
>> >news:153501c38c0d$783e7b10$a101280a@.phx.gbl...
>> >> I'm considering distributing a large table which we
>> >> now have, for performance reasons.
>> >>
>> >> We've been doing a join on this large table (11
million
>> >> rows), and I don't know whether it would help much
to
>> >> partition the table across four machines.
>> >>
>> >> When you perform a join on a distributed partitioned
>> view,
>> >> where does the "work" take place. ie. If the table
>> just
>> >> gets reconstructed at the site of the view, and then
>> the
>> >> join operation takes place, I might actually
*suffer*
>> in
>> >> performance because of the network overhead.
>> >>
>> >> Could someone advise me on this? I may be wrong
about
>> how
>> >> it works, and I'd like to understand it before
>> undertaking
>> >> the task of distributing it.
>> >>
>> >> Thanks in advance,
>> >>
>> >> Andrew
>> >>
>> >
>> >
>> >.
>> >
>
>.
>

distributed partitioned view pblm

i have the following simple tables and partitioned views ...all is fine
until i try to i/u/d using the distributed part. view...tucker2 and jag are
sqlserver enterprise edition, windows 2k server...
i get ==> union all view 'customers' in not updateable because the defintion
contains
a disallowed construct (42000,4416)
what am i missing' thx, chester
-- On Server1 - tucker2
drop TABLE dbo.Customers_tucker2
CREATE TABLE Customers_tucker2
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 1 AND 99999),
cust_name varchar(30) NOT NULL,
state_cd char(2) NOT NULL
)
insert into customers_tucker2 values (11101, 'chet1', 'tx')
insert into customers_tucker2 values (11102, 'chet2', 'xx')
insert into customers_tucker2 values (11103, 'chet3', 'tv')
insert into customers_tucker2 values (11104, 'chet4', 'vv')
insert into customers_tucker2 values (11105, 'chet5', 'zz')
-- On Server2 - jag
drop TABLE Customers_jag;
CREATE TABLE Customers_jag
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 100000 AND 999999),
cust_name varchar(30) NOT NULL,
state_cd char(2) NOT NULL
)
insert into customers_jag values (111001, 'chet41', 'tt')
insert into customers_jag values (111002, 'chet42', 'rr')
insert into customers_jag values (111003, 'chet43', 'dd')
insert into customers_jag values (111004, 'chet44', 'gg')
insert into customers_jag values (111005, 'chet45', 'ff')
/*************************************
run the following on both servers
************************************/
drop view customers
go
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
go
select top 2 * from jag.pubs.dbo.customers_jag
select top 2 * from tucker2.pubs.dbo.customers_tucker2
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
SET XACT_ABORT ON
go
create view customers
as
select customerid, cust_name, state_cd
from tucker2.pubs.dbo.customers_tucker2
union all
select customerid, cust_name, state_cd
from jag.pubs.dbo.customers_jag
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
go
select min(customerid), max(customerid) from customers
select * from customers
where customerid in (111005,11101)
/* the following 3 update/insert/del fail with msg:
union all view 'customers' in not updateable because the defintion contains
a disallowed construct (42000,4416)
*/
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
go
SET XACT_ABORT ON
update customers
set state_cd = 'la'
where customerid = 11101
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
go
insert into customers values ( 1122, 'chet', 'tx')
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
go
SET XACT_ABORT ON
delete from customers
where customerid = 11101Chet,
what for version of sql server are you using? Only the Developer and
Enterprise Editions of SQL Server 2000 allow INSERT, UPDATE, and DELETE
operations on partitioned views.
hth
Quentin
"chet gwin" <cgwin@.houston.rr.com> wrote in message
news:SyGVa.120630$XV.6674033@.twister.austin.rr.com...
> i have the following simple tables and partitioned views ...all is fine
> until i try to i/u/d using the distributed part. view...tucker2 and jag
are
> sqlserver enterprise edition, windows 2k server...
> i get ==> union all view 'customers' in not updateable because the
defintion
> contains
> a disallowed construct (42000,4416)
> what am i missing' thx, chester
>
> -- On Server1 - tucker2
> drop TABLE dbo.Customers_tucker2
> CREATE TABLE Customers_tucker2
> (CustomerID INTEGER PRIMARY KEY
> CHECK (CustomerID BETWEEN 1 AND 99999),
> cust_name varchar(30) NOT NULL,
> state_cd char(2) NOT NULL
> )
> insert into customers_tucker2 values (11101, 'chet1', 'tx')
> insert into customers_tucker2 values (11102, 'chet2', 'xx')
> insert into customers_tucker2 values (11103, 'chet3', 'tv')
> insert into customers_tucker2 values (11104, 'chet4', 'vv')
> insert into customers_tucker2 values (11105, 'chet5', 'zz')
>
> -- On Server2 - jag
> drop TABLE Customers_jag;
> CREATE TABLE Customers_jag
> (CustomerID INTEGER PRIMARY KEY
> CHECK (CustomerID BETWEEN 100000 AND 999999),
> cust_name varchar(30) NOT NULL,
> state_cd char(2) NOT NULL
> )
>
> insert into customers_jag values (111001, 'chet41', 'tt')
> insert into customers_jag values (111002, 'chet42', 'rr')
> insert into customers_jag values (111003, 'chet43', 'dd')
> insert into customers_jag values (111004, 'chet44', 'gg')
> insert into customers_jag values (111005, 'chet45', 'ff')
> /*************************************
> run the following on both servers
> ************************************/
> drop view customers
> go
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> go
> select top 2 * from jag.pubs.dbo.customers_jag
> select top 2 * from tucker2.pubs.dbo.customers_tucker2
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> SET XACT_ABORT ON
> go
> create view customers
> as
> select customerid, cust_name, state_cd
> from tucker2.pubs.dbo.customers_tucker2
> union all
> select customerid, cust_name, state_cd
> from jag.pubs.dbo.customers_jag
>
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> go
> select min(customerid), max(customerid) from customers
> select * from customers
> where customerid in (111005,11101)
> /* the following 3 update/insert/del fail with msg:
> union all view 'customers' in not updateable because the defintion
contains
> a disallowed construct (42000,4416)
> */
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> go
> SET XACT_ABORT ON
> update customers
> set state_cd = 'la'
> where customerid = 11101
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> go
> insert into customers values ( 1122, 'chet', 'tx')
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> go
> SET XACT_ABORT ON
> delete from customers
> where customerid = 11101
>
>sql

distributed partitioned view + procedure

If i have a view such as
Create view Viewall
as
select * from server1.db.dbo.abc
union all
select * from server2.db.dbo.abc
union all
select * from server3.db.dbo.abc
union all
select * from server4.db.dbo.abc
And if one server say server 2 is unavailable, will the view fail to run ?
If so , how can i still let the stored proc run
and same for a stored procedure
Create proc Viewall
as
select * from server1.db.dbo.abc
union all
select * from server2.db.dbo.abc
union all
select * from server3.db.dbo.abc
union all
select * from server4.db.dbo.abc
What happens in this case if server2 is unavailable ? And also a way to let
it run should any server be made unavailable> And if one server say server 2 is unavailable, will the view fail to run ?
For a query where the optimizer realizes it has to hit server 2 will fail.
> If so , how can i still let the stored proc run
Have redundancy on the servers.
Same goes for stored procedures.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OHe5POhkDHA.1284@.TK2MSFTNGP09.phx.gbl...
> If i have a view such as
> Create view Viewall
> as
> select * from server1.db.dbo.abc
> union all
> select * from server2.db.dbo.abc
> union all
> select * from server3.db.dbo.abc
> union all
> select * from server4.db.dbo.abc
>
> And if one server say server 2 is unavailable, will the view fail to run ?
> If so , how can i still let the stored proc run
> and same for a stored procedure
> Create proc Viewall
> as
> select * from server1.db.dbo.abc
> union all
> select * from server2.db.dbo.abc
> union all
> select * from server3.db.dbo.abc
> union all
> select * from server4.db.dbo.abc
> What happens in this case if server2 is unavailable ? And also a way to let
> it run should any server be made unavailable
>
>

Distinct Count Measures Spanning Partitions

We have a large cube partitioned now by week, whereas before it was one big
dumb partition. The problem is I don't know how to get DISTINCT COUNT
measures to behave properly. If I create a named set and do a SUM or an
AGGREGATE function of all the partitions in that set, it literally adds the
distinct counts from the partitions instead of doing a distinct count for al
l
the partitions in the set.
Is this even possible?Pl. note that, in AS 2000, Aggregate() will not work on Distinct Count
measures, whether the cube is partitioned or not. So it's not clear why
partitioning the cube would worsen problems with distinct count measures
- can you give a specific example of what happened?
http://groups-beta.google.com/group...erver.olap/msg/
5b7f7bedfe8d147e[vbcol=seagreen]
Newsgroups: microsoft.public.sqlserver.olap
From: "Sean Boon [MS]"
Date: Thu, 9 Oct 2003 13:02:57 -0700
Subject: Re: Distinct Count
Hi Chet,
Distinct count will work with MDX's WHERE clause. What doesn't work is
attempting to aggregate across sets that consist of multiple members.
In
other words you can't use the AGGREGATE() function or a calculated
member
based on the AGGREGATE() function in the where clause. It's a
limitation of
the distinct count aggregate type. There is a way around this though and
it's outlined in the following whitepaper.
http://msdn.microsoft.com/libr_ary/...ry/e_n-us/dnol.
.
Sean
Sean Boon
SQL Server BI Product Unit[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Sorry for not being more specific.
Calculated members based upon named sets that span what the cube is
partitioned by is what causes the problems.
Let's say there's a cube that is partitioned by month with this sample Fact
Data:
CustID TimeID
1 1
1 32
1 33
1 61
1 64
2 1
3 1
4 1
5 32
6 32
7 62
8 62
The TimeID's from 1-31 are Jan, 32-59 Feb, and 60+ are March, all of 1998.
I'm looking to get Distinct Count of Customers (CustID).
When querying the cube at the natural Year, Quarter, and Month levels, the
correct results are returned:
Year Distinct Count = 8
Quarter 1 Distinct Count = 8
Jan Distinct Count = 4
Feb Distinct Count = 3
Mar Distinct Count = 3
As you can see, drilling up and down with native members of dims in the cube
is just fine, regardless of partitioning.
However, we do a lot of dynamic rolling analysis. Various front ends will
generate a named set or calculated member, or both, for "the last N months."
Let's say I created a simple calculated member called "LastTwoMonths":
[Time].[1998 01] + [Time].[1998 02]
This returns 7, which is incorrect. It does the distinct count of each
partition and adds them together, which is not "correct" in the sense of tru
e
distinct counting. With the above data, the distinct count for those two
months should be 6.
I can't figure out how to get the distinct count to work properly when the
cube is partitioned by the same dimension that many calc members are based
upon.
Thanks for giving this matter the attention. I really appreciate it!
"Deepak Puri" wrote:

> Pl. note that, in AS 2000, Aggregate() will not work on Distinct Count
> measures, whether the cube is partitioned or not. So it's not clear why
> partitioning the cube would worsen problems with distinct count measures
> - can you give a specific example of what happened?
> http://groups-beta.google.com/group...erver.olap/msg/
> 5b7f7bedfe8d147e
> Newsgroups: microsoft.public.sqlserver.olap
> From: "Sean Boon [MS]"
> Date: Thu, 9 Oct 2003 13:02:57 -0700
> Subject: Re: Distinct Count
> Hi Chet,
> Distinct count will work with MDX's WHERE clause. What doesn't work is
> attempting to aggregate across sets that consist of multiple members.
> In
> other words you can't use the AGGREGATE() function or a calculated
> member
> based on the AGGREGATE() function in the where clause. It's a
> limitation of
> the distinct count aggregate type. There is a way around this though and
> it's outlined in the following whitepaper.
> http://msdn.microsoft.com/libr-ary/...ry/e-n-us/dnol.
> ..
> Sean
> --
> Sean Boon
> SQL Server BI Product Unit
>
> - Deepak
> Deepak Puri
> Microsoft MVP - SQL Server
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||Based on your detailed description, I would re-iterate that, with AS
2000, you won't be able to roll up Distinct Count measures dynamically
in a calculated member. However, this should work in AS 2005 (Yukon), if
that's an option.
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!sql

Dist. Partitioned Views

I've read that the partitioning column must be part or
all of the primary key.
Is this design advice or a requirement? If it's a
requirement, why?
Any and all help appreciated,
Thanks,
Andrewit is a requirement, so that sql server will know to which
server the new row belongs. This is just to avoid
confilict and overlapping of the data on multiple servers.
>--Original Message--
>I've read that the partitioning column must be part or
>all of the primary key.
>Is this design advice or a requirement? If it's a
>requirement, why?
>Any and all help appreciated,
>Thanks,
>Andrew
>.
>sql