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

Hi everybody.

I have a question about the database performance.

Suppose we have 15 tables distributed to 3 databases. So if we want to to load some related data from diffrent tables of diffrent databases, we have to make more connections to the other databases, and it takes some time to establish the connection. but if we use 1 database with 15 tables, then we wont have this problem and we wont make more connections. but using 1 database, will grow the size of database file (*.mdf) and sure this couses to take more time of file operation like record-seek-time, record-insertion-time and etc.

which of these solutions have better performance? Distributed databases or single one?

Thanks

Even if you put them all in one database, you dont have to put them all in the same mdf file, or even on the same drive, and then even as the tables grow very large, you can partition them, which further helps the optimization.

The driving factor though, for me, is logical separation.

Are these tables all part of the same application? In this case I would put them in the same database. Are some of them shared between applications? In this case, I would put the shared tables in a database, and the application specific tables in another.|||Are you talking about "Databases" on the same server, or different servers?

Databases on the same server would not require multiple connections to be made for each database, you connect to the server, not the database.

|||

The main reason for having multiple databases on different machines would be if you are trying to scale out instead of scale up on a single server. Unless a single server cannot handle the workload, you probably don't need/want to scale out yet.

The size of the data file(s) in a single database does not have nearly as much impact on performance as other factors like having the proper indexes in place for your workload.

|||

Hi, thanks for the replyes.

I can seperate my data in 3 sets. first, the data which are stattic, like the list of countries, second, the memberships data of the members, like member's general information, membership information and etc. and third, the data of the members' posts.

the 1st set, is static, so never (or seldom) grows. but the 2nd and 3rd are dynamic and would grow alot.

data of 2nd and 3rd sets can be related, but also can not. I mean, as you know, the posts of the members are related to the members, but they are POSTS, so it may be logical to use a seperate database for them and a seperate database for members.

but from another view, they are related, they are the posts of the members! so it would be logical again, to put both the sets in one database.

and by this time, i'm going to use just one server, not many servers (may be in future).

and I dont know, putting all the data in one database, would reduce the performance of my database (and so my application) or not.

by the way, the data of the members, will be used by seperate applications, but the posts are for a single application. infact all of my applications will use the membership information, but the each of these, have their own application-specific data.

Distributed Broker Queue Performance Issue

Hi,

We are doing a POC for transferring a huge number of messages(millions) from oner machine to another. The two approaches we are examining are MSMQ and SQL Broker. The MSMQ is set up as a remote queue on the target machine, and the source machine takes as little as 1 millisecond to send the message (using a .NET program). However, when testing on Service Broker, we find that the time taken to send message to the queue is significantly higher - like 70 millisecond. Could you please help us in understanding why this is happening?

The service broker distributed queues have been set up as per the directions in the posting at http://www.sqlservercentral.com/columnists/sindukuri/2797.asp

The source program (written in .NET) is calling a stored procedure in the source machine to write to the SSB queue. When we run SQL Trace, we find that the SP is responsible for 99% of the time taken. Here is our SP that send the message:

Declare @.ConversationHandle uniqueidentifier
Begin Dialog @.ConversationHandle
From Service SenderService
To Service 'ReceiverService'
On Contract SampleContract
WITH Encryption=off;
SEND
ON CONVERSATION @.ConversationHandle
Message Type SenderMessageType
(<<XML String>>)

Please let us know if there are any additional settings required in the Service Broker to improve its performance. Or , what are the other approaches for building a distributed SSB application?

How big are the messages?
Are you using transactional MSMQ?

70 ms per message means ~15 msgs per second, that is quite slow. The most basic setup w/o any optimization is usualy be around 200 msgs/sec (with 1k payload) and an optimal setup should drive ~4000 msgs/sec on comodity hardware (ie. .5 milliseconds per message). Of course, I'm talking fully transacted, remote delivery operations. Have you done the minimal optimizations needed for any database operations (eg. separate data and log files on separte spindles) ?

Sending one message per dialog will give you the worst performance in SSB, both on the sending side (you need to to add the cost of the END CONVERSATION messages) as well as from the point of view of processing the messages. Have a look at the slides at http://blogs.msdn.com/remusrusanu/archive/2007/04/03/orlando-slides-and-code.aspx for a comparison of one-message-per-conversation vs. resusing conversations, as well as to see the very basic optimizations you can apply to SSB operations.

Besides raw performance, the SSB semantics (dialogs/conversartions) are quite different from MSMQ. SSB provides full duplex exactly-once-in-order reliable delivery with well defined error semantics, while MSMQ provides just raw messaging (think TCP vs. UDP if you're familiar with IP network programming).|||

The messages are close to 1 K - these are simple XML strings. We are using transactional MSMQ, and transactional SSB. These transactions are created within the .NET program, and the SQL Stored Procedure does not contain any transactions. The logic is very simple - we have a loop in C#, that reads data from anothe table (and stores in a datareader), create XML representation of that data, and calls a Stored Procedure which contains the Send Statement. I examined the links that you provided - I understand that it is possible to delay the Commit by using loop counts. However, in our case, since there are separate transactions for each message, what we can do to improve throughput?

Regards

Prasanth

|||The problem is your performance of communicating from .Net application to SQL Server. You basically cannot feed more than 15 messages per second (15 T-SQL batches), so SSB has nothing else to transmit more than 15 messages per second. Anything you can do improve this will help the message rate: cumulate several calls into a single one to reduce the number of round trips, use parametrized queries, anything that applies generic .Net T-SQL programming applies to SSB as well.
If you cannot reduce the number of transaction commits, a faster spindle for the database LDF will help. Of course, separate MDF and LDF on separate spindles.

Distinct Function

Hi All,

I have used Distinct function in my mdx query to remove duplicate
values.

I want to know what performance effect it will have on execution of
query.

With large volume of data query is taking more time to execute with
Distinct function. If we remove it it is taking less time.

Any inputs is appreciated.

Raghu

Depends on the query, but usually it has no effect on the performance. Distinct function doesn't look at cell values - it dedups tuples from the set. And if it is placed on the axis of SELECT query, then before getting cell values, the AS engine performs Distinct internally anyway. So it would be interesting to see your exact scenario to understand why you see performance difference.