Hi!
I sent this e-mail two times at saturday, but yahoo didn’t deliver it.
I try it again:
Hi Dany!
Try with CTE:
with procs as (
select distinct "PROC"
from "MOVI"
where "MOVI"."TIPO" in ('1','A','B')
and ("MOVI"."FECH" between
'20190301' and '20190412')
and "MOVI"."MIEM" = '15JMS45D7A'
and "MOVI"."HECH" = 'N' order by
"PROC")
select proc.proc
from procs
left outer join proc on proc.proc = procs.proc
where proc.proc is not null
Maybe it will be fast with inner join too:
with procs as (
select distinct "PROC"
from "MOVI"
where "MOVI"."TIPO" in ('1','A','B')
and ("MOVI"."FECH" between
'20190301' and '20190412')
and "MOVI"."MIEM" = '15JMS45D7A'
and "MOVI"."HECH" = 'N' order by
"PROC")
select proc.proc
from procs
inner join proc on proc.proc = procs.proc
András
From: firebird-support@yahoogroups.com <firebird-support@yahoogroups.com>
Sent: Monday, May 27, 2019 4:25 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Query that was very fast under the oldest 2.5
versions are very slow under 2.5.8 or 2.5.9
Hello Karol; here you have both query plans, excecuted over same FDB file, on
the same machine:
-----------------------------------------------------
FIREBIRD 2.5.0:
-----------------------------------------------------
Preparing query: select
"PROC"."PROC","PROC"."CARP","PROC"."ACTO","PROC"."DEMA","PROC"."OBSE","TPRO"."DSCR"
as
"D_TPRO","PROC"."OJUD","PROC"."INST","PROC"."EXP1","PROC"."EXP2","PROC"."EXP3","PROC"."EXP4"
from "PROC" left outer join "TPRO" on "PROC"."TPRO"="TPRO"."TPRO" left outer
join "OJUD" o1 on "PROC"."OJUD"=o1."OJUD" left outer join "ZONA& quot; o2 on
o1."ZONA"=o2."ZONA" where ((("PROC"."PROC" in (select distinct "PROC" from
"MOVI" where "MOVI"."TIPO" in ('1','A','B') and ("MOVI"."FECH" between
'20190301' and '20190412') and "MOVI"."MIEM" = '15JMS45D7A' and "MOVI"."HECH" =
'N' order by "PROC")))) order by
o2."ORDE",o1."ORDE",lower("PROC"."ACTO"),lower("PROC"."DEMA")
Prepare time: 1.498s
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)
Fie ld #07: PROC.OJUD Alias:OJUD Type:STRING(10)
Field #08: PROC.INST Alias:INST Type:STRING(1)
Field #09: PROC.EXP1 Alias:EXP1 Type:STRING(18)
Field #10: PROC.EXP2 Alias:EXP2 Type:STRING(18)
Field #11: PROC.EXP3 Alias:EXP3 Type:STRING(18)
Field #12: PROC.EXP4 Alias:EXP4 Type:STRING(18)
PLAN SORT ((MOVI INDEX (MOVI_PROC, MOVI_FECH)))
PLAN SORT (JOIN (JOIN (JOIN (PROC NATURAL, TPRO INDEX (TPRO_PK)), O1 INDEX
(OJUD_PK)), O2 INDEX (ZONA_PK)))
Executing...
Done.
3051054 fetches, 8 marks, 17180 reads, 8 writes.
0 inserts, 0 updates, 0 deletes, 148293 index, 52648 seq.
Delta memory: 79112 bytes.
Total execution time: 0:01:34 (hh:mm:ss)
Script execution finished.
-----------------------------------------------------
FIREBIRD 2.5.8:
---------------------------------------------- -------
Preparing query: select
"PROC"."PROC","PROC"."CARP","PROC"."ACTO","PROC"."DEMA","PROC"."OBSE","TPRO"."DSCR"
as
"D_TPRO","PROC"."OJUD","PROC"."INST","PROC"."EXP1","PROC"."EXP2","PROC"."EXP3","PROC"."EXP4"
from "PROC" left outer join "TPRO" on "PROC"."TPRO"="TPRO"."TPRO" left outer
join "OJUD" o1 on "PROC"."OJUD"=o1."OJUD" left outer join "ZONA" o2 on
o1."ZONA"=o2."ZONA" where ((("PROC"."PROC" in (select distinct "PROC" from
"MOVI" where "MOVI"."TIPO" in ('1','A','B') and ("MOVI".&q uot;FECH" between
'20190301' and '20190412') and "MOVI"."MIEM" = '15JMS45D7A' and "MOVI"."HECH" =
'N' order by "PROC")))) order by
o2."ORDE",o1."ORDE",lower("PROC"."ACTO"),lower("PROC"."DEMA")
Prepare time: 0.031s
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: PROC.OJUD Alias:OJUD Type:STRING(10)
Field #08: PROC.INST Alias:INST Type:STRING(1)
Field #09: PROC.EXP1 Alias:EXP1 Type:STRING(18)
Field #10: PROC.EXP2 Alias:EXP2 Type:STRING(18)
Field #11: PROC.EXP3 Alias:EXP3 Type:STRING(18)
Field #12: PROC.EXP4 Alias:EXP4 Type:STRING(18)
PLAN SORT ((MOVI INDEX (MOVI_PROC)))
PLAN SORT (JOIN (JOIN (JOIN (PROC NATURAL, TPRO INDEX (TPRO_PK)), O1 INDEX
(OJUD_PK)), O2 INDEX (ZONA_PK)))
Executing...
Done.
2458464 fetches, 8 marks, 843116 reads, 8 writes.
0 inserts, 0 updates, 0 deletes, 1076748 index, 53232 seq.
Delta memory: 221664 bytes.
Total execution time: 0:20:40 (hh:mm:ss)
Script execution finish
Thank you!!!
________________________________
Posted by: danysch...@yahoo.com
