At 09:53 PM 17/08/2006, zinfabbe wrote:

>But another question (concerning the correlated subquery):
>
>Is a correlated query always better than a join?

No. With a large set, it is very costly, because the subquery is
called for every row.

>Or is it only better in this case (ie a point query).

Not sure what you mean by "a point query". It's always wise to
test. Sets of around 200 rows are fine with a correlated subquery
looking up one field. Sets of many thousands looking up several
fields will be very slow, as each CSQ must run for each row.

>I assume it is only better in this case and that a join is still
>better in the case when not using the "WHERE (PERSON_ID = :PersonID)"

Yes, always. A join for this is a waste of resources on both client
and server sides.

But the rules are not hard and fast. You should always test both
ways with representative data to work out which suits the actual
requirement better. For example, you can have a lot of key-related
problems when you try to use outer joined sets; and you must use a
CSQ when embedding lookupcombo controls in a grid; and others.

Helen