Hi, Alexandre;

Thank you very much.

I think 100% like you. Also, I know this forum is amazing, with lots
of guys trying to help.

Moreover, they helped me a lot in this special case, but it still has
problems.

The situation is simple: a main table called PROC which has a
relation to multiple records in another table called MOVI. MOVI has a
field FECH with a date value in string format (ej. '20081212')

Each PROC row, may have CERO to lots of related rows in MOVI.

MOVI is defined as:

CREATE TABLE MOVI
(
MOVI Char(10) NOT NULL,
PROC Char(10) NOT NULL,
MIEM Char(10) NOT NULL,
TIPO Char(1) NOT NULL,
AVIS Char(2) NOT NULL,
HECH Char(1) NOT NULL,
FECH Char(8) NOT NULL,
HORA Char(4) NOT NULL,
DSCR Varchar(200) NOT NULL,
FOJA Varchar(10) NOT NULL,
PRES Char(1) NOT NULL,
TEXT Blob sub_type 0,
CONSTRAINT MOVI_PK PRIMARY KEY (MOVI)
);

CREATE INDEX MOVI_FECH ON MOVI (FECH);
CREATE INDEX MOVI_HECH ON MOVI (HECH);
CREATE INDEX MOVI_MIEM ON MOVI (MIEM);
CREATE INDEX MOVI_PRES ON MOVI (PRES);
CREATE INDEX MOVI_PROC ON MOVI (PROC);
CREATE INDEX MOVI_TIPO ON MOVI (TIPO);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON MOVI TO LEX WITH GRANT OPTION;

Sometimes I have to build queries with PROC rows + the MOVI rows with
the gratest FECH value. The best query expression I found for finding
that MOVI row for each PROC row that (with the help of this forum!!!)
is:

select m1."PROC",m1."FECH",m1."HORA",m1."DSCR" from "MOVI" m1
where m1."FECH"=(select max(m2."FECH") from "MOVI" m2 where
m1."PROC"=m2."PROC" and m2."TIPO" = '1' and m2."HECH" = 'P') and
m1."TIPO" = '1' and m1."HECH" = 'P'

(Please note that MOVI is filtered by a few things
(ej. "TIPO", "HECH", and others on each special case)).

Running that query with FlameRobin:

Preparing query: select m1."PROC",m1."FECH",m1."HORA",m1."DSCR"
from "MOVI" m1 where m1."FECH"=(select max(m2."FECH") from "MOVI" m2
where m1."PROC"=m2."PROC" and m2."TIPO" = '1' and m2."HECH" = 'P')
and m1."TIPO" = '1' and m1."HECH" = 'P'
Prepare time: 00:00:00.
Field #01: MOVI.PROC Alias:PROC Type:STRING(10)
Field #02: MOVI.FECH Alias:FECH Type:STRING(8)
Field #03: MOVI.HORA Alias:HORA Type:STRING(4)
Field #04: MOVI.DSCR Alias:DSCR Type:STRING(200)
PLAN (M2 INDEX (MOVI_PROC))
PLAN (M1 INDEX (MOVI_TIPO, MOVI_HECH))


Executing...
Done.
7716517 fetches, 4 marks, 1797839 reads, 4 writes.
0 inserts, 0 updates, 0 deletes, 3790122 index, 0 seq.
Delta memory: 4137208 bytes.
Execute time: 00:00:31.
Script execution finished.

The size of PROC and MOVI (in number of rows) depends of each user,
but MOVI can be 1 or 2 millon records or more on some users. I am
testing with the last FB 2.5, and I think the query works fast, but
the full data retrieving process becomes to slow.

It will be very nice if the solution is just adding indexes, but I
can't see what kind of indexes I can add for doing this better.

Please let me know how other thing I can tell you for helping you
helping me <g>.

Best regards,

Dany