Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database with 250 small and medium sized tables. These tables are related with both MyISAM and InnoDB storage engines. There are multiple triggers that have been created on different tables. These triggers are getting executed on Insert, Update and Delete events. My system has started to sometimes perform bad. On check, I found that there are a huge number of DB connection threads running.

Now my question is:

  • Do these triggers affect my system performance? If yes, Is there any better alternative for these triggers?
share|improve this question

1 Answer 1

A trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database.
There are three different strategies to ensure execution of data integrity code when a window on data application (WoD) attempts to execute a transaction:

  • Declarative : Table constraint implementation, using primary & unique keys, check constraints, foreign keys.
  • Triggered procedural: Using triggers.
  • Embedded procedural: Implementing data integrity logic in applications business layer.
    This is probably by far the most used procedural strategy for implementing DI code in WoD applications.

You can state only two types of table constraints declaratively:
uniquely identifying attributes (keys) and subset requirements referencing back to the same table, in which case a subset requirement is a table constraint (foreign key to the same table).
Implementing all other types of table constraints (like a multi-tuple constraints or transition constraints) requires you to develop procedural DI code.

So the answer is No, not without a heavy cost to implement the logic in application level.

As I know, triggers will affect performance in insert, update and delete operations. but doesn't affect read performance.
To tune your RDBMS first see what are the DB connection threads doing, and what are the most costly waits.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.