linked server problem slow queries

We're using SS2000 SP4 on both servers. We've been running the following
query for 6 months or so. I'm not sure exactly how long it used to take
because the whole job has about 10 queries. But the whole job used to take
about 2 hours. Now this query alone can take 3 hours.
use branch_master
update t set t.branch = z.branch_new,
t.branch_old=z.branch_old,
t.reassign_date = getdate()
from server2.promo2004.dbo.tblmail_resp t, branch_master..tblZipReassign_New z
where z.ziprequestid = 58
and t.prop_zip = z.zip
and t.branch= z.branch_old
and z.process='Y'
and z.update_type = 'A'
However, if we change the query and put it on the remote server it runs in
under t seconds. Here is the query we use on the remote server.
use promo2004 --on maximus2
update t set t.branch = z.branch_new,
t.branch_old=z.branch_old,
t.reassign_date = getdate()
from tblmail_resp t, server1.branch_master.dbo.tblZipReassign_New z
where z.ziprequestid = 58
and t.prop_zip = z.zip
and t.branch= z.branch_old
and z.process='Y'
and z.update_type = 'A'
I don't know if it makes any difference but the tblmail_resp table on
server2 has about 8 million rows and the tblzipreassign_new table on server1
has about 2500 rows.
We have the same problem with a couple of other jobs have queries that go
from server1 to server2 and they have slowed down a lot of in some cases run
for over 8 hours before we stop them.
Any ideas?
Thanks,
--
Dan D.Hi Dan,
In order for me to better answer this I suggest you post the entire DDL
of the tables including indexes and the query plan.

linked server problem slow queries

We're using SS2000 SP4 on both servers. We've been running the following
query for 6 months or so. I'm not sure exactly how long it used to take
because the whole job has about 10 queries. But the whole job used to take
about 2 hours. Now this query alone can take 3 hours.
use branch_master
update t set t.branch = z.branch_new,
t.branch_old=z.branch_old,
t.reassign_date = getdate()
from server2.promo2004.dbo.tblmail_resp t, branch_master..tblZipReassign_New
z
where z.ziprequestid = 58
and t.prop_zip = z.zip
and t.branch= z.branch_old
and z.process='Y'
and z.update_type = 'A'
However, if we change the query and put it on the remote server it runs in
under t seconds. Here is the query we use on the remote server.
use promo2004 --on maximus2
update t set t.branch = z.branch_new,
t.branch_old=z.branch_old,
t.reassign_date = getdate()
from tblmail_resp t, server1.branch_master.dbo.tblZipReassign_New z
where z.ziprequestid = 58
and t.prop_zip = z.zip
and t.branch= z.branch_old
and z.process='Y'
and z.update_type = 'A'
I don't know if it makes any difference but the tblmail_resp table on
server2 has about 8 million rows and the tblzipreassign_new table on server1
has about 2500 rows.
We have the same problem with a couple of other jobs have queries that go
from server1 to server2 and they have slowed down a lot of in some cases run
for over 8 hours before we stop them.
Any ideas?
Thanks,
--
Dan D.Hi Dan,
In order for me to better answer this I suggest you post the entire DDL
of the tables including indexes and the query plan.

linked server problem slow queries

We're using SS2000 SP4 on both servers. We've been running the following
query for 6 months or so. I'm not sure exactly how long it used to take
because the whole job has about 10 queries. But the whole job used to take
about 2 hours. Now this query alone can take 3 hours.
use branch_master
update t set t.branch = z.branch_new,
t.branch_old=z.branch_old,
t.reassign_date = getdate()
from server2.promo2004.dbo.tblmail_resp t, branch_master..tblZipReassign_New z
where z.ziprequestid = 58
and t.prop_zip = z.zip
and t.branch= z.branch_old
and z.process='Y'
and z.update_type = 'A'
However, if we change the query and put it on the remote server it runs in
under t seconds. Here is the query we use on the remote server.
use promo2004 --on maximus2
update t set t.branch = z.branch_new,
t.branch_old=z.branch_old,
t.reassign_date = getdate()
from tblmail_resp t, server1.branch_master.dbo.tblZipReassign_New z
where z.ziprequestid = 58
and t.prop_zip = z.zip
and t.branch= z.branch_old
and z.process='Y'
and z.update_type = 'A'
I don't know if it makes any difference but the tblmail_resp table on
server2 has about 8 million rows and the tblzipreassign_new table on server1
has about 2500 rows.
We have the same problem with a couple of other jobs have queries that go
from server1 to server2 and they have slowed down a lot of in some cases run
for over 8 hours before we stop them.
Any ideas?
Thanks,
Dan D.
Hi Dan,
In order for me to better answer this I suggest you post the entire DDL
of the tables including indexes and the query plan.