[posted and mailed, please reply in public]

TThai (tpthai (AT) pepco (DOT) com) writes:
The inserted/deleted tables are not the fastest in town. You may win
quite a lot by doing this in your trigger:

SELECT * INTO #inserted FROM inserted
SELECT * INTO #deleted FROM deleted

and then use the temp tables instead.

There might of course be other issues with the UPDATE statements having
bad query plans and all that. Also, if you update the table the trigger
belongs to, be sure to check that the RECURSIVE_TRIGGERS is not on for
the database.


--
Erland Sommarskog, SQL Server MVP, sommar (AT) algonet (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

I suggest you check the trigger execution plan with Query Analyzer. I
likely reason for the performance problem is that you don't have useful
indexes to support the trigger queries.

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"TThai" <tpthai (AT) pepco (DOT) com> wrote

Erland Sommarskog <sommar (AT) algonet (DOT) se> wrote

Hi Erland,
Appreciated for the suggestion reqarding the virtual tables. It did
make a big difference in processing time. However, my next problem is
when i update tableA, it created 1000 entries per each record_link in
tableB. I would like to only capture 100 records in tableB. Please
see update trigger and sql statements to execute the trigger. Thank
you very much in advance.

1. update tableA set constant_cd = 'A' where record_link between 1 and
100

2. update trigger:
CREATE trigger Tr_xxx on tableA for UPDATE as

SELECT * INTO #INSERTED FROM INSERTED
SELECT * INTO #DELETED FROM DELETED

DECLARE @NUMROWS AS INT

IF UPDATE(constant_cd)
BEGIN
Insert into tableB
(RECORD_LINK,
CONSTANT_CD,
PREV_CONSTANT_CD,
PROPERTY_CD,
PREV_PROPERTY_CD,
SEQ_NO,
PREV_SEQ_NO,
SUFFIX,
PREV_SUFFIX,
CO_NO,
PREV_CO_NO)
select ins.RECORD_LINK_NUMBER,
ins.CONSTANT_CD,
DEL.CONSTANT_CD,
ins.PROPERTY_CD,
DEL.PROPERTY_CD,
ins.SEQ_NO,
DEL.SEQ_NO,
ins.SUFFIX,
DEL.SUFFIX,
ins.CO_NO,
DEL.CO_NO
from #INSERTED INS, #DELETED DEL
END
return

TThai (tpthai (AT) pepco (DOT) com) writes:
You always use SELECT TOP if you only want to catch a subset of the rows.

However, I not in your trigger code that if you have now conditions
to link #inserted and #deleted, so you get a cartesian join which is
not likely to be useful.

You should probably have something like:

FROM #inserted i JOIN #deleted d ON i.keycol = d.keycol


--
Erland Sommarskog, SQL Server MVP, sommar (AT) algonet (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

"Dan Guzman" <danguzman (AT) nospam-earthlink (DOT) net> wrote

appreciated your response, Dan. I will try it. Do you have any
suggestion where can i find out more information regarding the
execution plan. For example how to analyze to get the best result.


Thanks,
-tt