Hi Alexandre;

Here I am adding two real examples.

In the FIRST one, I run a query for getting PROC rows filtering with
values from other tables. Super-fast, less than one second.

In the NEXT one, I run a query for getting the same PROC rows, but
adding the values from MOVI (it´s expected that MOVI can return more
than one row for each PROC row, when there are more than one MOVI
with same FECH). Very slow, near than 6 minutes.

FIRST:

Preparing query:
select "PROC"."PROC","PROC"."CARP","PROC"."ACTO","PROC"."DEMA","PROC".
"OBSE","TPRO"."DSCR" as "D_TPRO" from "PROC"
left outer join "TPRO" on "PROC"."TPRO"="TPRO"."TPRO"
where (("PROC"."PROC" in (select "PROC" from "PROC" left outer
join "OJUD" on "PROC"."OJUD"="OJUD"."OJUD" left outer join "ZONA"
on "OJUD"."ZONA"="ZONA"."ZONA" where ("ZONA"."ORDE"
between '000000550' and '000000550')))) order by lower
("PROC"."ACTO"),"PROC"."PROC"
Prepare time: 00:00:00.
Field #01: PROC.PROC Alias:PROC Type:STRING(10)
Field #02: PROC.CARP Alias:CARP Type:STRING(18)
Field #03: PROC.ACTO Alias:ACTO Type:STRING(200)
Field #04: PROC.DEMA Alias:DEMA Type:STRING(200)
Field #05: PROC.OBSE Alias:OBSE Type:STRING(120)
Field #06: TPRO.DSCR Alias:D_TPRO Type:STRING(120)
PLAN JOIN (JOIN (PROC INDEX (PROC_PK), OJUD INDEX (OJUD_PK)), ZONA
INDEX (ZONA_PK))
PLAN SORT (JOIN (PROC NATURAL, TPRO INDEX (TPRO_PK)))


Executing...
Done.
221817 fetches, 0 marks, 702 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 41664 index, 11427 seq.
Delta memory: -24680 bytes.
Execute time: 00:00:01.
Script execution finished.

NEXT:

Preparing query:
select "PROC"."PROC","PROC"."CARP","PROC"."ACTO","PROC"."DEMA","PROC".
"OBSE","TPRO"."DSCR" as "D_TPRO",m3."FECH",m3."HORA",m3."DSCR"
as "D_MOVI" from "PROC"
left outer join "TPRO" on "PROC"."TPRO"="TPRO"."TPRO"
left outer join (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') m3 on "PROC"."PROC"=m3."PROC"
where (("PROC"."PROC" in (select "PROC" from "PROC" left outer
join "OJUD" on "PROC"."OJUD"="OJUD"."OJUD" left outer join "ZONA"
on "OJUD"."ZONA"="ZONA"."ZONA" where ("ZONA"."ORDE"
between '000000550' and '000000550')))) order by lower
("PROC"."ACTO"),"PROC"."PROC"
Prepare time: 00:00:00.
Field #01: PROC.PROC Alias:PROC Type:STRING(10)
Field #02: PROC.CARP Alias:CARP Type:STRING(18)
Field #03: PROC.ACTO Alias:ACTO Type:STRING(200)
Field #04: PROC.DEMA Alias:DEMA Type:STRING(200)
Field #05: PROC.OBSE Alias:OBSE Type:STRING(120)
Field #06: TPRO.DSCR Alias:D_TPRO Type:STRING(120)
Field #07: MOVI.FECH Alias:FECH Type:STRING(8)
Field #08: MOVI.HORA Alias:HORA Type:STRING(4)
Field #09: . Alias:D_MOVI Type:STRING(200)
PLAN (M3 M2 INDEX (MOVI_PROC))
PLAN JOIN (JOIN (PROC INDEX (PROC_PK), OJUD INDEX (OJUD_PK)), ZONA
INDEX (ZONA_PK))
PLAN JOIN (SORT (JOIN (PROC NATURAL, TPRO INDEX (TPRO_PK))), M3 M1
INDEX (MOVI_PROC))


Executing...
Done.
232364131 fetches, 0 marks, 5599615 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 114710623 index, 11427 seq.
Delta memory: 24788 bytes.
Execute time: 00:05:55.
Script execution finished.

Dany