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@...).

cu Christian

-----------------------------

select
R.T_RESULTID, 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 */
order by
R.SWIMTIME

-----------------------------

PLAN SORT (JOIN (A NATURAL,MM INDEX (RDB$FOREIGN23),M INDEX
(RDB$PRIMARY5),S INDEX (RDB$FOREIGN14),E INDEX (RDB$FOREIGN15),AG
INDEX (RDB$FOREIGN17),R INDEX (RDB$FOREIGN18,RDB$FOREIGN19),C INDEX
(RDB$PRIMARY3)))

-----------------------------

CREATE TABLE T_RESULT /* aprox. 900'000 records */
(
T_RESULTID INTEGER,
T_AGEGROUPID INTEGER,
T_ATHLETEID INTEGER,
COMMENT BLOB SUB_TYPE 1 SEGMENT SIZE 80,
POINTS SMALLINT,
PLACE SMALLINT,
REACTIONTIME SMALLINT,
STATUS SMALLINT,
SWIMTIME INTEGER,
CONSTRAINT PK_RESULT PRIMARY KEY (T_RESULTID)
);
CREATE ASC INDEX IX_RESULT_SWIMTIME ON T_RESULT (SWIMTIME);


RECREATE TABLE T_ATHLETE /* aprox. 15'000 records */
(
T_ATHLETEID INTEGER NOT NULL,
T_CLUBID INTEGER,
BIRTHDATE TIMESTAMP,
FINAID INTEGER,
FIRSTNAME VARCHAR( 30) ,
GENDER SMALLINT DEFAULT 0,
LASTNAME VARCHAR( 50) ,
MIDDLENAME VARCHAR( 30) ,
NATION VARCHAR( 3) ,
CONSTRAINT PK_ATHLETE PRIMARY KEY (T_ATHLETEID)
);


RECREATE TABLE T_AGEGROUP /* aprox. 56'000 records */
(
T_AGEGROUPID INTEGER NOT NULL,
T_EVENTID INTEGER NOT NULL,
AGEMAX SMALLINT,
AGEMIN SMALLINT,
GENDER SMALLINT DEFAULT 0,
NAME VARCHAR( 40) ,
CONSTRAINT PK_AGEGROUP PRIMARY KEY (T_AGEGROUPID)
);


RECREATE TABLE T_EVENT /* aprox. 56'000 records */
(
T_EVENTID INTEGER NOT NULL,
T_SESSIONID INTEGER NOT NULL,
T_STYLEID INTEGER NOT NULL,
EVENTSORT INTEGER,
GENDER SMALLINT DEFAULT 0,
NUMBER SMALLINT,
PREVEVENTID INTEGER,
ROUND SMALLINT,
DAYTIME TIMESTAMP,
CONSTRAINT PK_EVENT PRIMARY KEY (T_EVENTID)
);


RECREATE TABLE T_SESSION /* aprox. 3'000 records */
(
T_SESSIONID INTEGER NOT NULL,
T_MEETID INTEGER NOT NULL,
NUMBER SMALLINT,
COURSE SMALLINT,
STARTTIME TIMESTAMP,
STARTDATE TIMESTAMP,
LANEMIN SMALLINT,
LANEMAX INTEGER,
TEMPERATURE SMALLINT,
POOLNAME VARCHAR( 50) ,
CONSTRAINT PK_SESSION PRIMARY KEY (T_SESSIONID)
);


RECREATE TABLE T_MEET /* aprox. 3'000 records */
(
T_MEETID INTEGER NOT NULL,
AGEDATE TIMESTAMP,
ALTITUDE SMALLINT,
CITY VARCHAR( 30) ,
FINAID INTEGER,
MEETTYPE SMALLINT,
NAME VARCHAR( 100) ,
NATION VARCHAR( 3) ,
ORGANIZER VARCHAR( 50) ,
POOLNAME VARCHAR( 50) ,
PROMOTER VARCHAR( 50) ,
STATE VARCHAR( 4) ,
TIMING SMALLINT,
CONSTRAINT PK_MEET PRIMARY KEY (T_MEETID)
);


RECREATE TABLE T_ATHLETEMEET /* aprox. 250'000 records */
(
T_ATHLETEID INTEGER NOT NULL,
T_MEETID INTEGER NOT NULL,
T_CLUBID INTEGER NOT NULL,
CONSTRAINT PK_ATHLETEMEET PRIMARY KEY (T_ATHLETEID, T_MEETID)
);


RECREATE TABLE T_CLUB /* aprox. 400 records */
(
T_CLUBID INTEGER NOT NULL,
PARENTCLUBID INTEGER,
CLUBTYPE SMALLINT,
CODE VARCHAR( 8) ,
FINAID INTEGER DEFAULT 0,
NAME VARCHAR( 100) ,
CONSTRAINT PK_CLUB PRIMARY KEY (T_CLUBID)
);