locked
Constraints and performance RRS feed

  • Question

  • Can overly adding constraint in order to validate all possible values for a column add a performance hit? Looking at constraints, we can add huge code in order to validate most columns, is this a good idea?
    Friday, September 23, 2011 4:35 PM

Answers

  • Definitely, for example FK constraint should be trusted and then it 'helps' SQL Server in terms of performance

     

    The below, does not 'touch'  Sales.SalesOrderHeader (see execution plan) to get the data

    SELECT sd.SalesOrderID, sd.CarrierTrackingNumber

    FROM Sales.SalesOrderHeader AS s

     INNER JOIN Sales.SalesOrderDetail AS sd

     ON s.SalesOrderID = sd.SalesOrderID

    WHERE sd.OrderQty > 20


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, September 25, 2011 5:57 AM
  • Every CONSTRAINT has a cost. Thus, multiple CONSTRAINTs have a bunch of costs, which may be noticeable at some point.

    CONSTRAINTs are not meant to validate the date. They are meant to enforce data rules. Put another way, data rules/logic belongs in CONSTRAINTs, business logic does not.

    The question should be asked, if some data breaks this rule, is my data inherently bad, or is it just not what the business wants?

    Monday, September 26, 2011 1:39 PM
    Answerer

All replies

  • It depends. Obviously every check constraint add some additional overhead on insert/update stage. Amount of the overhead depends on the implementation of the constraint - obviously there is the difference between simple constraint that checks if column is positive or if check constraints call CLR user defined function with very complex logic.

    And of course a lot of things depend on how often data is inserted/updated. Even most complex check constraints would not matter much if you insert just a few dozen rows per day.


    Thank you!

    My blog: http://aboutsqlserver.com

    Friday, September 23, 2011 6:27 PM
  • Definitely, for example FK constraint should be trusted and then it 'helps' SQL Server in terms of performance

     

    The below, does not 'touch'  Sales.SalesOrderHeader (see execution plan) to get the data

    SELECT sd.SalesOrderID, sd.CarrierTrackingNumber

    FROM Sales.SalesOrderHeader AS s

     INNER JOIN Sales.SalesOrderDetail AS sd

     ON s.SalesOrderID = sd.SalesOrderID

    WHERE sd.OrderQty > 20


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, September 25, 2011 5:57 AM
  • There is an overhead, but the constraints are the main line of defense, the easiest to implement and the impact should be minimal comparing to implementing logic through the triggers. So, the more we can put into domain integrity through constraints - the better.

    BTW, just today I was reading chapter 1 of the Jos Pros series Book 4 devoted to constraints - they are explained there in very simple terms.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, September 26, 2011 1:20 AM
    Answerer
  • Every CONSTRAINT has a cost. Thus, multiple CONSTRAINTs have a bunch of costs, which may be noticeable at some point.

    CONSTRAINTs are not meant to validate the date. They are meant to enforce data rules. Put another way, data rules/logic belongs in CONSTRAINTs, business logic does not.

    The question should be asked, if some data breaks this rule, is my data inherently bad, or is it just not what the business wants?

    Monday, September 26, 2011 1:39 PM
    Answerer