| Database Triggers performance issues [message #218608] |
Thu, 08 February 2007 23:43  |
|
|
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.
Regds
Amit Nanote
|
|
|
|
| Re: Database Triggers performance issues [message #218620 is a reply to message #218608] |
Fri, 09 February 2007 00:19  |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
