Remove cursor from trigger - multi row updates

Remove cursor from trigger - multi row updates
I need to remove to following cursor for the delete trigger (I didn't
design it) as it is causing too much of a bottle neck on the server.
What would be the best way to impletement this, bearing in mind that it
would have to take in account multi row functionality as the system
very rarely has single row deletes. I have SQL2K Enterprise on
Win2KServer.
CREATE TRIGGER [tri_DelRecs] ON MyTable
FOR DELETE
AS
SET NOCOUNT ON
DECLARE @.MasterID int
DECLARE dc2 CURSOR FOR
SELECT [Master ID] FROM Deleted
OPEN dc2
FETCH NEXT FROM dc2 INTO @.MasterID
WHILE @.@.FETCH_STATUS = 0
BEGIN
delete from [tb1] where [master id] = @.masterid
delete from [tb2] where [master id] = @.masterid
delete from [tb3] where [master id] = @.masterid
delete from [tb4] where [master id] = @.masterid
delete from [tb5] where [master id] = @.masterid
.. .. .. .. .. .. ..
.. .. .. .. .. .. ..
delete from [tbn] where [master id] = @.masterid
FETCH NEXT FROM dc2 INTO @.Masterid
END
CLOSE dc2
DEALLOCATE dc2
Would I able to accomplish this using a series of JOINs on master id?
Thanks
qhdelete from [tb1] where [master id] in (Select [Master ID] from Deleted)
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"Scott" <quackhandle1975@.yahoo.co.uk> schrieb im Newsbeitrag
news:1113569793.664500.141850@.g14g2000cwa.googlegroups.com...
> Remove cursor from trigger - multi row updates
> I need to remove to following cursor for the delete trigger (I didn't
> design it) as it is causing too much of a bottle neck on the server.
> What would be the best way to impletement this, bearing in mind that it
> would have to take in account multi row functionality as the system
> very rarely has single row deletes. I have SQL2K Enterprise on
> Win2KServer.
>
> CREATE TRIGGER [tri_DelRecs] ON MyTable
> FOR DELETE
> AS
> SET NOCOUNT ON
> DECLARE @.MasterID int
> DECLARE dc2 CURSOR FOR
> SELECT [Master ID] FROM Deleted
> OPEN dc2
> FETCH NEXT FROM dc2 INTO @.MasterID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> delete from [tb1] where [master id] = @.masterid
> delete from [tb2] where [master id] = @.masterid
> delete from [tb3] where [master id] = @.masterid
> delete from [tb4] where [master id] = @.masterid
> delete from [tb5] where [master id] = @.masterid
> .. .. .. .. .. .. ..
> .. .. .. .. .. .. ..
> delete from [tbn] where [master id] = @.masterid
>
> FETCH NEXT FROM dc2 INTO @.Masterid
> END
> CLOSE dc2
> DEALLOCATE dc2
>
> Would I able to accomplish this using a series of JOINs on master id?
>
> Thanks
> qh
>|||delete [tb1]
where exists (select * from deleted as d where d.[master id] = tb1.[master
id])
...
AMB
"Scott" wrote:

> Remove cursor from trigger - multi row updates
> I need to remove to following cursor for the delete trigger (I didn't
> design it) as it is causing too much of a bottle neck on the server.
> What would be the best way to impletement this, bearing in mind that it
> would have to take in account multi row functionality as the system
> very rarely has single row deletes. I have SQL2K Enterprise on
> Win2KServer.
>
> CREATE TRIGGER [tri_DelRecs] ON MyTable
> FOR DELETE
> AS
> SET NOCOUNT ON
> DECLARE @.MasterID int
> DECLARE dc2 CURSOR FOR
> SELECT [Master ID] FROM Deleted
> OPEN dc2
> FETCH NEXT FROM dc2 INTO @.MasterID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> delete from [tb1] where [master id] = @.masterid
> delete from [tb2] where [master id] = @.masterid
> delete from [tb3] where [master id] = @.masterid
> delete from [tb4] where [master id] = @.masterid
> delete from [tb5] where [master id] = @.masterid
> ... .. .. .. .. .. ..
> ... .. .. .. .. .. ..
> delete from [tbn] where [master id] = @.masterid
>
> FETCH NEXT FROM dc2 INTO @.Masterid
> END
> CLOSE dc2
> DEALLOCATE dc2
>
> Would I able to accomplish this using a series of JOINs on master id?
>
> Thanks
> qh
>|||Have you considered using ON DELETE CASCADE instead of a trigger?
Never use cursors in triggers, for one thing the set-based code is
usually much simpler:
CREATE TRIGGER [tri_DelRecs] ON mytable FOR DELETE
AS
DELETE FROM tb1
WHERE EXISTS
(SELECT *
FROM Deleted
WHERE [master id] = tb1.[master id])
DELETE FROM tb2
WHERE EXISTS
(SELECT *
FROM Deleted
WHERE [master id] = tb2.[master id])
... etc
David Portas
SQL Server MVP
--|||Change your DDL on the referencing tables:
CREATE TABLE subord_n
(...
master_id INTEGER NOT NULL
REFERENCES Master (master_id)
ON DELETE CASCADE,
.);
Using DRI actions also gives the optimizer information about the
relationships, so all your code gets better.|||Thanks for all your replies guys, much appreciated!
I have used been testing and I think I have what I'm after using the
following trigger, although I will try the ON DELETE CASCADE.
Cheers
qh|||Doh!
CREATE TRIGGER [tri_MyTrigger] ON [dbo].[tbl1]
FOR INSERT, UPDATE, DELETE
AS
IF @.@.ROWCOUNT = 0
BEGIN
PRINT 'There are no records to delete!'
RETURN
END
IF NOT EXISTS(SELECT * FROM DELETED)
--sql block for insert
INSERT INTO tbl2([ID], [Name], [Team])
SELECT [ID], [name], [Team] FROM Inserted
ELSE
IF NOT EXISTS(SELECT * FROM INSERTED)
--sql block for delete
DELETE FROM tbl2
WHERE tbl2.[ID] IN (SELECT [ID] FROM Deleted)
ELSE
--sql block for update
UPDATE tbl2
SET [Team] = Inserted.[Team], [Name] = Inserted.[Name]
FROM tbl2, Inserted
WHERE tbl2.[ID] = Inserted.[ID]
qh

Remote scan on linked server

I've got an interesting situation. I have two SQL 2000 servers and a proc o
n
one that updates data on the other. If I use literal values in the where
clause of the update statement then the query plan uses a "remote query" to
do the update. If I use variables then it uses a "remote scan" and filters
locally. Obviously that takes too long.
Here is some sample script.
-- This uses a remote query
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = 'ROB'
WHERE OrderNumber = 12345
-- This uses a remote scan
DECLARE @.Salesperson CHAR(3)
DECLARE @.OrderNumber INT
SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
-- This uses a remote query; same table and index structure; linked server
defined the same
UPDATE [Server3].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
I can't figure out any reason why it does it that way. To make things more
odd, that proc updates a couple other servers in the same way for the same
table and those use remote queries. The linked servers are defined the same
,
the table structure and indexes are the same.
Does anyone have any idea why it would work this way?
Thanks.
Dale.Is it possible the datatypes of those declared variables are not equal to
the corresponding data types on the linked server? In any case you should
try using OPENQUERY() as it will pass thru all queries so they are always
interpreted on the other side just as you wrote them with no odbc
intervention.
Andrew J. Kelly SQL MVP
"Dale M." <DaleM@.discussions.microsoft.com> wrote in message
news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
> I've got an interesting situation. I have two SQL 2000 servers and a proc
> on
> one that updates data on the other. If I use literal values in the where
> clause of the update statement then the query plan uses a "remote query"
> to
> do the update. If I use variables then it uses a "remote scan" and
> filters
> locally. Obviously that takes too long.
> Here is some sample script.
> -- This uses a remote query
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = 'ROB'
> WHERE OrderNumber = 12345
> -- This uses a remote scan
> DECLARE @.Salesperson CHAR(3)
> DECLARE @.OrderNumber INT
> SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> -- This uses a remote query; same table and index structure; linked server
> defined the same
> UPDATE [Server3].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> I can't figure out any reason why it does it that way. To make things
> more
> odd, that proc updates a couple other servers in the same way for the same
> table and those use remote queries. The linked servers are defined the
> same,
> the table structure and indexes are the same.
> Does anyone have any idea why it would work this way?
> Thanks.
> Dale.
>|||Thanks for the reply.
The data types are the same.
I've tried several variations of OPENQUERY syntax but it doesn't like
variables in its parameters, so I'm not sure how to get the update done.
Plus, I'd sorta like to know why anyways.
"Andrew J. Kelly" wrote:

> Is it possible the datatypes of those declared variables are not equal to
> the corresponding data types on the linked server? In any case you should
> try using OPENQUERY() as it will pass thru all queries so they are always
> interpreted on the other side just as you wrote them with no odbc
> intervention.
> --
> Andrew J. Kelly SQL MVP
> "Dale M." <DaleM@.discussions.microsoft.com> wrote in message
> news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
>
>

Remote scan on linked server

I've got an interesting situation. I have two SQL 2000 servers and a proc on
one that updates data on the other. If I use literal values in the where
clause of the update statement then the query plan uses a "remote query" to
do the update. If I use variables then it uses a "remote scan" and filters
locally. Obviously that takes too long.
Here is some sample script.
-- This uses a remote query
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = 'ROB'
WHERE OrderNumber = 12345
-- This uses a remote scan
DECLARE @.Salesperson CHAR(3)
DECLARE @.OrderNumber INT
SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
-- This uses a remote query; same table and index structure; linked server
defined the same
UPDATE [Server3].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
I can't figure out any reason why it does it that way. To make things more
odd, that proc updates a couple other servers in the same way for the same
table and those use remote queries. The linked servers are defined the same,
the table structure and indexes are the same.
Does anyone have any idea why it would work this way?
Thanks.
Dale.Is it possible the datatypes of those declared variables are not equal to
the corresponding data types on the linked server? In any case you should
try using OPENQUERY() as it will pass thru all queries so they are always
interpreted on the other side just as you wrote them with no odbc
intervention.
--
Andrew J. Kelly SQL MVP
"Dale M." <DaleM@.discussions.microsoft.com> wrote in message
news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
> I've got an interesting situation. I have two SQL 2000 servers and a proc
> on
> one that updates data on the other. If I use literal values in the where
> clause of the update statement then the query plan uses a "remote query"
> to
> do the update. If I use variables then it uses a "remote scan" and
> filters
> locally. Obviously that takes too long.
> Here is some sample script.
> -- This uses a remote query
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = 'ROB'
> WHERE OrderNumber = 12345
> -- This uses a remote scan
> DECLARE @.Salesperson CHAR(3)
> DECLARE @.OrderNumber INT
> SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> -- This uses a remote query; same table and index structure; linked server
> defined the same
> UPDATE [Server3].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> I can't figure out any reason why it does it that way. To make things
> more
> odd, that proc updates a couple other servers in the same way for the same
> table and those use remote queries. The linked servers are defined the
> same,
> the table structure and indexes are the same.
> Does anyone have any idea why it would work this way?
> Thanks.
> Dale.
>|||Thanks for the reply.
The data types are the same.
I've tried several variations of OPENQUERY syntax but it doesn't like
variables in its parameters, so I'm not sure how to get the update done.
Plus, I'd sorta like to know why anyways.
"Andrew J. Kelly" wrote:
> Is it possible the datatypes of those declared variables are not equal to
> the corresponding data types on the linked server? In any case you should
> try using OPENQUERY() as it will pass thru all queries so they are always
> interpreted on the other side just as you wrote them with no odbc
> intervention.
> --
> Andrew J. Kelly SQL MVP
> "Dale M." <DaleM@.discussions.microsoft.com> wrote in message
> news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
> > I've got an interesting situation. I have two SQL 2000 servers and a proc
> > on
> > one that updates data on the other. If I use literal values in the where
> > clause of the update statement then the query plan uses a "remote query"
> > to
> > do the update. If I use variables then it uses a "remote scan" and
> > filters
> > locally. Obviously that takes too long.
> >
> > Here is some sample script.
> > -- This uses a remote query
> > UPDATE [Server2].Orders.dbo.OrderHistory
> > SET Salesperson = 'ROB'
> > WHERE OrderNumber = 12345
> >
> > -- This uses a remote scan
> > DECLARE @.Salesperson CHAR(3)
> > DECLARE @.OrderNumber INT
> > SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
> >
> > UPDATE [Server2].Orders.dbo.OrderHistory
> > SET Salesperson = @.Salesperson
> > WHERE OrderNumber = @.OrderNumber
> >
> > -- This uses a remote query; same table and index structure; linked server
> > defined the same
> > UPDATE [Server3].Orders.dbo.OrderHistory
> > SET Salesperson = @.Salesperson
> > WHERE OrderNumber = @.OrderNumber
> >
> > I can't figure out any reason why it does it that way. To make things
> > more
> > odd, that proc updates a couple other servers in the same way for the same
> > table and those use remote queries. The linked servers are defined the
> > same,
> > the table structure and indexes are the same.
> >
> > Does anyone have any idea why it would work this way?
> >
> > Thanks.
> >
> > Dale.
> >
>
>

Remote scan on linked server

I've got an interesting situation. I have two SQL 2000 servers and a proc on
one that updates data on the other. If I use literal values in the where
clause of the update statement then the query plan uses a "remote query" to
do the update. If I use variables then it uses a "remote scan" and filters
locally. Obviously that takes too long.
Here is some sample script.
-- This uses a remote query
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = 'ROB'
WHERE OrderNumber = 12345
-- This uses a remote scan
DECLARE @.Salesperson CHAR(3)
DECLARE @.OrderNumber INT
SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
-- This uses a remote query; same table and index structure; linked server
defined the same
UPDATE [Server3].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
I can't figure out any reason why it does it that way. To make things more
odd, that proc updates a couple other servers in the same way for the same
table and those use remote queries. The linked servers are defined the same,
the table structure and indexes are the same.
Does anyone have any idea why it would work this way?
Thanks.
Dale.
Is it possible the datatypes of those declared variables are not equal to
the corresponding data types on the linked server? In any case you should
try using OPENQUERY() as it will pass thru all queries so they are always
interpreted on the other side just as you wrote them with no odbc
intervention.
Andrew J. Kelly SQL MVP
"Dale M." <DaleM@.discussions.microsoft.com> wrote in message
news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
> I've got an interesting situation. I have two SQL 2000 servers and a proc
> on
> one that updates data on the other. If I use literal values in the where
> clause of the update statement then the query plan uses a "remote query"
> to
> do the update. If I use variables then it uses a "remote scan" and
> filters
> locally. Obviously that takes too long.
> Here is some sample script.
> -- This uses a remote query
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = 'ROB'
> WHERE OrderNumber = 12345
> -- This uses a remote scan
> DECLARE @.Salesperson CHAR(3)
> DECLARE @.OrderNumber INT
> SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> -- This uses a remote query; same table and index structure; linked server
> defined the same
> UPDATE [Server3].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> I can't figure out any reason why it does it that way. To make things
> more
> odd, that proc updates a couple other servers in the same way for the same
> table and those use remote queries. The linked servers are defined the
> same,
> the table structure and indexes are the same.
> Does anyone have any idea why it would work this way?
> Thanks.
> Dale.
>
|||Thanks for the reply.
The data types are the same.
I've tried several variations of OPENQUERY syntax but it doesn't like
variables in its parameters, so I'm not sure how to get the update done.
Plus, I'd sorta like to know why anyways.
"Andrew J. Kelly" wrote:

> Is it possible the datatypes of those declared variables are not equal to
> the corresponding data types on the linked server? In any case you should
> try using OPENQUERY() as it will pass thru all queries so they are always
> interpreted on the other side just as you wrote them with no odbc
> intervention.
> --
> Andrew J. Kelly SQL MVP
> "Dale M." <DaleM@.discussions.microsoft.com> wrote in message
> news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
>
>

Remote Database updates take long time to complete

Hi
I have have two linked SQL Servers and I am trying to get remote writes
working correctly (fast).

I have configured the DB link on both machines to:
Point at each others DB.
I have security set up to map each others server logins
and Server Options: Collation Compatible, Data Access, RPC, RPC Out, Use
Remote Collation all checked

My problem is that when a SP performs

Begin Transaction

Update Local Table

Update Remote Table

Commit Tran

It takes several seconds to complete. (about 7 seconds not acceptable to
us)
This is due to the remote update - how can I improve the response time?

example of a stored procedures that takes time

where ACSMSM is a remote (linked) SQL Server.

procedure [psm].ams_Update_VFE
@.strResult varchar(8) = 'Failure' output,
@.strErrorDesc varchar(512) = 'SP Not Executed' output,
@.strVFEID varchar(16),
@.strDescription varchar(64),
@.strVFEVirtualRoot varchar(255),
@.strVFEPhysicalRoot varchar(255),
@.strAuditPath varchar(255),
@.strDefaultBranding varchar(16),
@.strIPAddress varchar(23)
as
declare @.strStep varchar(32)
declare @.trancount int

Set XACT_ABORT ON
set @.trancount = @.@.trancount
set @.strStep = 'Start of Stored Proc'

if (@.trancount = 0)
BEGIN TRANSACTION mytran
else
save tran mytran

/* start insert sp code here */

set @.strStep = 'Write VFE to MSM'

update
ACSMSM.msmprim.msm.VFECONFIG
set
DESCRIPTION = @.strDescription,
VFEVIRTUALROOT = @.strVFEVirtualRoot,
VFEPHYSICALROOT = @.strVFEPhysicalRoot,
AUDITPATH = @.strAuditPath,
DEFAULTBRANDING = @.strDefaultBranding,
IPADDRESS = @.strIPAddress
where
VFEID = @.strVFEID;

set @.strStep = 'Write VFE to PSM'

update
ACSPSM.psmprim.psm.VFECONFIG
set
DESCRIPTION = @.strDescription,
VFEVIRTUALROOT = @.strVFEVirtualRoot,
VFEPHYSICALROOT = @.strVFEPhysicalRoot,
AUDITPATH = @.strAuditPath,
DEFAULTBRANDING = @.strDefaultBranding,
IPADDRESS = @.strIPAddress
where
VFEID = @.strVFEID

/* end insert sp code here */

if (@.@.error <> 0)
begin
rollback tran mytran
set @.strResult = 'Failure'
set @.strErrorDesc = 'Fail @. Step :' + @.strStep + ' Error : ' + @.@.Error
return -1969
end
else
begin
set @.strResult = 'Success'
set @.strErrorDesc = ''
end
-- commit tran if we started it

if (@.trancount = 0)
commit tran

return 0Steve Thorpe (stephenthorpe@.nospam.hotmail.com) writes:
> It takes several seconds to complete. (about 7 seconds not acceptable to
> us)
> This is due to the remote update - how can I improve the response time?

Have you tried moving the updates of the remote table to a stored
procedures that runs on the remote server, and then call that stored
procedure remotely? I would expect that to reduce the ping-pong over
the network.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I have found the cause of the problem:

'For linked server DELETEs or UPDATEs, SQL Server retrieves data from the
table, performs any filtering that is necessary, and then performs the
deletes or updates through the OLEDB rowset. This processing can result in a
round-trip to the remote server for each row that is to be deleted or
updated. '

The solution is:

EXEC remserver.master.dbo.sp_executesql
@.stmt = N'DELETE sometable WHERE col1 = 10 OR col1 = 20'

EXEC localserver.master.dbo.sp_executesql
@.stmt = N'DELETE sometable WHERE col1 = 10 OR col1 = 20'

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94004CF4CD0FYazorman@.127.0.0.1...
> Steve Thorpe (stephenthorpe@.nospam.hotmail.com) writes:
> > It takes several seconds to complete. (about 7 seconds not acceptable
to
> > us)
> > This is due to the remote update - how can I improve the response time?
> Have you tried moving the updates of the remote table to a stored
> procedures that runs on the remote server, and then call that stored
> procedure remotely? I would expect that to reduce the ping-pong over
> the network.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp