Re: Foreign Keys Constraints, perforamance analysis

> > No,
> > I compare
> > DELETE FROM person;
> > against
> > DELETE FROM person;
> > DELETE FROM married;
> > DELETE FROM child;
> >
> > Which I think has very much to do with performane of real-worl
> applications
> > i think. I often think of Accounts, where there are numerous records
> stored
> > for this account - which should be deleted when the account is deleted.
>
> It doesn't unless you delete all your people alot (as Tom said).

Agreed, but I don't want to measure the performance of real-world
application anyway, I just want
to issolate how much you loose having the database manager handle the
deletion for you, as the ON DELETE
CASCADE foreign key constraint does.

> There's a BIG difference between
> delete from person where name='foo' compared to
> delete from person where name='foo'; delete from married where ... ;
delete
> from child where ...;
> and
> delete from person; compared to
> delete from person; delete from married; delete from child;

I can see that,
In the first case there are a hell lot of overhead sending the queries.

> In the first case, the system sees either 1 statement that expands into 3
> statements effectively versus 3 statements. Not too different.

ok...

> In the second case the system sees 1 statement + 1 statement per row
versus
> 3 statements.

I can't see what you mean here... "+ 1 statement per row"... there is only
one row?

> Very different, because it doesn't know it's going to be deleting all of
the
> rows so it's probably going to choose to index scan to find the matching
> rows for each row per each row in person versus knowing before hand to
> delete them all.

OK... hmm... *confused* :)

What is the difference between these two (only comparing the tables with
foreign keys constraits now):

DELETE FROM PERSON;

and

DELETE FROM PERSON where id = 1;
DELETE FROM PERSON where id = 2;

The only thing I can see (which I assume is what I do wrong here), is that
there is a lot of overhead sending the queries. If we ignore the overhead in
our conversation, what is the difference?

> In addition, with match unspecified, these two behaviors are also not
> guaranteed to be the same. With NULLs in the FK fields, you can have rows
> that shouldn't get deleted when you delete all of the PK rows. ("At least
> one of the values of the referencing columns in R1 shall be a null value,
or
> the value of each referencing column in R1 shall be equal to the value of
> the corresponding referenced column in some row of the referenced
table....
> let matching rows be all rows in the referencing table whose referencing
> column values equal the corresponding referenced column values for the
> referential constraint")

OK, but this is just a test i write. I _am_ sure there are no NULLs there. I
just want to make myself
aware of how what it costs in performance having foreign keys constraints.

> There are problems, and it would be nice to figure out a way to combine
> actions and checks when a large number of changes are seen (of course how
do
> you define a large number, but...) to get around some of these bulk cases.

before I send this message I just gotta say thanks!
i appreciate your input more than you think :)

Anyway, what I do is, in pseudocode:

FOR ( i = 1 to N*2)
insert into person

FOR (i = 1 to N)
insert into married or married_fkc

FOR (i = 1 to 2*N)
insert into child or child_fkc

if (fkc)
delete from person;
else
delete from person, delete from married, delete from child;

I guess this last example shows quite good what I do. Don't this change your
minds?

Daniel Åkerud