locked
Have Foreign Key Constrain or Don't Have FK? For performance effective Web2.0 application. RRS feed

  • Question

  • Hello,

    Good to have Foreign Key Constrain or not for performance for Web2.0 application?

    I've always created Foreign Key Constrain for Business application.

    However, I'm creating Web2.0 application which requires fast performance.
    I can force relationship by code.

    I had talk with ehow.com guys. They hate Foreign Key Constrain because it defects on performance.

    But, I'm still not sure about this.

    I want to ask you all experts about this issue.

    What do you think? Have FK or Don't Have FK?


    MCSD .NET, SCJP, SCJWD
    Friday, June 11, 2010 4:07 AM

Answers

  • Hi GMS,

    I must say, Foreign Keys are always good to enforce the relationship in Database but when it comes to performance matter then we have to think more on indexing the FK.

    For CASCADE options, it might be slow when one Parent table is having more nos. of child table. Then index is the only option where you can improve the performance of your query.

    Please check this article for FK Benefits:

    ·          http://msdn.microsoft.com/en-us/library/ms175464.aspx

    ·         http://www.mssqltips.com/tip.asp?tip=1296

     

    And yes, when you think for an Index on FK then you need to consider ratio of NULL rows and duplicates too in child table and size required for an Index.

    It really does depend on how often you are writing to this table and how time critical the loading of each records is and also very much depends on data-types, requirements.

    I would appreciate if any answers from others

    Thanks,

    Sandeep

     

     

     

    Friday, June 11, 2010 5:47 AM
  • In addition , FK also help for performance.Consider the following SELECT..

    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

    SQL Server does nto 'touch' SalesOrderHeader because of FK trusted constaint

    Monday, June 14, 2010 4:54 AM
  • If you want a data model, you will want FKs. If the database is like just an optimized flat file, don't use FKs.

    >I had talk with ehow.com guys. They hate Foreign Key Constrain because it defects on performance.

    That might be the case in a poorly designed model or a solution. Suggest to them they hire an expert who can fix the issues they are experiencing.


    Do they also hate indexes because they affect performance and throw those out too?  What I sometimes see from many developers is that they want a license to not think and they also prefer to have a license to cheat if the feel like they need to.  Foreign keys can get in the way of both -- especially in the way of cheating in an intended FK relationship.

    I agree with Brian: Hire an expert that knows what they are doing.  I would also bet that an expert can consistently get the database to run faster with FKs than they can get it to run without them -- and do it consistently.  But then again, that is what exactly what database experts are supposed to do.

     

    Monday, June 14, 2010 6:07 PM

All replies

  • Hi GMS,

    I must say, Foreign Keys are always good to enforce the relationship in Database but when it comes to performance matter then we have to think more on indexing the FK.

    For CASCADE options, it might be slow when one Parent table is having more nos. of child table. Then index is the only option where you can improve the performance of your query.

    Please check this article for FK Benefits:

    ·          http://msdn.microsoft.com/en-us/library/ms175464.aspx

    ·         http://www.mssqltips.com/tip.asp?tip=1296

     

    And yes, when you think for an Index on FK then you need to consider ratio of NULL rows and duplicates too in child table and size required for an Index.

    It really does depend on how often you are writing to this table and how time critical the loading of each records is and also very much depends on data-types, requirements.

    I would appreciate if any answers from others

    Thanks,

    Sandeep

     

     

     

    Friday, June 11, 2010 5:47 AM
  • In addition , FK also help for performance.Consider the following SELECT..

    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

    SQL Server does nto 'touch' SalesOrderHeader because of FK trusted constaint

    Monday, June 14, 2010 4:54 AM
  • If you want a data model, you will want FKs. If the database is like just an optimized flat file, don't use FKs.

    >I had talk with ehow.com guys. They hate Foreign Key Constrain because it defects on performance.

    That might be the case in a poorly designed model or a solution. Suggest to them they hire an expert who can fix the issues they are experiencing.

    Monday, June 14, 2010 11:20 AM
    Answerer
  • If you want a data model, you will want FKs. If the database is like just an optimized flat file, don't use FKs.

    >I had talk with ehow.com guys. They hate Foreign Key Constrain because it defects on performance.

    That might be the case in a poorly designed model or a solution. Suggest to them they hire an expert who can fix the issues they are experiencing.


    Do they also hate indexes because they affect performance and throw those out too?  What I sometimes see from many developers is that they want a license to not think and they also prefer to have a license to cheat if the feel like they need to.  Foreign keys can get in the way of both -- especially in the way of cheating in an intended FK relationship.

    I agree with Brian: Hire an expert that knows what they are doing.  I would also bet that an expert can consistently get the database to run faster with FKs than they can get it to run without them -- and do it consistently.  But then again, that is what exactly what database experts are supposed to do.

     

    Monday, June 14, 2010 6:07 PM