Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Posts
    57

    Unanswered: Foreign Keys improve performance?

    I have this queston that I cannot get a clear answer on. I have searched the internet to find out if using foreign keys have any performane benefits but some articles yes and some say no. So what should I believe here. Does foreign keys have any performance benefits.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    FK's enforce referential contraints...the fact that you need a PK on the children tables provides the performance
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    FKs keep some types of garbage data (such as orphans) out of your database.

    This is a good thing because you don't have to spend endless cycles later trying to clean house.

    There is some tiny perf cost to a FK though, because the server needs to check whether the corresponding PK exists when you do an insert or update, but a seek on a PK should be quite fast.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i guess you could look at it a couple of ways, and here's a different perspective

    suppose you want to add a row, but you don't have FKs defined

    however, you are concerned about integrity, so, like the many good application-centric developers of years gone by, you issue a SELECT on the parent table, using the value of the PK, to ensure that it actually exists (otherwise inserting the child would create an orphan)

    thus, the full operation consists of these oprations: SELECT, check error status, proceed with INSERT if okay, else issue error message ("parent does not exist")

    nowadays, the scenario is different

    by allowing the database to enforce integrity, the full operation becomes: INSERT, check error status, issue error if not okay

    in other words, without FKs, ensuring integrity involves two calls to the database engine, whereas with FKs, only one

    thus FKs make applications twice as efficient!!

    QED

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If you are talking about selects (rather than inserts) then I have seen a couple of articles that suggest that the engine can formulate a better plan in very specific circumstances (I remember it involved single column dates).

    Ok - I dug up the exchange:
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74552

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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