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

Removing constraints for performance. Expand / Collapse
Author
Message
Posted Friday, October 26, 2012 1:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:11 AM
Points: 236, Visits: 1,107
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
Post #1377427
Posted Friday, October 26, 2012 6:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 41,558, Visits: 34,479
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 2008, MVP
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

Post #1377532
Posted Friday, October 26, 2012 6:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:11 AM
Points: 236, Visits: 1,107
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
Post #1377554
Posted Friday, October 26, 2012 8:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 41,558, Visits: 34,479
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 2008, MVP
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

Post #1377610
Posted Friday, October 26, 2012 8:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:20 PM
Points: 2,105, Visits: 3,560
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
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #1377629
Posted Monday, October 29, 2012 11:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:11 AM
Points: 236, Visits: 1,107
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
Post #1378378
Posted Monday, October 29, 2012 11:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:20 PM
Points: 2,105, Visits: 3,560
Glad to hear that helped!

David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #1378385
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse