Hi Andrus,
About the 'DISTINCT' part that I would write you back about.
To be sure I formulated my case well, I had to look it up in a couple of
guides. As you will notice I will use the Oracle database as the datastore,
simply because I have the most experience with this database.
I'd got confirmed that the most important reason not to use DISTINCT is the
fact that is forces the database to do a 'SORT UNIQUE' which can cost more
disk reads and more processing time when reading the results.
This information comes from the 'Oracle 9i Performance and Tuning Guide,
p2-14):
The distinct part results in a SORT-UNIQUE action... SORT UNIQUE occurs if a
user specifies a DISTINCT clause (Example 2û15)or if an operation requires
unique values for the next step (Example 2û16). Example 2û15 DISTINCT Clause
Causing SORT UNIQUE
SELECT DISTINCT last_name, first_name
FROM per_all_people_f
WHERE full_name LIKE :b1;
Plan
--------------------------------------------------
SELECT STATEMENT
SORT UNIQUE
TABLE ACCESS BY INDEX ROWID PER_ALL_PEOPLE_F
INDEX RANGE SCAN PER_PEOPLE_F_N54
Another, not technical, reason is that in general it is a bad practice to
use
DISTINCT, because the query writer 'admits' that his 'where-logic' is not
strong
enough to return the results that are required. However, I don't quite think
this is 100% valid for Cayenne, because Cayenne can not always tell if that
is
the case.
Still I do feel that the developer can have the option leaving the distinct
out
by using query.setDistinct(false), because it gives somewhat more space to
tuning.
Regards, Laszlo
_________________________________________________________________
MSN 8 with e-mail virus protection service: 2 months FREE*
http://join.msn.com/?page=features/virus
This archive was generated by hypermail 2.0.0 : Tue May 13 2003 - 01:42:26 EDT