Delete / Update Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Delete / Update Help

Author

Topic

jm536712
Starting Member

1 Post

Posted - 2002-06-24 : 14:23:48

I'm having to write distributed queries that perform Updates and Deletes. I use linked servers to accomplish the task. I use the simple process of
--Update ServerName.DBName.Owner.Table
--Set FieldName = 'X'
Select *
from
ServerName.DBName.Owner.Table

I'm encountering two problems. First, only 2 qualifiers are allowed in Where clauses. Second, even while testing the query in a non distrbuted fashion-that is removing the ServerName and performing on a test box, my Select * will take 4 seconds and the Update or Delete takes, well, way too long

.

I guess what I'm looking for is
1) some basic advice on writing Updates and Deletes for distrbuted
Queries
and
2) some help understanding what would cause an update or delete to take soo much longer than a select. (I've ruled out triggers in this case).

Here's an example of what I've been doing:
--UPDATE DBName.DBO.USERDATA
--SET Value = 'NEEDS WORK'
Select *
FROM
(
SELECT A.AccountID, AC.DateEntered
FROM LRCS.DBO.Account A
left Join
(
Select AC1.AccountID, Max(AC1.DateEntered) DateEntered
FROM DBName.DBO.AccountCorrespondence AC1
Where
AC1.CorrespondenceTypeID = 40
Group by AC1.AccountID
) AC
on(A.AccountID = AC.AccountID)
WHERE
A.QueueID = 5
AND
(
AC.DateEntered <= '04/15/2002'
or
AC.DateEntered is null
)
) NotWorkedRecently
JOIN
(
Select UD1.AccountID
FROM DBName.DBO.USERDATA UD1
WHERE UD1.USERDEFINEDID = 29
) UD
ON (NotWorkedRecently.AccountID = UD.AccountID)

Any and all help would be appreciated.

Edited by - jm536712 on 06/24/2002 14:25:35

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-24 : 16:00:44

quote:

I guess what I'm looking for is
1) some basic advice on writing Updates and Deletes for distrbuted
Queries
and
2) some help understanding what would cause an update or delete to take soo much longer than a select. (I've ruled out triggers in this case).

1) Don't change data across servers if you can help it. There's more overhead involved because the DTC on each server has to be enlisted to ensure transactional consistency. Many organizations choose to replicate data from one server to the other to facilitate a local update/delete. If you have to perform a distributed update/delete, I beleve you can solve the WHERE clause problem by aliasing your rowset names.

2) If you've ruled out triggers, I would next check indexes. Data modification statements necessarily cause index pages to be modified as well.

Jonathan Boott, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources