Removing constraints for performance.

Question

Removing constraints for performance.

ben.brugman

SSChampion

Points: 13369
More actions
October 26, 2012 at 1:50 am

#263792

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 SSChampion Points: 13369 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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 SSC-Dedicated Points: 34562 More actions · Answer 4

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[/url] 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 SSChampion Points: 13369 More actions · Answer 5

David Benoit (10/26/2012)
Also may want to check out THIS ARTICLE[/url] 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 SSC-Dedicated Points: 34562 More actions · Answer 6

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