Friday, February 24, 2012

delete reomte records slow

I encountered a wierd problem. I have two sql 2005 server on two compters. A db is restored on both servers. Hence they got a db of the exact same schema and data. I found if I exec a delete statement through link server it performs very poorly. But the same query is ok if being runed locally. Here is my script,

if run locally at server A

declare @.t table (col bigint)

insert into @.t (col) values(1)

insert into @.t (col) values(2)

insert into @.t (col) values(3)

delete from schemaName.targetTable where col in (select col from @.t)

This was completed within 1 second.

However, if run from server B again A,

declare @.t table (col bigint)

insert into @.t (col) values(1)

insert into @.t (col) values(2)

insert into @.t (col) values(3)

delete from serverB.db.schemaName.targetTable where col in (select col from @.t)

It won't complete even after 1 minute. I canneled the job and did not wait it to finish. There is no permission problem. The select and insert through link server is fine. Any suggestion is welcome. Thanks in advance.

Sorry, the remote delete statement should be

delete from serverA.db.schemaName.targetTable where col in (select col from @.t)

No comments:

Post a Comment