ODP: [firebird-support] Query that was very fast under the oldest 2.5 versions are very slow under 2.5.8 or 2.5.9

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Hi

Do you use FB3 Super Server or Classic?
What value of PagePuffers and PageSize?
I see big reads but also big indexes reads but fetches are lower.

There is also one plan difference 
MOVI INDEX (MOVI_PROC) vs MOVI INDEX (MOVI_PROC, MOVI_FECH)

But i am intersted how this looks like at second run with big cache


Regards,
Karol Bieniaszewski

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

[danysch...@yahoo.com [firebird-support]]

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" 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)
 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, 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"."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!!!

Re: [firebird-support] Re: Drop table safe when other users are active?

[Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]]

Den 2019-05-26 kl. 13:28, skrev hv...@users.sourceforge.net 

[firebird-support]:

>

> > A few years ago we suffered Firebird corruption and got it fixed with

> > the help of IB Surgeon. I was told at the time that dropping tables

> > while other users were connected was the probable cause, and that this

> > was a bug in FIrebird that had been around for a long time. I think 

> this

> > would be for Firebird 2.5.

>

> What bug ? Is it registered at the tracker ? Do you have a test case ?

>

> > Now I'm wondering if this is a bug that is known to have been fixed

> > since then? In other words: Is it now (version 3.0.4) safe to drop

> > tables when the database is in use by multiple users?

>

> I physically can't answer if some unknown bug is fixed or not. There 

> was a lot

> of changes and refactorings during FB3 release circle. It is 

> impossible to make

> any kind of correct answer on such a question, sorry.

>

> Regards,

> Vlad

>



I realize that it's difficult. I was under the impression that this is a 

well-known problem, since it appears to be well-known to the guys at 

IBSurgeon. Apparently not. It's probably highly intermittent, 

considering I have dropped a lot of tables in non-exclusive mode through 

the years, before I got a corruption and learned about this problem. A 

test case? Yeah, well, I could try... But I think it would be better if 

the dev team were to communicate with those who know the problem a lot 

better, i.e. please talk to IBSurgeon.



Regards,

Kjell





[Non-text portions of this message have been removed]