I'm not an expert, but I had a little look at your SQL. A couple of
questions that might help, but might also make no difference or might
even mess things up :-) Careful!

danyschaer wrote:

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

In the outmost where clause's subquery for ZONA.ORDE you use left outer
joins, but then use a where clause that checks the joined table for
non-null values. It should produce the same results with inner joins,
because non-matching records won't be included anyway due to the where
clause. I think inner joins are easier to execute fast.

Also, you have the selection of max MOVI.FECH inside a join condition.
Might be better to put it in the outermost where clause. Like this
(UNTESTED):

select "PROC"."PROC",
"PROC"."CARP",
"PROC"."ACTO",
"PROC"."DEMA",
"PROC"."OBSE",
"TPRO"."DSCR" "D_TPRO",
m."FECH",
m."HORA",
m."DSCR" "D_MOVI"
from "PROC"
left outer join "TPRO" on "PROC"."TPRO" = "TPRO"."TPRO"
left outer join "MOVI" m
on "PROC"."PROC" = m."PROC"
and m."TIPO" = '1'
and m."HECH" = 'P'
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')
)
and (m."PROC" is null
or m."FECH" = (
select max(msub."FECH")
from "MOVI" msub
where msub."PROC" = m."PROC"
and msub."TIPO" = '1'
and msub."HECH" = 'P'
))
order by lower("PROC"."ACTO"),
"PROC"."PROC"

Furthermore, why the subquery for PROC.PROC in...?
Can't that be achieved by inner joins at the outmost level? Like this
(UNTESTED):

select "PROC"."PROC",
"PROC"."CARP",
"PROC"."ACTO",
"PROC"."DEMA",
"PROC"."OBSE",
"TPRO"."DSCR" "D_TPRO",
m."FECH",
m."HORA",
m."DSCR" "D_MOVI"
from "PROC"
left outer join "TPRO" on "PROC"."TPRO" = "TPRO"."TPRO"
left outer join "MOVI" m
on "PROC"."PROC" = m."PROC"
and m."TIPO" = '1'
and m."HECH" = 'P'
inner join "OJUD" on "PROC"."OJUD" = "OJUD"."OJUD"
inner join "ZONA" on "OJUD"."ZONA" = "ZONA"."ZONA"
where "ZONA"."ORDE" between '000000550' and '000000550'
and (m."PROC" is null
or m."FECH" = (
select max(msub."FECH")
from "MOVI" msub
where msub."PROC" = m."PROC"
and msub."TIPO" = '1'
and msub."HECH" = 'P'
))
order by lower("PROC"."ACTO"),
"PROC"."PROC"

I might be missing vital aspects here, but I'm sure you'll spot that if
that's the case...

Good luck!
Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64