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


Removing constraints for performance.


Removing constraints for performance.

Author
Message
ben.brugman
ben.brugman
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: 1088 Visits: 2391
First I consider constraints as safetybelts. I think constraints should be used as much as possible. For normal operations they should be used all times.

Constraints and performance.
I have been looking for examples where constraints do help the performance.
(I have seen cases where constraints were did influence performance in a negative way).

On:
http://msdn.microsoft.com/en-us/library/ms345146(v=sql.90).aspx
"Note A constraint can become "untrusted" in various ways; for instance, if a bulk insert is performed without specifying the CHECK_CONSTRAINTS argument or if a constraint is created with NOCHECK. If a constraint is untrusted, the query processor will revert to scanning all base tables as it has no way of verifying that the requested data is in fact located in the correct base table."

This is at least an indication that constraints can help the performance.

Please help me in finding more examples (or webreferences) where constraints do actually help the performance.

Thanks,
Ben Brugman
GilaMonster
GilaMonster
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91355 Visits: 45285
Constraints can and do help performance. Unique constraints may allow SQL to ignore DISTINCT operations or reduce group by statements to simpler forms or know absolutely for sure how many rows a particular operation will affect. Check constraints can allow SQL to ignore entire predicates (eg WHERE x > 0 when there's a constraint forcing x to be > 0) or if a predicate requires rows that violate constraints (where x IS NULL on a not null column). Similar with foreign keys.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


ben.brugman
ben.brugman
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: 1088 Visits: 2391
GilaMonster (10/26/2012)
Constraints can and do help performance. Unique constraints may allow SQL to ignore DISTINCT operations or reduce group by statements to simpler forms or know absolutely for sure how many rows a particular operation will affect. Check constraints can allow SQL to ignore entire predicates (eg WHERE x > 0 when there's a constraint forcing x to be > 0) or if a predicate requires rows that violate constraints (where x IS NULL on a not null column). Similar with foreign keys.


Thanks for your anwser.
I need an example which is a bit more convincing.

Problem:
Relational constraints were removed because of performance problems.

This should not happen.
Argument 1:
I think that this is a design problem. With a 'correct' design and implementation, constraints should not lead to performance problems.
Argument 2: Constraints in general, but relation constraints specifically help the query processor to build better query plans.

But I can not find any convincing examples of this. Without the convincing examples or webreferences the above arguments are weak.

I tried to build some constructions to demonstrate the effect of removing the relational constraints and show that this was bad for performance regrettably I did not succeed.

Again thanks for your anwser and time
Ben
GilaMonster
GilaMonster
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91355 Visits: 45285
There are definitely articles out there on this, I don't have links onhand, could hit google and search.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


David Benoit
David Benoit
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3526 Visits: 3650
Read section 14.2 and 14.3 in this link - http://msdn.microsoft.com/en-us/library/ff647793.aspx

Also may want to check out THIS ARTICLE from this site.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
ben.brugman
ben.brugman
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: 1088 Visits: 2391
David Benoit (10/26/2012)

Also may want to check out THIS ARTICLE from this site.


Thank you for the reference to this article. This is what I was looking for.

With google I did search for performance improvements by relations constraints, but probably just didn't use the 'right' searchwords.

Thanks,
Ben Brugman
David Benoit
David Benoit
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3526 Visits: 3650
Glad to hear that helped!

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
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