IDS Forum

Use of new Trigger logic issue

Posted By: Norton, Graeme
Date: Thursday, 2 September 2010, at 4:48 a.m.

We are in the process of upgrading from V10 to 11.5 of IDS and as part
of upgrade I was looking at using the new Trigger logic to allow the old
and new values to be referenced in a called stored procedure. However,
on using this I had severe performance issues.

For example, the current insert trigger on a large table (109 columns)
tests some conditions and if passed inserts a copy of the record
(including selecting some additional data using direct sql and stored
procedure calls) into an audit table. Then 3 extra procedures are
called to carry out bits of work.

In test I created 200 records in the table and it took at a maximum 2
seconds to run over a few tests.

I altered the trigger to only call one stored procedure which would
reference the new and old values using the syntax:

CREATE TRIGGER trg_in_pres_slot INSERT ON pres_slot

FOR EACH ROW

( EXECUTE PROCEDURE usp_trigger_pres_slot() WITH TRIGGER REFERENCES );

I put the logic I had from the old trigger into the stored procedure and
it worked fine except, it took a lot longer. For the 200 records it
repeatedly took between 14-16 seconds to complete. I started taking
bits out to see what was the issue until all I had was an empty stored
procedure and it was still taking 6 seconds to add 200 records with no
other actions going on, so longer than the previous trigger statement
which had things going on.

Needless to say, I have abandoned this approach, which is a pity as it
keeps the logic in one place and I can just alter the procedure rather
than having to drop and recreate the trigger if I make a change.

Can anybody explain this apparent performance issue with this new
syntax?

Graeme Norton | Onshore Broadcast Technology Lead | Accenture | ITV plc
96 Kirkstall Road | Leeds | LS3 1HD | Tel: 0113 222 7157 | Mob: 07764 256655 | Graeme.Norton@ITV.COM

ITV plc Head Office Tel +44 (0) 20 7157 3000 http://www.itv.com/
Please consider the environment before printing this email

--------------------------------------------------------------------------
ITV Digital Channels Limited (Registration No. 3101815) (ITV) is incorporated in England and Wales with its registered office at The London Television Centre, Upper Ground, London SE1 9LT. Please visit the official ITV website at http://www.itv.com/ for the latest company news.

The contents of this email and any attachments are confidential, may be privileged, may be subject to copyright and are intended solely for the use of the individual to whom they are addressed. If you have received this email and you are not the intended recipient please notify mailto:postmaster@itv.com and delete this email and you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this email are strictly prohibited.

Although ITV routinely screens for viruses, recipients should scan this email and any attachments for viruses. ITV makes no representation or warranty that this email or any of its attachments is free of viruses or defects and does not accept any responsibility for any damage caused by any virus or defect transmitted by this email. ITV reserves the right to monitor all e mails and the systems upon which such e mails are stored or circulated.
This email does not conclude a binding agreement. Any views or opinions presented in this email are solely those of the author and do not necessarily represent those of ITV.
Thank You.
--------------------------------------------------------------------------

IDS Forum is maintained by Administrator with WebBBS 5.12.