February 15, 2010 at 5:55 am
Hi Folks,
My question is very simple.
'Does relationships like (PKs-FKs) help in impoving query performance?? or just they are for data integrity??'
Thanks,
Noman
February 15, 2010 at 6:15 am
FK is for dri which means only integrity of data. On the other hand, having unique constraints (for the parent table) will help the optimizer pick a faster plan because it can assume 1 and only 1 row for each id on the parent table.
Other than that you'll need indexes for the fk columns if you want to boost perf. But that will always depend on your data, how you access it and what where predicates you have.
February 15, 2010 at 6:24 am
So, If i say it like that, we dont need to add FKs if we have no problem with data integrity because our application flow will handle this.
Is a good approach.?
February 15, 2010 at 6:38 am
My personal opinion is that it is the database's responsibility to maintain data integrity. While the application can do this, it only works if the application is the only interface into your database, and that includes keeping those darned all powerful DBA's from ever running any scripts to modify your data. Many times, tables are scripted with columns that are non-null and with many of the business-rule type constraints which might make sense to have in the application instead, but fkey relationships and unique indexes, etc, etc are delegated to the apps which I think is backward.
A good rule is, if something can screw up the database, then the database should prevent it if possible. The benefit is that even those darned DBA's can't screw things up unless they do it on purpose. Let the application care if the database will accept NULL customer.phone_number as a valid data. You can still apply the rules in the business layer, and in fact get better performance by doing so (cascading deletes and updates are a case where data integrity is forced at the database but better executed at the application in many cases), but the database should ALWAYS provide for its own integrity. Otherwise it's like saying that my house is safe because the neighbor has a dog.
Tim Januario
February 15, 2010 at 6:52 am
I'm working with a world class erp (ms dynamics Nav). And it handles all the integrity stuff in the application. As far as I can tell it's bullet proof to handle that.
However some dumb (&?&?) user decided to go in the table to drop "unused" documents and the whole erp is now useless because of that bad judgement call. The client will recover... eventually but to a great cost.
If the dri had been in the db it would have been darn near impossible for the user to do that (unless she would have had access to the db and be able to drop the constraints...).
Of course there was too much power given to the user, but the dri could have prevented that problem too...
February 15, 2010 at 9:41 am
A good practice comes from a well-known phrase:
«There's only two men I trust. One of them is me. The other's not you.» 🙂
I try to put constraints on every table that needs to have one, as in my opinion it is better to take to face a job of taking off a constraint then to face a duplicate cleanup in a production environment.
The problem may appear if the database is poorly designed, but this is another topic.
February 15, 2010 at 10:08 am
Foreign keys can sometimes help the optimiser to come up with a better plan. The existence of a foreign key constraint gives the optimiser information about the data and that may allow for a more optimal plan
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2010 at 10:23 am
Noman Tariq (2/15/2010)
So, If i say it like that, we dont need to add FKs if we have no problem with data integrity because our application flow will handle this.Is a good approach.?
That's a good approach, provided that all the developers working on the application or who ever will work on the application develop perfect code and never make mistakes.
If you have non-perfect developers, you should go with DRI.
February 15, 2010 at 11:52 pm
Thanks to everyone for sharing your valuable thoughts.
It really helpful to me.
Thanks,
Noman
February 21, 2010 at 8:07 am
Noman Tariq (2/15/2010)
Hi Folks,My question is very simple.
'Does relationships like (PKs-FKs) help in impoving query performance?? or just they are for data integrity??'
Thanks,
Noman
Foreign keys it realy depends, When you delete data from the primary table then there is a huge overhed of making sure that the data is not available in the child tables this is a huge cost to the delete operations especially the oneces where you want to have a archiving stratergy in place.
Fk is a good thing for making sure of the data, If the application or the middle tire can do this then why not just leve it to the middle tire and not bother about it. make sure you provide your data through servicess, and gets consumed so you have a single possible place for failure.
Regards
Vinay
February 21, 2010 at 9:42 am
bhushanvinay (2/21/2010)
Foreign keys it realy depends, When you delete data from the primary table then there is a huge overhed of making sure that the data is not available in the child tables this is a huge cost to the delete operations especially the oneces where you want to have a archiving stratergy in place.
A single join is a huge overhead? :unsure:
bhushanvinay (2/21/2010)
Fk is a good thing for making sure of the data, If the application or the middle tire can do this then why not just leve it to the middle tire and not bother about it. make sure you provide your data through servicess, and gets consumed so you have a single possible place for failure.
Data integrity is better enforced in the database. See an earlier post in this thread for an example where mid-tier enforcement can fail.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply