Does JOIN order effect efficiency?

Hi all,

Does JOIN order effect efficiency?

If I have three large tables to join together should I join the two that I know will cut the number of rows down a lot first and then join the 3rd table or does it make no difference (if I join the first and 3rd - which I know will be a large result set and then join the 2nd).

Thanks in advance,

Chiz.it should make no difference. have a look at the query plan to be sure.

Does it effect performance to have several publications instead of just 1. to the same sub

for example:
I am publishing about 5 tables to one subscriber.
Each table is approx 10gb each. I want to break
them up into 5 different publications in case
something happens to one of them and I need to
reinitialize. I figure If i had to , it would
only need to reinitialize that 1 10gb table.
Whereas if I had all 5 in one publication I would
then be reinitilizing all 5 10gb tables instead
of doing just one.
Am I causing more work for my distributor? I
figured it wouldn't hurt since it's the same log
reader for all the publications and same
distribution agent as well.
tia
-comb
Absolutely not!
In general when deploying very large snapshots like this you should
investigate
compressing your snapshots
bcping the data into the file system and then into the tables
Or using a DTS package with the fast insert option - this provides the best
performance.
Doing this on a live environment will require validation and some clean up
work to ensure everything is in sync.
You will get better performance by using multiple distribution agents if you
use the independent option. I find that using two agents works best. Most
than two causes performance degradation a the pull subscriber. Your results
may vary.
"Combfilter" <adsf@.asdf.com> wrote in message
news:MPG.1be892207facc1cc9896cf@.news.newsreader.co m...
> for example:
> I am publishing about 5 tables to one subscriber.
> Each table is approx 10gb each. I want to break
> them up into 5 different publications in case
> something happens to one of them and I need to
> reinitialize. I figure If i had to , it would
> only need to reinitialize that 1 10gb table.
> Whereas if I had all 5 in one publication I would
> then be reinitilizing all 5 10gb tables instead
> of doing just one.
> Am I causing more work for my distributor? I
> figured it wouldn't hurt since it's the same log
> reader for all the publications and same
> distribution agent as well.
> tia
> -comb
|||In article <OdRqiZ7uEHA.3972
@.TK2MSFTNGP15.phx.gbl>, hilary.cotter@.gmail.com
says...
> Absolutely not!
> In general when deploying very large snapshots like this you should
> investigate
> compressing your snapshots
> bcping the data into the file system and then into the tables
> Or using a DTS package with the fast insert option - this provides the best
> performance.
> Doing this on a live environment will require validation and some clean up
> work to ensure everything is in sync.
> You will get better performance by using multiple distribution agents if you
> use the independent option. I find that using two agents works best. Most
> than two causes performance degradation a the pull subscriber. Your results
> may vary.
>
> "Combfilter" <adsf@.asdf.com> wrote in message
> news:MPG.1be892207facc1cc9896cf@.news.newsreader.co m...
>
>
Thanks Hilary.
I see how to compress the snapshots, but then
when i read some of the older group discussions
about that , that it takes a lot of time to
decompress on the subscriber side and really
doesn't seem to be faster. I would like to learn
how to find a faster way to get these snapshots
over to the subscriber and have them sync up, but
I am not that skilled at sql. I have read some
of your and pauls notes about doing a backup of
the db and restore on the subscriber and some how
you can setup replication "with no sync" or
something like that but cannot find any how to
articles on how to do this.
thanks,
comb
|||In general a compressed snapshot will travel across the wire faster than an
uncompressed on, but then you will have to wait for the snapshot to be
extracted. I have found that the snapshot files I have worked with extract
relatively quickly and compress quickly as well.
I think you should bcp your data into the file system, compress them, send
them across the wire and then bcp them in. Do a no sync subscription and a
validation. Then you need to play catch up to get your subscriber in sync
with the publisher.
If you need more details post back here and Paul or myself will help you.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Combfilter" <adsf@.asdf.com> wrote in message
news:MPG.1be979f3afe5a3769896d0@.news.newsreader.co m...[vbcol=seagreen]
> In article <OdRqiZ7uEHA.3972
> @.TK2MSFTNGP15.phx.gbl>, hilary.cotter@.gmail.com
> says...
best[vbcol=seagreen]
up[vbcol=seagreen]
you[vbcol=seagreen]
Most[vbcol=seagreen]
results
> Thanks Hilary.
> I see how to compress the snapshots, but then
> when i read some of the older group discussions
> about that , that it takes a lot of time to
> decompress on the subscriber side and really
> doesn't seem to be faster. I would like to learn
> how to find a faster way to get these snapshots
> over to the subscriber and have them sync up, but
> I am not that skilled at sql. I have read some
> of your and pauls notes about doing a backup of
> the db and restore on the subscriber and some how
> you can setup replication "with no sync" or
> something like that but cannot find any how to
> articles on how to do this.
> thanks,
> comb
|||In article <#WIdSMJvEHA.1300
@.TK2MSFTNGP14.phx.gbl>, hilary.cotter@.gmail.com
says...
> In general a compressed snapshot will travel across the wire faster than an
> uncompressed on, but then you will have to wait for the snapshot to be
> extracted. I have found that the snapshot files I have worked with extract
> relatively quickly and compress quickly as well.
> I think you should bcp your data into the file system, compress them, send
> them across the wire and then bcp them in. Do a no sync subscription and a
> validation. Then you need to play catch up to get your subscriber in sync
> with the publisher.
> If you need more details post back here and Paul or myself will help you.
>
I tried the compression method yesterday, but my
snapshot agent went suspect. I am guessing when
you say use bcp method that just means click the
check box that says "compress snapshot" under
snapshotlocation tab in the publication
properties? I saw it create a bcp file , and
then once that file was there the next step it
started was to add it to a .cab file, so I am
assuming that was correct? Not sure why my agent
went suspect.
Where do I find the no sync option? Will this
hurt me in the long run? I've searched google
groups and google up and down for a site that
will show this step by step.
Thanks for yours and pauls help in this group.
comb

Does anyone use more than 256 worker threads ?

Hi Hassan
I suggest that you increment this value in stages until you stop getting the
error! You can then monitor the effect that the give value has on your syste
m.
John
"Hassan" wrote:

> If so, why and how much more than 256 have you set or recommend to set
> without causing too much of a performance degradation ? I am thinking 384
> for our environment due to the fact that we have a lot of transactions/sec
> and at times we run out of worker threads as we cannot connect to SQL
>
>Check out Ken Henderson's blog on max worker threads:
http://blogs.msdn.com/khen1234/arch.../07/489778.aspx
Be especially careful because if the underlying issue is that your server is
undersized for the workload, you will make matters much worse by trying to
do more. You might be better off doing some query tuning upgrading
hardware.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:u3qbSYkkGHA.1000@.TK2MSFTNGP04.phx.gbl...
> If so, why and how much more than 256 have you set or recommend to set
> without causing too much of a performance degradation ? I am thinking 384
> for our environment due to the fact that we have a lot of transactions/sec
> and at times we run out of worker threads as we cannot connect to SQL
>|||If so, why and how much more than 256 have you set or recommend to set
without causing too much of a performance degradation ? I am thinking 384
for our environment due to the fact that we have a lot of transactions/sec
and at times we run out of worker threads as we cannot connect to SQL|||Hi Hassan
I suggest that you increment this value in stages until you stop getting the
error! You can then monitor the effect that the give value has on your syste
m.
John
"Hassan" wrote:

> If so, why and how much more than 256 have you set or recommend to set
> without causing too much of a performance degradation ? I am thinking 384
> for our environment due to the fact that we have a lot of transactions/sec
> and at times we run out of worker threads as we cannot connect to SQL
>
>|||Check out Ken Henderson's blog on max worker threads:
http://blogs.msdn.com/khen1234/arch.../07/489778.aspx
Be especially careful because if the underlying issue is that your server is
undersized for the workload, you will make matters much worse by trying to
do more. You might be better off doing some query tuning upgrading
hardware.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:u3qbSYkkGHA.1000@.TK2MSFTNGP04.phx.gbl...
> If so, why and how much more than 256 have you set or recommend to set
> without causing too much of a performance degradation ? I am thinking 384
> for our environment due to the fact that we have a lot of transactions/sec
> and at times we run out of worker threads as we cannot connect to SQL
>

Does ALTER COLUMN effect the data?

I just want to change the length of 2 fields. They're CHAR and NVARCHAR type. I want to change the length from 50 to 75.

Will doing this effect the data? Will it delete the data in those columns? I just want to make sure before I do anything. The table contains about 2.5 million rows, so I don't want to mess anything up. I'm aware that the script may take a while to run and finish, but I'm only concerned about data integrity.

Here is my example script syntax to show what I'm planning to do:

Code Snippet

alter table credit alter column writeoffreason char(75) not null;

Does it matter what type of data is in the columns? For now, it's only CHAR and NVARCHAR.

I also read somewhere that you can increase length but cannot decrease without repercussions?

Thanks!

It will not delete, but SS has to allocate space in order to accomodate the CHAR one, because it has to pad with spaces to the right. This statement will not only affect metadata, it will affect actual space allocation. Check table fragmentation after it has finished.

AMB

|||thanks for the confirmation, i had thought so that the data integrity would remain intact.