Thread: constraints and performance

Do constraints effect performance significantly?
Thanks
Jodi

On Wed, 11 Feb 2004, Jodi Kanter wrote:

> Do constraints effect performance significantly?

That depends.

Foreign key constraints on int4 columns or int8 columns (matching of
course) are usually quite fast.

FK constraints on non-int (numeric, text, etc...) tend to be slower.

check constraints can be a killer if they are complex or use functions,
but simple check constraints are usually alright.

Do you have a specific use case you're looking at?

A long time ago, in a galaxy far, far away, jkanter@virginia.edu (Jodi Kanter) wrote:
> Do constraints effect performance significantly?

They would be expected to provide a significant enhancement to
performance over:
 a) Firing triggers,
 b) Firing rules, and
 c) Forcing the application to validate the data, and then adding
    in "data validation" reports to report on the cases where a buggy
    application violated the constraints.

So yes, they should be considerably faster than any of their
alternatives.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','ntlug.org').
http://cbbrowne.com/info/lsf.html
"The only constructive theory connecting neuroscience and psychology
will arise from the study of software."
-- Alan Perlis
[To the endless aggravation of both disciplines.  Ed.]

On Wed, 11 Feb 2004, Christopher Browne wrote:

> A long time ago, in a galaxy far, far away, jkanter@virginia.edu (Jodi Kanter) wrote:
> > Do constraints effect performance significantly?
>
> They would be expected to provide a significant enhancement to
> performance over:
>  a) Firing triggers,
>  b) Firing rules, and
>  c) Forcing the application to validate the data, and then adding
>     in "data validation" reports to report on the cases where a buggy
>     application violated the constraints.
>
> So yes, they should be considerably faster than any of their
> alternatives.

A completely correct answer, but not the one I suspect Jodi wanted,
which was whether there was a "significant" penalty difference between
using constraints on a table and not using constraints on a table.

I'm not sure I have any better answer, because we don't know what
"significant" means, or the nature of the constraints.

I will share my experience, which is that constraints add little
noticable overhead in simple cases. However, when constraining cascading
deletes through many tables, for instance, it is absolutely noticable.
Between those two extremes, it isn't "too bad", for me and my
applications, wherein I rely heavily on constraints (and rules, and
server-side triggers).

I don't think it is possible to say "constraints add an n% overhead", due
to the extreme variability of the way they can be used.

Best practice, as Christopher notes, indicates that they should be used.
It saves a lot of grief (why write the code in the application layer
when you can the DB authors already have?). If you are in a
situation where the difference between using them and not using them
forms a critical boundary, I would suggest you have some other problems,
either in design or specification.

I realize that may not be helpful, given real world constraints - the
consumers of applications may not be realistic in setting requirements.

One thing to think about carefully, if building an application that has
to scale to any real degree, is the tradeoff between client side and
server side processing. While it is best-practice to keep data
validation close to the data, I have been involved in some projects
where scaling the DB server to the task was not economically possible;
the project would not have happened if that were an enforced criteria.
Messy, bad, poor practice? Yes on all counts. One must be very, very
careful if one chooses to ensure data integrity client-side.

Anyway, getting back to the question, the only real answer is "try it
and see". As far as I know, there's no way to quantify the impact of
constraints on query performance without taking the data model and
usage patterns of the application into account.

I hope this helps some.

-j


--
Jamie Lawrence                                        jal@jal.org
There is nothing more demoralizing than a small but adequate income.
   - Edmund Wilson