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

Dear Hugo,
Thanks for your solution. With the set-based UPDATE method, really find that
it's much faster.
I’ll have another question. Lets take (my) the same example, now I need to
add another update trigger in Table2 as below (where the insert trigger in
Table1 still remain). In this trigger, what ever record(s) updated in Table2
will insert a new record into Table3.
========================================
=============
CREATE TRIGGER TG_Table2_Upd ON Table2
DECLARE @.Msg varchar(5) /*temporary variable */
DECLARE @.KeyStr char (3) /*temporary variable */
/*Create temporary table and data will be retrieved from table 2*/
Create Table #Temp (KeyStr char (3))
/* Assume the table2 contains 3 thousand records with 30 data fields*/
Insert into #Temp (KeyStr) Select KeyStr From inserted
/*loop for this temporary table*/
While Exists (Select * From #Temp) Begin
Set @.KeyStr = (Select top 1 KeyStr From #Temp)
Set @.Msg = ‘U’ + @.KeyStr
Insert into Table3 (UpdateTime, Message) values (CURRENT_TIMESTAMP, @.Msg)
Delete #Temp Where KeyStr = @.KeyStr
End
Drop table #Temp
End
========================================
=============
In this case, how can I improve the performance?
regards,
Stephanie
"Hugo Kornelis" wrote:

> On Wed, 16 Mar 2005 18:35:02 -0800, Stephanie wrote:
> (snip)
> Hi Stephanie,
> This is still a cursor. Everytime you loop through a set of rows and
> process one row as a time, you're performing a cursor operation, even if
> you are not using the prebuilt cursor tools for it.
> Warning against using cursors should be taken as warning against any
> form of iterative processing. In SQL, you should write queries that
> operate on whole sets at once.
> Another thing (even though you don't ask) - a trigger fires once per
> statement execution. All rows affected by the statement (be it one, zero
> or five million) are in the inserted pseudo-table. The code you posted
> will result in an error if there are more than one (but if you used
> SELECT instead of SET, it would not error - it would just pick one of
> the rows and process only that one). And it will produce wrong results
> if no rows are affected (as @.InsCode will be set to NULL).
>
> Remove the cursor. Replace it with a set-based UPDATE statement.
> You've posted two slighhtly different versions of your trigger, but I
> believe that your real trigger is more complicated than this (if only
> because the CREATE TRIGGER statement itself would cause an error). As an
> exampl, I'll post a set-based equivalent of the second trigger you
> posted:
> CREATE TRIGGER TG_Table1_Ins ON Table1 AFTER INSERT
> AS
> UPDATE Table2
> SET Code = (SELECT i.InsCode
> FROM inserted AS i)
> * CASE WHEN Table2.Code > 10 THEN 2 ELSE 1 END
> go
> As you can see, the set-based code is much shorter. If you test it,
> you'll find that it's much faster as well.
> In case you need help rewriting your real code in set-based form, you'll
> need to post the table structure (as CREATE TABLE statements), some
> sample data (as INSERT statements) and expected output. Posting the slow
> code you currently use might help as well. See www.aspfaq.com/5006.
>
> I don't know Oracle myself, but based on what I've heard, it appears as
> if SQL Server is heavily optimized for set-based processing (with the
> obvious tradeoff in cursor operations), whereas Oracle is either more
> optimized for cursorbased operation, or a bit for both. In any case,
> cursors do tend to be faster in Oracle.
>
> Do you mean that some rows in Table2 are changed and some are not? If
> the rows that need to be changed are determined by the data in the
> inserted pseudo-table, then this makes sense (as only one of the rows in
> inserted will be processed). But with the code you posted, this should
> not be possible - it should either process all rows in table2, or none
> at all - regardless of transactions in the calling stored procedure (a
> trigger is always part of a transaction - if not explicit, then implicit
> as part of the statement that fires the trigger). If you can post some
> code to reproduce this (I'd need at least the CREATE TABLE and INSERT
> statements to get the starting data, the actual trigger code and the
> statement that fires the trigger), I'll gladly look into it.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>On Thu, 17 Mar 2005 21:45:02 -0800, Stephanie wrote:

>Dear Hugo,
>Thanks for your solution. With the set-based UPDATE method, really find tha
t
>it's much faster.
>Ill have another question. Lets take (my) the same example, now I need to
>add another update trigger in Table2 as below (where the insert trigger in
>Table1 still remain). In this trigger, what ever record(s) updated in Table
2
>will insert a new record into Table3.
(snip)
Hi Stephanie,
The trigger code you posted will never work. You have omitted a part of
the CREATE TRIGGER statement, and you have an unmatched END.
Anyway, instead of filling a temp table, iterating iver the rows and
inserting new rows one at a time in Table3, you can easily do this in
one statement:
CREATE TRIGGER TG_Table2_Upd
ON Table2
AFTER INSERT, UPDATE -- Wild guess
AS
INSERT INTO Table3 (UpdateTime, Message)
SELECT CURRENT_TIMESTAMP, 'U' + KeyStr
FROM inserted
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you very much! :)
regards,
Stephanie Kan
"Hugo Kornelis" wrote:

> On Thu, 17 Mar 2005 21:45:02 -0800, Stephanie wrote:
>
> (snip)
> Hi Stephanie,
> The trigger code you posted will never work. You have omitted a part of
> the CREATE TRIGGER statement, and you have an unmatched END.
> Anyway, instead of filling a temp table, iterating iver the rows and
> inserting new rows one at a time in Table3, you can easily do this in
> one statement:
> CREATE TRIGGER TG_Table2_Upd
> ON Table2
> AFTER INSERT, UPDATE -- Wild guess
> AS
> INSERT INTO Table3 (UpdateTime, Message)
> SELECT CURRENT_TIMESTAMP, 'U' + KeyStr
> FROM inserted
> go
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>