Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Mar 2007
    Posts
    97

    Unanswered: Do Foreign Key Boost Performance?

    Hello,

    can Foreign Keys boost performance resp. Select or Where Statement in combination with a join?

    Silas

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    nope. but indexes do and I index all of my FKs. FKs are all about maintaining data integrity and they are very important.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Do brakes boost your car's performance?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In a very few incredibly specific circumstances foreign keys can (in an MS SQL Server DB) boost performance. I never consider them in terms of a performance boost. I would advise you don't either - there are an incalcable number of things to consider first.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    gotta link?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    One caveat to my previous post. I have read that using cascading deletes in conjunction with foreign keys requires less overhead than maintaining the data integrity on your own. But as far as I know plain vanilla foreign keys on their own do not impact performance. If I am wrong, please let me know.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah - a bit like Sean I'd like to stress I only addressed a small part of the performance aspect of your question. Their purpose in maintaining integrity is far more important than any performance overhead.
    Quote Originally Posted by Thrasymachus
    gotta link?
    Yuppers:
    http://www.sqlteam.com/forums/topic....pootle,kristen
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    does youse guys ever link anyplace other than sqlteam?

    buncha wannabes

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

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by r937
    does youse guys ever link anyplace other than sqlteam?

    buncha wannabes

    whenever my boss introduces me as the resident guru I do feel like a wannabe and a fraud.

    but back to my point here, I went to the msdn link and am I right that FK's on their own without setting db options or setting cascading deletes to on do not improve performance in a perceptible way?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "setting cascading deletes to on" is a bit of misnomer

    as soon as you define the foreign key, it gets an ON DELETE action

    if you specify nothing, it defaults to NO ACTION

    SQL Server 2000 offers only NO ACTION and DELETE as options

    SQL Server 2005 offers NO ACTION, CASCADE, SET NULL, and SET DEFAULT

    it's safe to say that all of these actions are more efficiently done by the database than by application code

    but as far as SELECT queries are concerned, no, foreign keys don't speed those up (except insofar as the columns are indexed, which they usually are when someone defines foreign keys)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jul 2005
    Location
    New Zealand
    Posts
    61
    Quote Originally Posted by blindman
    Do brakes boost your car's performance?
    Yes.....in terms of stopping performance!

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Lempster
    Yes.....in terms of stopping performance!
    If you are at least marginally sane, you won't operate a car that you can't stop safely. A car without brakes is crippled, its top performance is negligable because I can walk faster than I'll try to drive it.

    Give me a 1970 Volvo that is rusted out, with one bad carburator, and good brakes and it will easily reach the speed limit... It might be smelly, ugly, and loud, but it will go as fast as the law allows.

    Give me a 2007 Lamborghini, fresh off the sales floor with bad brakes, and while the engine is fantastic and the transmission is good enough, the amount of torque that can reach the pavement is limited (by law) to what will get me to ten kph.

    Since the Volvo with brakes can travel at more than ten times the top legal speed of the Lamborghini, I feel pretty comfortable with the assertion that good brakes will significantly increase performance!

    -PatP

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    For those of you that are slow studies, the above analogy applies just as well to databases as it applies to vehicles... Foreign keys are the "safety gear" that is needed to keep a database from becoming corrupt.

    Getting wrong answers fast is the fetish of amatuer programmers and DBAs. Wrong answers are still wrong, it doesn't matter how fast you get them.

    Having foreign key constraints that you don't need will not hurt you (or query performance). Having them may help reduce elapsed time for queries because of how the engine enforces the foreign key and runs queries, but it will not hurt queries. There is some (time) cost to loading data with foreign key constraints, but that is a tiny cost compared to either a wrong answer that it caught and corrected by the business and trivial compared to the cost of a wrong answer that is actually used by the business!

    -PatP

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oops, you posted first, and a nice lesson there about FKs

    i wanted to mention that good performance without reliable brakes is a huge risk

    last year i drove my car (a '69 beetle) 20 miles to the shop using only the handbrake, as the brake cable had snapped off, and i can tell you, i drove real careful (note to the pedants: yes, i am aware i used an adjective where i should've used an adverb, but that's just my style, innit )

    this year the clutch pedal rusted half off, and i had to drive it to the same shop without being able to get it into first gear, and let me tell you, that was no picnic, you just cannot let yourself come to a complete stop because then you can't get it into second, either, and in third you'll just stall when you try to go (luckily i only had to push it while in traffic once)

    anyhow, my point to databases was that speed is irrelevant if something else is wrong

    then i was going to quote that familiar "to err is human, but to really screw things up, let your programmers put apps into production without data reliability"
    Last edited by r937; 09-03-07 at 12:24.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    THAT is why I love old vehicles... Especially Beetles
    George
    Home | Blog

Posting Permissions

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