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
Author Omacht András
Post date 2019-05-28T09:34:27Z

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: danyschaer@...