danyschaer wrote:

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

The problem resides on PROC NATURAL, from what I understood from the
query, the outer query has no where clause, so there is nthing to do
besides scan all records...

If PROC table is huge, certainly, it would take time to execute.

see you !

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