Click here to monitor SSC
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-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 327
Rich,

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

Wondering,
Doug
Rich Mechaber
Rich Mechaber
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1085 Visits: 3661
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
Douglas Osborne-456728
Douglas Osborne-456728
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 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
Mike M - DBA2B
Mike M - DBA2B
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 878
Interesting article! Thanks for taking the time to put it together.

Regards,

--Mike
bob amy
bob amy
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 221
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.
gmduckhunter
gmduckhunter
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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.
"

john.hall-1073044
john.hall-1073044
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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.....
vmssanthosh
vmssanthosh
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 167
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
peter-757102
peter-757102
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 2549
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.
vmssanthosh
vmssanthosh
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 167
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
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