boost update and delete - 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)

boost update and delete

Author

Topic

p2bl
Yak Posting Veteran

54 Posts

Posted - 2002-12-26 : 03:42:56

I have a table with more than 20000 record to be updated and deleted,but each update's duration is almost 1s(there is a update trigger which will cause another tables delete and insert),so 20000 rows means nearly 6 hours!When we do this,there's no other connection to the database,and there's no lock and there's not much index on every related table.So why so slowly?

========================
look!

p2bl
Yak Posting Veteran

54 Posts

Posted - 2002-12-26 : 04:03:12

and when we do this update ,the SQL Server will use 100% CPU,does any IO operation cause a 100% cpu ocupation?

========================
look!

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2002-12-26 : 04:58:27

Post your SQL syntax of the trigger...

p2bl
Yak Posting Veteran

54 Posts

Posted - 2002-12-26 : 21:13:23

thanks for all ur reply.It's not me that write the code.In fact ,the trigger may looks strange,the 20000 record table's DDL

CREATE TABLE [dbo].[waitForDealTrans] (
[id] [bigint] IDENTITY (1, 1) NOT NULL ,
[matriculatedId] [varchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[dealName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[dealPrice] [money] NOT NULL ,
[dealStatus] [int] NOT NULL ,
[feeYear] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[linkId] [int] NULL ,
[feeId] [int] NOT NULL ,
[isAccount] [bit] NOT NULL ,
[isPrint] [bit] NOT NULL ,
[printDate] [datetime] NULL ,
[taskId] [int] NOT NULL ,
[lastUpdate] [datetime] NOT NULL
) ON [PRIMARY]
GO


and the update trigger

CREATE TRIGGER tri_UpdateWaitForDealtrans ON dbo.waitForDealTrans
FOR UPDATE
AS
--return
declare @id bigint
if update(dealStatus)
begin

select @id =i.id from inserted i , deleted d
where (i.id=d.id) and (i.dealStatus<>d.dealStatus)and (i.dealStatus=4)
if(@@rowcount=1)
begin
delete waitForDealTrans
WHERE id = @id
end
end

and when we update the record with set 'dealstatus'=4 it will fire the delete trigger

CREATE TRIGGER tri_DeleteWaitForDealtrans ON dbo.waitForDealTrans
FOR Delete
AS

UPDATE BankCardInfo
SET lockFee=lockFee - d.dealPrice
FROM BankCardInfo b INNER JOIN
(SELECT matriculatedId, SUM(dealPrice) AS dealPrice
FROM deleted
GROUP BY matriculatedId) d ON b.matriculatedId =d.matriculatedId


if @@rowcount = 1
begin

insert into dealTrans (matriculatedId,dealName,dealPrice,dealStatus,feeYear, linkId, feeId,isAccount,lastUpdate)
select matriculatedId,dealName,dealPrice,dealStatus,feeYear, linkId, feeId,isAccount,getDate()
from deleted where (deleted.dealStatus=4)
end

GO

in the profiler,the SQL I marked as bold cost much of the time,any sugesstion?

========================
look!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources