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)
>

How to Encrypt or Hide parameters in URL

Please help, i have been struggling for a solution could'nt able find any info. on the net.

I am currently using SSRS 2000 updated with SP2.

Is it possible to hide or encrypt the parameters in URL.

Please help, Thank you very much for the information.

are you using any front end application to pass the parameters?

|||

Yes, i am using a front end to pass the parameters via URL

http://servername/reportserver?/ttsReports/StatusReport&eStatus=Open&UserName=myname

But want to hide the parameters information completely from the URL, using SQL2K RS

PLease Please Help, if any one has the info. That will be a gret great help for me.

Thank you all.

How to do a USE statement inside a stored proc?

Hi,
I have a general purpose stored proc that could be used in several
databases. I need to tell it what database to work on. My first solution was
to use the 'USE @.DB' statement at the beginning of the stored proc, where @.D
B
was a parameter passed in. Yet this does not work!
I remember having done this before, but don't recall exactly how!
Any suggestions?
--
Thanks in advance,
Juan Dent, M.Sc.EXEC('USE '+@.db+'; do something');
Please read http://www.sommarskog.se/dynamic_sql.html
"Juan Dent" <Juan_Dent@.nospam.nospam> wrote in message
news:B8CDCB05-C049-422E-AC38-60A97D7D6CCA@.microsoft.com...
> Hi,
> I have a general purpose stored proc that could be used in several
> databases. I need to tell it what database to work on. My first solution
> was
> to use the 'USE @.DB' statement at the beginning of the stored proc, where
> @.DB
> was a parameter passed in. Yet this does not work!
> I remember having done this before, but don't recall exactly how!
> Any suggestions?
> --
> Thanks in advance,
> Juan Dent, M.Sc.