Hi,

> > What if you add a compound index :
> speed is a bit better with the original query (18 seconds now).

First update your index-selectivity from the indexes in question with SET
STATISTICS INDEX index_name, so we know for sure the optimizer makes
decisions on the right information.
Could you also post the PLAN?

> > after that you could try this one :
> >
> > SELECT
> > (SELECT R2.T_RESULTID FROM T_RESULT R2
> > WHERE R2.T_AGEGROUPID = AG.T_AGEGROUPID and
> > R2.T_ATHLETEID = A.T_ATHLETEID and
> > R2.SWIMTIME = MIN(R.SWIMTIME)),
> > MIN(R.SWIMTIME),
> > A.LASTNAME,
>
> I receive an error with this query:
> "Dynamic SQL Error SQL error code = -104 Invalid expression in the
> select list (not contained in either an aggregate function or the
> GROUP BY clause)"

Ooops i forgot the sub-select :

SELECT
(SELECT R2.T_RESULTID FROM T_RESULT R2
WHERE R2.T_AGEGROUPID = AG.T_AGEGROUPID and
R2.T_ATHLETEID = A.T_ATHLETEID and
R2.SWIMTIME = MIN(R.SWIMTIME)),
MIN(R.SWIMTIME),
A.LASTNAME,
A.FIRSTNAME,
A.BIRTHDATE,
S.STARTDATE,
M.CITY,
M.NATION,
C.CODE
FROM
T_RESULT R
JOIN T_ATHLETE A ON (A.T_ATHLETEID = R.T_ATHLETEID)
JOIN T_AGEGROUP AG ON (AG.T_AGEGROUPID = R.T_AGEGROUPID)
JOIN T_EVENT E ON (E.T_EVENTID = AG.T_EVENTID)
JOIN T_SESSION S ON (S.T_SESSIONID = E.T_SESSIONID)
JOIN T_MEET M ON (M.T_MEETID = S.T_MEETID)
JOIN T_ATHLETEMEET MM ON (MM.T_MEETID = M.T_MEETID and
MM.T_ATHLETEID = A.T_ATHLETEID)
JOIN T_CLUB C ON (C.T_CLUBID = MM.T_CLUBID)
WHERE
E.T_STYLEID = 1 and
A.GENDER = 0
/* and C.T_CLUBID = 56 */
GROUP BY
AG.T_AGEGROUPID,
A.T_ATHLETEID,
A.LASTNAME,
A.FIRSTNAME,
A.BIRTHDATE,
S.STARTDATE,
M.CITY,
M.NATION,
C.CODE

> I don't really understand the first column in the select statement.
> This is probably the column, that causes the error, because it's not
> in the group clause.

Indeed, i put it in because you wanted the T_RESULTID that belongs to the
MIN(SWIMTIME) right? If you don't need you could better remove that
sub-query.

> > I'm not familar with this sport, but doesn't the AGEGROUP belong
> > more to the ATHLETE then the RESULT table?
> No. I look at the data in two different ways:
> - Meet results: Here I display only the results of one meet and these
> results are grouped by events and agegroups.
> - Rankings: Here the agegroup has no effect. If I use the age here, it
> is calcuated between the ATHLETE.BIRTHDATE and the SESSION.STARTDATE.
> That's why I was thinking of changing the schema and adding the
> T_STYLEID and the T_MEETID directly in the table T_RESULT.

It will speed-up, but if it really helps that much i don't darve to say.
How many records should be choosen from the result table with a specific
T_STYLEID ?


Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81