Home » RDBMS Server » Performance Tuning » Database Triggers performance issues
Database Triggers performance issues [message #218608] Thu, 08 February 2007 23:43 Go to next message
Messages: 56
Registered: July 2005
Location: Indore

Hi ,
I would like to know the performance issues related with database triggers.
I am writing single line trigger for updating timestamp with Systimestamp and generating ids via sequences during insert.

Are trigger precomplied? If not then how do it behaves?
Do number of triggers affects the performance?
If the users count using database is in millions do trigger slow down the performance?
Do update/insert trigger trigger takes same time as it required for that update/insert statement?

Please give ur expert comments with detailed illustration or links from where I can explore it?
Thank you in anticipation.

Amit Nanote
Re: Database Triggers performance issues [message #218620 is a reply to message #218608] Fri, 09 February 2007 00:19 Go to previous message
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Obviously having a trigger will be slower than NOT having a trigger. How much slower? Why don't you benchmark it? Create a table, insert 100000 rows, create a trigger then repeat.

Triggers are compiled. Like all PL/SQL, embedded SQL will slow them down considerably. But if you just stick to PL/SQL commands then performance can be quite acceptable.

Conventional wisdom is that OLTP systems that perform transactional DML will be fine with triggers, but they are a bad idea for batch DML (eg. a Data Warehouse).

Ross Leishman
Previous Topic: query fast in one session slow in another
Next Topic: SQL with high consistent gets
Goto Forum:

Current Time: Sat Oct 22 20:51:04 CDT 2016

Total time taken to generate the page: 0.08805 seconds