How to enhance the performance when there is bulk update in trigger

[SM]

Dear Sir,

I encounter a problem where the execution of a trigger is very slow (in
terms of minute). Below is the example of the trigger that I have been
implemented.

Assume the trigger is fired on Table1 when inserting. The trigger
perform the following
- Create a temporary table @temp
- Retrieve all the records from Table2 and insert into this temporary
table.
- Then loop each of the temporary-Record and perform some business logic
- Update the record back into Table2

=========================================================
CREATE TRIGGER TG_Table1_Ins ON Table1

/* stored the Code value from Table1 – On Inserted*/
DECLARE @InsCode integer

/*temporary variable */
DECLARE @Code integer
DECLARE @KeyStr char (3)

Set @InsCode = (select Code from inserted)

/*Create temporary table and data will be retrieved from table 2*/
Create Table #Temp (KeyStr char (3), Code integer)

/* Assume the table2 contains 3 thousand records with 30 data fields*/
Insert into #Temp (KeyStr, Code) Select KeyStr, Code From From Table2

/*loop for this temporary table*/
While Exists (Select * From #Temp) Begin

Set @KeyStr = (Select top 1 KeyStr From #Temp)
Set @Code = (Select Code From #temp where KeyStr = @KeyStr)

/*some logic processing here, this is only example*/
If (@InsCode > 10)
Set @Code = @InsCode * 2
Else Set @Code = @InsCode

Update Table2 Set Code = @Code Where KeyStr = @KeyStr
Delete #Temp Where KeyStr = @KeyStr
End

Drop table #Temp
End

=========================================================

Question:
1. Is the temporary table method correctly been used? I understand that
Cursor is not recommended in Trigger.
2. How to speed up the performance.
3. I have tried this in oracle, it is pretty fast (in 2 ~ 5 second)
compare to MS Sql.

Thank you in advance.


Regards

SM

 

[Mary Chipman [MSFT]]

This is a perfect example of some business logic that would be better
encapsulated in a stored procedure with an explicit transaction. The
trouble with triggers is that they extend a transaction, slowing
multiple operations down. I'd create a stored procedure that uses a
BEGIN TRAN to insert into table 1, perform all of your logic, insert
into table 2, then COMMIT if no errors or ROLLBACK if any of the
operations failed. Your stored procedure can then return
success/failure information in output parameters.

Also, I suspect you don't need the temp table in the procedure,
although I didn't take the time to inspect the logic closely. For help
with writing efficient T-SQL, post in the microsoft.public.sqlserver.
programming newsgroup -- that's where the T-SQL gurus hang out.

--Mary