Hello,
can Foreign Keys boost performance resp. Select or Where Statement in combination with a join?
Silasnope. but indexes do and I index all of my FKs. FKs are all about maintaining data integrity and they are very important.|||Do brakes boost your car's performance?|||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.|||gotta link?|||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.|||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.gotta link?Yuppers:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74552&SearchTerms=foreign,key,pootle,kristen|||does youse guys ever link anyplace other than sqlteam?
buncha wannabe (http://en.wiktionary.org/wiki/wannabe)s
:p|||does youse guys ever link anyplace other than sqlteam?
buncha wannabe (http://en.wiktionary.org/wiki/wannabe)s
:p
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?|||"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)|||Do brakes boost your car's performance?
Yes....in terms of stopping performance! ;)|||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|||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|||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"|||THAT is why I love old vehicles... Especially Beetles :D|||The question was: Do foreign keys boost performance?
I'm the only one that answered the question and provided evidence (from the horses mouth too - and I don't mean the wannabees).
ergo:
Poots - 1
The rest - 0
Besides - Silas gave up on us ages ago.|||Isn't that always the score? ;)|||Isn't that always the score? ;)Spoken like a true protege ;)|||...I feel pretty comfortable with the assertion that good brakes will significantly increase performance!
-PatP
When I wrote 'stopping performance' I meant the ability to be able to stop, not reducing the ability to go. There is a difference I believe!|||Guys, can we just drop this topic now and move on to evaluating the importance of regularly scheduled oil changes?
