October 25, 2010 at 11:49 am
This information was published quite a while back in the book:
Improving .NET Application Performance and Scalability
J.D. Meier, Srinath Vasireddy, Ashish Babbar, Sharon Bjeletich and Alex Mackman
Microsoft Corporation
May 2004
I mention this because the entire chapter goes into a lot of detail on improving the overall performance of .NET applications and includes information on SQL Server performance considerations that will improve the overall application performance experience.
The information can be found in Chapter 14 - Improving SQL Server Performance
found under the topic - Define All Primary Keys and Foreign Key Relationships
"
Define All Primary Keys and Foreign Key Relationships
Primary keys and foreign key relationships that are correctly defined help ensure that you can write optimal queries. One common result of incorrect relationships is having to add DISTINCT clauses to eliminate redundant data from result sets.
When primary and foreign keys are defined as constraints in the database schema, the server can use that information to create optimal execution plans.
Declarative referential integrity (DRI) performs better than triggers do, and DRI is easier to maintain and troubleshoot than triggers are. DRI is checked by the server before the server performs the actual data modification request. When you use triggers, the data modification requests are inserted in the Inserted and Deleted temporary system tables, and the trigger code is run. Depending on the trigger code, the final modifications are then made or not made,
The sample screen shot in Figure 14.2 shows an execution plan that accesses only one table, although two tables are included in a join in the query. Because there is a declared foreign key relationship between the authors table and the titleauthor table, and the au_id column in the titleauthor table is not allowed to be null, the optimizer knows it does not need to access the authors table to resolve the query. The result of the SET STATISTICS IO command also shows that the authors table is never accessed.
"
October 25, 2010 at 2:31 pm
Everything is good except you left out the fact the FK constraint will slow inserts because the constraint has to be checked so keep that in mind.....
November 15, 2010 at 6:42 am
hi
I have a debate over this concept.
Foreign key and check constraints are referential integrity components. How we can say this will increase the query performance.
Yes it may increase the performance in some aspects but not always.
Query performance always depends upon the indexes getting used. not with the referential integrity components.
If i dont have any physical delete operation in my database. Why do i need a foreign key/check constraint, which is going to degrade the performance of insert/update operations.
Please correct me if i am wrong.
Regards
VMSSanthosh
November 15, 2010 at 6:52 am
vmssanthosh (11/15/2010)
hiI have a debate over this concept.
Foreign key and check constraints are referential integrity components. How we can say this will increase the query performance.
Yes it may increase the performance in some aspects but not always.
Query performance always depends upon the indexes getting used. not with the referential integrity components.
If i dont have any physical delete operation in my database. Why do i need a foreign key/check constraint, which is going to degrade the performance of insert/update operations.
Please correct me if i am wrong.
Regards
VMSSanthosh
You are mostly correct, but you do read the data far more often then you write it, correct? It is in this situation that the optimizer can be better with absolute certainty about the relationships. Hence it is often good to have referential integrity with foreign keys that are trusted.
Where the optimizer can do a better job is in eliminating joins that are not required when the foreign keys are trusted. Imagine a view that uses inner joins to combine attributes from several normalised tables and present it as one table. If a consuming query that uses this view and only requests attributes from one or two tables, then the query optimizer can only remove the unneeded joins if there are trusted foreign keys to these tables that are used in the view. It can do so, because it knows implicitly the records in the unneeded tables do exist, which would affect the outcome of the query if they did not.
Without trusted relationships, the optimiser must make a plan that joins the unused tables too, just to make sure a record can be part of the result set. This is the type of real situation you see performance benefits of having trusted relationships.
November 15, 2010 at 7:21 am
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
November 15, 2010 at 7:27 am
I guess you didn't read what everyone has written.
Doug
November 15, 2010 at 7:30 am
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.
November 15, 2010 at 7:41 am
vmssanthosh (11/15/2010)
hiWe 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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 15, 2010 at 9:17 am
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
November 15, 2010 at 9:25 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 15, 2010 at 9:48 am
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
April 25, 2013 at 10:53 pm
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
April 26, 2013 at 1:33 am
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
April 26, 2013 at 2:35 am
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!
April 26, 2013 at 3:36 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 46 through 60 (of 92 total)
You must be logged in to reply to this topic. Login to reply