SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Check and Foreign Key Constraint Improves Query Performance


A Check and Foreign Key Constraint Improves Query Performance

Author
Message
Douglas Osborne-456728
Douglas Osborne-456728
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 327
I guess you didn't read what everyone has written.

Doug
peter-757102
peter-757102
SSC Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

Group: General Forum Members
Points: 855 Visits: 2559
I can agree with your point of view where it concerns your own database usage, which I cannot see as typical of relational databases in general. Rebulding all data every day surely is not the norm as many databases are simple too big to even try anything like that or need to be accessible 24/7.

The only situation where I can personally see foreign keys as not/less needed is in a staging database to feed a dimensional data warehouse. Anyway, an index is not a replacement for a foreign key, nor is the other way around.
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40909 Visits: 32666
vmssanthosh (11/15/2010)
hi
We mostly do bulk loads in our databases. nearly a lakh data will get inserted/updated in our databases everyday.

In those cases, fk relationship will degrade the insert/update operation. We handles the referential integrity at business rules. Also, we dont have any delete operation for any of our master tables. we does active/deactive for our master tables.

Our indexes are taking care of query performance and we uses index hints to do the same if required.

According to me, it depends upon the way we are maintaining the databases.

My point is it should not misguide the people tat fk's improves the database performance. Most of the transactional databases doesnt require fk relationship.

Regards
VMSSanthosh


Actually, FK's do increase performance. The optimizer will know, and understand, that you have an enforced relationship and take advantage of that information in ways that simply having an index in place will not provide. The fact that you're also talking about using hints as a casual and standard method suggests you have a number of tuning opportunities that you might not be exploring. I'd suggest digging a bit deeper in understanding how this stuff works.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
vmssanthosh
vmssanthosh
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 167
I didnt say index is a replacement for foreign key. I says that query performance depends on the indexes not on referential integrity components. even it helps in generating a good execution plan, we always have the option of changing the execution plan using the query hints.

So why we need to sacrifice the insert/update operations performance.

When we define our transactions tables with more foreign keys/constraints, it in turn reduce the insert/update operation performance.

Most of the transactions details will be retrieved using the index columns. then it will be joined with other references to get appropriate information. So retrieving data from transactions table will always be faster and it needs less/no optimization plans.

Regards
VMSSanthosh
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40909 Visits: 32666
vmssanthosh (11/15/2010)
I didnt say index is a replacement for foreign key. I says that query performance depends on the indexes not on referential integrity components. even it helps in generating a good execution plan, we always have the option of changing the execution plan using the query hints.

So why we need to sacrifice the insert/update operations performance.

When we define our transactions tables with more foreign keys/constraints, it in turn reduce the insert/update operation performance.

Most of the transactions details will be retrieved using the index columns. then it will be joined with other references to get appropriate information. So retrieving data from transactions table will always be faster and it needs less/no optimization plans.

Regards
VMSSanthosh


But, query hints aren't magic. You can't control every aspect of the optimizer. You can't tell it to ignore joins that it just doesn't need with any hint, but the optimizer can ignore joins because of referential integrity. We are talking two radically different things here, and I think you're missing it. There are performance improvements offered by referential integrity. That's not saying it's magic either, but you can't just dismiss it out of hand. You need to understand what you're losing if you get rid of it. That's not even talking about the potential for dirty data that most businesses would prefer to avoid.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
vmssanthosh
vmssanthosh
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 167
hi
I agree that if i am not using referential integrity i will be ending with dirty data. But when thedb is structured as no master record deletion and having the integrity check as business rules at the time of insertion. We can avoid use of foreign keys at those transactions tables.
Since transactions tables will always have more reference columns. Also, transaction tables will be mainly used for insert/update operations. Direct Retrieval from transactions tables will be less on large size databases/tables which involves in many insert/update operations. Also, if we retrieve the information from transactions table, the no of records will always be very less which can be handled by indexes. Good execution plan is not required in tat situation.

Yes i may be missing something. My understandings on fks are limited. Since i ever used it much in our DB. I will definitely think about using more fks/constraints.

Thanks to all for sharing your views on my opinion.

Regards
VMSSanthosh
neeraj.it
neeraj.it
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 37
Based on my experience of a database where million of records are present and being operated for inserted/updated/deleted in a year. We were having proper constraints for data integrity purpose and there was no problem at all in intial time, but later on DML operations getting slower and reached to unacceptable level sometimes failed to execute. Then we removed the constraints to get performance back in the database.

Hence for SELECT operation definitely there is a benefit else DML operations will be slow later on based on amount of data.

Thanks
iyad.tabello
iyad.tabello
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 7
Hi all,
I tried the same script but in both cases I got the same execution plan scanning the 2 clustered indexes.
So did I miss anything here? As this will be helpful for tuning my database.

Thanks,
Iyad
peter-757102
peter-757102
SSC Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

Group: General Forum Members
Points: 855 Visits: 2559
neeraj.it (4/25/2013)
Based on my experience of a database where million of records are present and being operated for inserted/updated/deleted in a year. We were having proper constraints for data integrity purpose and there was no problem at all in intial time, but later on DML operations getting slower and reached to unacceptable level sometimes failed to execute. Then we removed the constraints to get performance back in the database.

Hence for SELECT operation definitely there is a benefit else DML operations will be slow later on based on amount of data.

Thanks



Are those DML operations for individual records or large sets? And if for sets...even as large as full table updates?

And what is the size of tables being referrenced? Many tables being refferenced are lookup tables for things like record status. These are critical to keep correct or else all business logic on top turns to jelly. I would never sacrefice this sort of RI for speed gains in this area.

If there is a clear situation to advice no referential constraints in, it would be prudent to scope that advice properly. Not every FK constraint has identical importance if you are forced to choose!

Did you check if there were needles updates being done on the foreign key fields (like no changing values)? I ain't exactly certain what happens when you update a field with the same identical value. For indexes i think i know the index update doesn't get done as it is not needed. But for foreign key constraint checks....i am not certain. It could even different between SQL Server editions.

It is worth a few tests, as throwing out RI rules just to gain speed sounds like the last thing one wants to do!
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40909 Visits: 32666
neeraj.it (4/25/2013)
Based on my experience of a database where million of records are present and being operated for inserted/updated/deleted in a year. We were having proper constraints for data integrity purpose and there was no problem at all in intial time, but later on DML operations getting slower and reached to unacceptable level sometimes failed to execute. Then we removed the constraints to get performance back in the database.

Hence for SELECT operation definitely there is a benefit else DML operations will be slow later on based on amount of data.

Thanks



But now your system is operating without a safety net. You could be getting dirty data. In fact, as data increases the chances of having it dirty increase pretty radically too. Instead of just dropping constraints, you should have identified what was causing the poor performance directly (scans because you needed an index or something along those lines).

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search