boost update and delete?

  • 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?

  • 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

  • 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

    http://www.sqlservercentral.com/columnists/awarren/

  • 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.

  • 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.

  • 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?

  • 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

  • I would get an execution plan on this and make sure that all the fields are indexed properly

  • 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