December 26, 2002 at 1:57 am
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?
December 26, 2002 at 8:03 am
Would it be practical to disable the trigger. Run the 20000 updates and deletes, then run a new process (might have to build) that does the trigger logic based on your 20000 updates and deletes. Then enable the trigger.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 26, 2002 at 8:13 am
One second is incredibly long for a trigger. I'd dig into why the associated activities take so long, could be you need better indexing somewhere. Short term if you need to get it done you could do the update one record at a time, that would keep your transactions short, though it might increase overall execution time.
Andy
December 26, 2002 at 1:11 pm
I hope you are not looping through 20,000 rows using a cursor or WHILE loop. If you are, change it so you do the UPDATE/DELETE in one/two statements whithout a cursor/loop. Also, if possible, move the trigger logic to SP.
If you still want the cursor/loop, starting your transaction ONE TIME in the beginning, or after every N rows (instead of every one row) will help.
December 26, 2002 at 4:41 pm
Are there a lot of foreign key relationships in the updated table(s), also those referenced in the triggers.
There has been a thread a while back that mentioned this as a possible cause of low performance.
December 26, 2002 at 7:24 pm
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
<i>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
</i>
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
<b>delete waitForDealTrans
WHERE id = @id</b>
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
<b>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
</b>
if @@rowcount = 1
begin
<b>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)</b>
end
GO
in the profiler,the SQL I marked as bold cost much of the time,any sugesstion?
January 6, 2003 at 3:50 pm
I have to agree that it seems to be taking longer than it should. Disabling the trigger sounded like a good idea. Investigating the index situation sounds good too. Another thing you may want to look into is your fragmentation of the table. With a lot of deletes and updates, you may have a terribly fragmented table.
"Keep Your Stick On the Ice" ..Red Green
January 13, 2003 at 2:54 pm
I would get an execution plan on this and make sure that all the fields are indexed properly
January 13, 2003 at 8:09 pm
My thoughts is the UPDATE is of course running the delete but the fact dbo.waitForDealTrans is the table involved it should be causing a deadlock. You effectively have a lock on the update of the row and until the update completes the lock remains. However you fire a delete at the table fore those rows that were just updated then you will be trying to remove the rows being updated and thus a paradox will occurr if not already happened.
Consider ROW exists when you update
Update fires a trigger which means the trigger code has to complete for the update to complete
The trigger fires a delete to remove the rows that are being updated and thus will be an issue.
I suggest mark the delete field and have a process that runs daily to clean them up and you shouldn't see this big of an issue.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply