March 13, 2009 at 4:01 pm
Hi,
I have what should be a simple update that I stopped after 7 minutes.
declare @cid int
set @cid = 893822
update ntmadbtest.aprimo_80_test.dbo.audience_members set company_id = @cid where audience_member_id in (select audience_member_id from audience_members where company_id = @cid)
There are 2 AM returned in the select and when I run with the explicit AM ids, it takes 11 seconds.
The list of Ams is retrieved from my local db, but the update is on a remote (linked) db using a vpn.
I am afraid this might be a runaway update, but I cannot see how it can be. Any idea why this would take so long? There are 756401 Ams total.
March 13, 2009 at 4:12 pm
Most likely the reason it is taking so long is that it has to pull all of the data in the remote table locally to perform the filtering, before the system can then update the remote table.
The best way to solve this kind of problem is to use a stored procedure on the remote system and call the stored procedure to update the table.
Another option would be to build the query dynamically and hardcode the ID's. This way SQL Server can send the parameters to the remote server.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 15, 2009 at 4:02 pm
Thanks. I did the dynamic SQL and all went well.
Thanks, again,
Bill
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply