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 «««34567»»»

A Check and Foreign Key Constraint Improves Query Performance Expand / Collapse
Author
Message
Posted Friday, October 22, 2010 9:40 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
Rich,

Why would you just randomly run that against ALL tables? Mine was targeted to the FKs marked as untrusted.

Wondering,
Doug
Post #1009309
Posted Friday, October 22, 2010 12:05 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:01 PM
Points: 687, Visits: 3,002
Douglas Osborne-456728 (10/22/2010)
Rich,

Why would you just randomly run that against ALL tables? Mine was targeted to the FKs marked as untrusted.

Wondering,
Doug


Hmmm, good point. I was thinking that if the constraint was already trusted, that the ALTER command would be skipped. But that's a lazy (and potentially time-consuming) assumption on my part. As I only did this once on a database, it wasn't really an issue for me (I did this when I was down for maintenance).

This arose for a decidedly non-random reason BTW: a 3rd party app was executing several stored procedures very slowly. Turns out there were hundreds of untrusted FK constraints that were created NOT FOR REPLICATION.

Unfortunately, it also turns out that you cannot, apparently, make a constraint trusted if it was created NOT FOR REPLICATION (I posted a while ago about this http://www.sqlservercentral.com/Forums/Topic944850-146-1.aspx). You have to DROP and CREATE the constraint.

While looking up info, I came across this post that suggests BOL may be inaccurate about this: http://connect.microsoft.com/SQLServer/feedback/details/585467/with-check-constraint-can-leave-table-with-untrusted-foriegn-keys.

Yours,
Rich
Post #1009412
Posted Friday, October 22, 2010 12:48 PM
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
Rich,

Nice add - I didn't know that NOT FOR REPLICATION would do that.

This has been a really good article and an extensive followup discussion.

Best,
Doug
Post #1009438
Posted Friday, October 22, 2010 2:23 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:38 AM
Points: 215, Visits: 552
Interesting article! Thanks for taking the time to put it together.

Regards,

--Mike
Post #1009491
Posted Sunday, October 24, 2010 11:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 17, 2014 12:56 PM
Points: 18, Visits: 174

I agree. You could just write
select so.* from SalesOrderdetail as so
and get the same result. (with the not null model)

That code would only be written if the developer did not know the data structure, or did not TRUST it. At least the cost of that distrust is very low (optimizer time only).

I think the cost of a FK constraint is in the insert and update side. There is a savings when the developer does not have to worry about detail rows that don't join to the header. That saving exists even without a FK constraint, if the data entry system is trusted to ensure it. But I would tend to constrain the data to ensure that the data entry code continues to work properly.

I think the conclusion that constraints improve performance is not sufficiently proven here.

But this article brought up several good points: 1) Making a FK null where the PK is not null will allow null values to be added that are not constrained. That is an interesting point. It is a good practice to keep the pk and FK are the same, even down to the null/not null constraint.
2) Check for untrusted constraints and correct them, as several of us have already done.

Thank you for a stimulating and informative article.
Post #1009703
Posted Monday, October 25, 2010 11:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 15, 2011 12:49 PM
Points: 1, Visits: 22
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.
"


Post #1010226
Posted Monday, October 25, 2010 2:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 10, 2011 8:45 AM
Points: 1, Visits: 19
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.....
Post #1010385
Posted Monday, November 15, 2010 6:42 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 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
Post #1020722
Posted Monday, November 15, 2010 6:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:18 AM
Points: 323, Visits: 2,200
vmssanthosh (11/15/2010)
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


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.
Post #1020734
Posted Monday, November 15, 2010 7:21 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
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
Post #1020769
« Prev Topic | Next Topic »

Add to briefcase «««34567»»»

Permissions Expand / Collapse