Hi people,

I have a database with Dialect 1 and 8K Page Size running on my development
machine that is a PIII 1GHZ 256 MB RAM on Windows XP. I need to run the
following query:

select
min(T.Data)
from
Turno T join
MaquinaOcupacao MO on (MO.TurnoID = T.TurnoID)
where
T.Data > '2002-12-01' and
MO.MaquinaID = 17

Wich should return
'2002-12-02' for this case...

the execution plan is:
PLAN JOIN (MO INDEX (RDB$FOREIGN209),T INDEX (RDB$PRIMARY206,SK_TURNO_DATA))

the statistics returned from IBConsole is:
Prepare Time 0.0000 sec
Execution Time 9.0373 sec
Starting Memory 69608448
Current Memory 69623808
Delta Memory 15360
Number of Buffers 8192
Read 522
Writes 55


create table turno(
TURNOID integer,
DATA date,
HORAINICIO date,
HORAFIM date);

alter table turno add constraint PK_TURNO primary key (TURNOID);
create index SK_TURNO_DATA on turno(DATA);

create table maquinaocupacao(
MAQUINAOCUPACAOID integer,
MAQUINAID integer,
TURNOID integer,
TEMPOTOTAL float,
TEMPOOCUPADO float,
CMP_TEMPODISPONIVEL computed by (TempoTotal - TempoOcupado));

alter table maquinaocupacao add constraint PK_MAQUINAOCUPACAO primary key
(MAQUINAOCUPACAOID);
alter table maquinaocupacao add constraint FK_MAQUINAOCUPACAO_MAQUINA
foreign key (MAQUINAID) references MAQUINATIPO(MAQUINATIPOID);
alter table maquinaocupacao add constraint FK_MAQUINAOCUPACAO_TURNO foreign
key (TURNOID) references TURNO(TURNOID);

The database statistics are:

Table Turno has 7788 rows
Table MaquinaOcupacao has 23364 rows

TURNO (342)
Primary pointer page: 994, Index root page: 997
Data pages: 68, data page slots: 88, average fill: 66%
Fill distribution:
0 - 19% = 0
20 - 39% = 2
40 - 59% = 1
60 - 79% = 65
80 - 99% = 0

Index RDB$PRIMARY206 (0)
Depth: 2, leaf buckets: 10, nodes: 7788
Average data length: 1.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 1
40 - 59% = 3
60 - 79% = 0
80 - 99% = 5

Index SK_TURNO_DATA (1)
Depth: 2, leaf buckets: 11, nodes: 7788
Average data length: 1.00, total dup: 5192, max dup: 2
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 4
60 - 79% = 7
80 - 99% = 0

MAQUINAOCUPACAO (343)
Primary pointer page: 1001, Index root page: 1002
Data pages: 218, data page slots: 218, average fill: 81%
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 23
60 - 79% = 73
80 - 99% = 121

Index RDB$FOREIGN209 (1)
Depth: 2, leaf buckets: 32, nodes: 23364
Average data length: 0.00, total dup: 23357, max dup: 5191
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 29
60 - 79% = 2
80 - 99% = 1

Index RDB$FOREIGN210 (2)
Depth: 2, leaf buckets: 24, nodes: 23364
Average data length: 0.00, total dup: 15576, max dup: 4
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 10
60 - 79% = 0
80 - 99% = 13

Index RDB$PRIMARY207 (0)
Depth: 2, leaf buckets: 28, nodes: 23364
Average data length: 1.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 14
60 - 79% = 0
80 - 99% = 13

What do you think I could do to increase the speed ?
I run a test using denormalized data (put the field "Data" on table
MaquinaOcupacao), the time to complete was:

select
min(MO.Data)
from
MaquinaOcupacao MO
where
MO.Data > '2002-12-01' and
MO.MaquinaID = 17

the plan was:
PLAN (MO INDEX (RDB$FOREIGN209))

the execution time 0.0281 sec

if I create an index on Field "Data" running the same query give the
following results:
PLAN (MO ORDER SK_MAQUINAOCUPACAO_DATA)
execution time: 0.0120 sec

I don't want to denormalize the data, not as my first choice to improve the
speed. Any suggestions ?

thanks very much to read it all...

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br