locked
Two constraints vs. Indexed view - performance considerations RRS feed

Answers

  • I can't quite wrap my head around what exactly you are doing, but no, if one constraint fails, it will not keep going. 

    However, based on your requirements:

    >>There is no unique constraint in AddLink table on Address_ID, guest_no combination. My suggestion was to add this constraint, though on the other hand the UDF constraint implicitly disallows duplicates too. I did find duplicates in AddLink table, but I think they were created before this constraint was introduced.<<

    It almost seems that you could eliminate the UDF using a unique constraint and a foreign key. Plus, the unique constraint is 100% trustable, unlike the UDF solution.  If you said you wanted to allow a cardinality other than 1, then you might use a UDF constraint. 

    Defining alternate keys like this is (to me) one of the things that can really differentiate a design from another. A lot of people design tables without thinking of all of the keys (and just putting an identity column and slapping a PK on it) and that is wehn things get duplicating.  Not saying this applies to you, but with what I have seen so far... I just wonder if it could be done.

    And again, if I am reading it right, I would definitely use an indexed view before using a UDF based constraint. Yes, maintaining the index can be slightly costly, but if you limit the columns in the index to the bare minimum the maintainence there may actually improve performance over the more manual process using UDFs.

     


    Louis

    • Marked as answer by Naomi NEditor Thursday, May 6, 2010 3:39 AM
    Friday, April 30, 2010 4:43 AM

All replies

  • It depends....

    Indexed view can certainly cause a performance hit on dynamic OLTP tables.

    If unique index can do the job, use that one instead of unique key constraint.

    UK constraint needed if FK reference required or a database design tool is used.


    Kalman Toth, SQL Server & Business Intelligence Training; SQLUSA.com
    Thursday, April 29, 2010 7:35 PM
  • Check the referenced thread for the full problem's description, please. We currently have a UDF based constraint. I need to figure out if adding an extra unique index may be benefitial or removing constraint and creating indexed view as Brian showed will be a better approach.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, April 29, 2010 8:25 PM
    Answerer
  • Thanks, Kalman. Ok, chosing constraints, do we need to add one more (as unique index) or the current UDF constraint is fine and nothing else is needed?
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, April 29, 2010 11:37 PM
    Answerer
  • Thanks, Kalman. Ok, chosing constraints, do we need to add one more (as unique index) or the current UDF constraint is fine and nothing else is needed?
    If the current is working, that is fine. No need for "double insurance".
    Kalman Toth, SQL Server & Business Intelligence Training; SQLUSA.com
    Friday, April 30, 2010 12:22 AM
  • But the current is using UDF. If the first constraint fails, is the second still evaluated?
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, April 30, 2010 2:05 AM
    Answerer
  • >> IF unique index can do the job, use that one instead of unique key constraint.<<

    Why? Technically speaking, a unique index and unique constraint are implemented the same. However, the usual point of a constaint is to protect the data from some situation, and an index is used to improve performance.  (which is why a unique constraint uses an unique index... by the rules of relational database engines, it doesn't have to, it can use a hash table method, but SQL Server uses a unique index).

    This helps the dba to know what they can and cannot modify for performance.  No matter how little the unique constraint is used, it should never be removed. But any index should be fair game for tuning.


    Louis

    Friday, April 30, 2010 4:30 AM
  • I can't quite wrap my head around what exactly you are doing, but no, if one constraint fails, it will not keep going. 

    However, based on your requirements:

    >>There is no unique constraint in AddLink table on Address_ID, guest_no combination. My suggestion was to add this constraint, though on the other hand the UDF constraint implicitly disallows duplicates too. I did find duplicates in AddLink table, but I think they were created before this constraint was introduced.<<

    It almost seems that you could eliminate the UDF using a unique constraint and a foreign key. Plus, the unique constraint is 100% trustable, unlike the UDF solution.  If you said you wanted to allow a cardinality other than 1, then you might use a UDF constraint. 

    Defining alternate keys like this is (to me) one of the things that can really differentiate a design from another. A lot of people design tables without thinking of all of the keys (and just putting an identity column and slapping a PK on it) and that is wehn things get duplicating.  Not saying this applies to you, but with what I have seen so far... I just wonder if it could be done.

    And again, if I am reading it right, I would definitely use an indexed view before using a UDF based constraint. Yes, maintaining the index can be slightly costly, but if you limit the columns in the index to the bare minimum the maintainence there may actually improve performance over the more manual process using UDFs.

     


    Louis

    • Marked as answer by Naomi NEditor Thursday, May 6, 2010 3:39 AM
    Friday, April 30, 2010 4:43 AM
  • >> IF unique index can do the job, use that one instead of unique key constraint.<<

    Why? Technically speaking, a unique index and unique constraint are implemented the same.

    Excellent question Louis. Yes, on the surface they appear to be the same, but there are subtle differences which may tip the scale one way or the other.

    The general Computer Science principle is to satisfy a requirement at the lowest possible level, that is the simplest, that is the fastest usually. In this instance UK is a higher level construct than UI, therefore UI should be used. The ANSI UK builds on a limited version of non-ANSI UI.

    If you need

    A. UK for FK reference (in rare cases since usually the PK used for reference) 

    B. In a database design / modelling tool

    C. Documented in ANSI INFORMATION_SCHEMA.TABLE_CONSTRAINT view

    then there is no choice, but use UK.

    Entirely different discussion, why the all important indexes not showing up in design tools, why are the "second class" database objects? Another, can you really DISABLE a UK constraint (a regular CHECK constraint can be disabled)?

    Here is an important difference between UK and UI which is any eye-catcher for any DBA: only the FILLFACTOR index option (actually a couple more) can be used in UNIQUE KEY constraint creation. Demo follows:

    ALTER TABLE AdventureWorks2008.Sales.SalesOrderHeader 
     ADD CONSTRAINT ukSOHrowguid
     UNIQUE (rowguid)
     WITH SORT_IN_TEMPDB 
    GO
    /*
    Msg 102, Level 15, State 1, Line 4
    Incorrect syntax near 'SORT_IN_TEMPDB'.
    */
    
    CREATE UNIQUE INDEX uiSOHrowguid 
    ON AdventureWorks2008.Sales.SalesOrderHeader (rowguid)
    WITH SORT_IN_TEMPDB 
    GO
    -- Command(s) completed successfully.

     Let us know if helpful.

      


     

     

    Kalman Toth, SQL Server & Business Intelligence Training; SQLUSA.com

    • Edited by Kalman Toth Friday, April 30, 2010 12:29 PM
    Friday, April 30, 2010 8:27 AM
  • Interesting point and example, Kalman. Good stuff.

    ---

    >The general Computer Science principle is to satisfy a requirement at the lowest possible level, that is the simplest, >that is the fastest usually. In this instance UK is a higher level construct than UI, therefore UI should be used. The >ANSI UK builds on a limited version of non-ANSI UI.

    I disagree. INDEXes have nothing to do with relational Databases. They have no place in a data model. They are implementation details of RDBMSs used to implement keys and for speed. Therefore, the main difference between a UK and a UQ, is whether it is part of the model.

    So, if the data must be inherently unique, it should be a CONSTRAINT. If it is for speed, it should be an INDEX.

    Who cares? Well, one, RDBMS implementation may change. Two, it helps explain to the next modeler what is going on.

    In Naom's case, it is clearly a case where the model defines it as UNIQUE. Therefore, it should a CONSTRAINT, not an INDEX.

    Friday, April 30, 2010 12:29 PM
    Answerer
  • I'm thinking, that this table should not even have a PK (it serves no purpose), but Guest_No/Address_ID combinations should have been a PK.

    Now, presently both fields are defined as numeric(17) (as opposite to usually used integers).

    I'm hoping we're going to re-design the DB for a new version of our software and that I may be involved in the design process...


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, April 30, 2010 1:03 PM
    Answerer
  • Interesting point and example, Kalman. Good stuff.

    ....In Naom's case, it is clearly a case where the model defines it as UNIQUE. Therefore, it should a CONSTRAINT, not an INDEX.


    Thanks Brian.

    Spinoff thread: http://social.msdn.microsoft.com/Forums/en-US/databasedesign/thread/d7775749-5f9b-4449-acbe-936b8f5cfc5d

    If you really twist my arm I go with UNIQUE KEY. Even better I would leave the decision up to the DBA given that indexes are performance objects.

    But here is another subtle difference: UNIQUE KEY-s are not CHECK constraints. In fact in Object Explorer UK-s listed under Keys. A CHECK constraint can be disabled. Look what happens to UK in the following demo where it is called an "object".

    USE tempdb;
    SELECT ProductName=Name, ProductNumber, ListPrice
    INTO Product
    FROM AdventureWorks2008.Production.Product
    GO
    ALTER TABLE Product 
     ADD CONSTRAINT ukProdName
     UNIQUE (ProductName)
    GO
    ALTER TABLE Product
    WITH NOCHECK 
    CHECK CONSTRAINT ukProdName
    GO
    /*
    Msg 11415, Level 16, State 1, Line 1
    Object 'ukProdName' cannot be disabled or enabled. This action applies only to 
    foreign key and check constraints.
    */
    DROP TABLE tempdb.dbo.Product
    

    Kalman Toth, SQL Server & Business Intelligence Training; SQLUSA.com
    Friday, April 30, 2010 1:03 PM
  • Good stuff Kalman. And good examples.

    I learn things from posts like yours.

    Friday, April 30, 2010 1:22 PM
    Answerer
  • Technically, you can disable them by disabling their indexes (and if Microsoft would add a bit of syntax, it would be a heck of a lot better.)  I used a non-clustered primary key because disabling the clustered index renders the table useless..

    create table test
    (
     testId int constraint pktest primary key nonclustered
    )
    insert into test
    select 1
    union all
    select 1

    --Msg 2627, Level 14, State 1, Line 5
    --Violation of PRIMARY KEY constraint 'pktest'. Cannot insert duplicate key in object 'dbo.test'.
    --The statement has been terminated.
    go

    alter index pktest on test disable
    go

    insert into test
    select 1
    union all
    select 1
    go
    alter index pktest on test rebuild
    go


    --Msg 1505, Level 16, State 1, Line 1
    --The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.test' and the --index name 'pktest'. The duplicate key value is (1).
    --The statement has been terminated.

     

     


    Louis

    Saturday, May 1, 2010 10:46 PM
  • Does it matter if the key fields are numeric(17) for the view?
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, May 6, 2010 3:40 AM
    Answerer
  • Strictly speaking BIGINT is faster and simpler.
    Kalman Toth, SQL Server & Business Intelligence Training; SQLUSA.com
    Thursday, May 6, 2010 7:59 AM