Hi,

> I have a query, that runs to slow and I'm looking for hints how to
> optimize it. You find at the end of the message:
> - the query
> - the plan
> - the DDL
>
> I'm using FB 1.5 build 4306, Win2000. If I add the commented part in
> the where clause, the query needs one second. If I want all athletes,
> then it takes up to 30 seconds.
>
> In addition to speed up the query, I would like to recive only one
> record per T_ATHLETE from T_RESULT with the condition min(SWIMTIME).
>
> I was thinking about denormalize the schema and put more columns
> directly in T_RESULT. All queries will filter on T_EVENT.STYLEID, so
> this column is a candiate to add it to T_RESULT.
>
> Every hint about speeding up the query is appreciated. I can provide a
> test database. Just send me an email for the download link
> (webmaster@...).

What if you add a compound index :

CREATE INDEX IDX_RES_ATHID_AGE_TIME ON T_RESULT (T_ATHLETEID, T_AGEGROUPID,
SWIMTIME);

first try this index with your original query to see if it makes difference,
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,
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
A.LASTNAME,
A.FIRSTNAME,
A.BIRTHDATE,
S.STARTDATE,
M.CITY,
M.NATION,
C.CODE

Hoping i didn't forget a field ;-)

I'm not familar with this sport, but doesn't the AGEGROUP belong more to the
ATHLETE then the RESULT table?

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