Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««45678»»»

A Check and Foreign Key Constraint Improves Query Performance Expand / Collapse
Author
Message
Posted Monday, November 15, 2010 7:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 26, 2011 1:32 PM
Points: 146, Visits: 327
I guess you didn't read what everyone has written.

Doug
Post #1020771
Posted Monday, November 15, 2010 7:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 22, 2014 3:42 AM
Points: 337, Visits: 2,283
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.
Post #1020774
Posted Monday, November 15, 2010 7:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, December 22, 2014 3:26 AM
Points: 14,205, Visits: 28,536
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
Post #1020782
Posted Monday, November 15, 2010 9:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 11, 2014 9:03 AM
Points: 126, Visits: 165
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
Post #1020871
Posted Monday, November 15, 2010 9:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, December 22, 2014 3:26 AM
Points: 14,205, Visits: 28,536
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
Post #1020876
Posted Monday, November 15, 2010 9:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 11, 2014 9:03 AM
Points: 126, Visits: 165
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
Post #1020892
Posted Thursday, April 25, 2013 10:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 4:13 AM
Points: 17, Visits: 31
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

Post #1446791
Posted Friday, April 26, 2013 1:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 26, 2013 5:33 AM
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
Post #1446826
Posted Friday, April 26, 2013 2:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 22, 2014 3:42 AM
Points: 337, Visits: 2,283
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!
Post #1446852
Posted Friday, April 26, 2013 3:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, December 22, 2014 3:26 AM
Points: 14,205, Visits: 28,536
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
Post #1446873
« Prev Topic | Next Topic »

Add to briefcase «««45678»»»

Permissions Expand / Collapse