Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    100

    Red face Unanswered: What kind of performance effect do constriants have.

    We have a database that we have designed so all the data dependances are managed by the front end code. However the company we are writing it for has asked us to add around 50 constraints. I was just a little worried what kind of effect on performance this would have. Cheers Ed

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    constraints are proactive elements of the database and really have no effect to very little on performance. this is typically because constraints check the transaction prior to the modification( before the data mod occurs) if the mod violates the constraint, the constraint will not allow the mod.

    if you duplicate all of your constraints at the app level your database constraints will never fire because you are cleaning the data before it is recieved at the RDBMS.
    However, you should not allow the application to handle all of the constraint duties. for example, primary key and foreign key constraints are an absolute must. by allowing the RDBMS to handle this process you guarantee the integrity of the data. you are more likely to have a data rule or business rule corruption at the application level rather than at the db level.

    Of all the data integrity mechanisms, constraints have the most functionality for the least amount of performance effect (next to datatypes).


    and finally, always check books online

    BOOKSONLINE{Enforcing Business Rules with Triggers}
    BOOKSONLINE{Constraints}

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •