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

>>But this is not the problem, because 2.5.0 does this query very fast, with no >>special cache settings.Yes, i say the same in the answer ;-)"And about >>changed plan, it is not releated to this setting but this only increase >>visibility (of the issue) in your small cache settings"Regards,Karo

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

29.05.2019 13:37, danysch...@yahoo.com [firebird-support] wrote: > The very important diference is the plan. 2.5.8  is not using MOVI_FECH > index, so it must > read more than one millon records to analize ("MOVI"."FECH" between > '20190301' and > '20190412'). Remove "DISTINCT" from subquer

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

I added this: http://tracker.firebirdsql.org/browse/CORE-6070 http://tracker.firebirdsql.org/browse/CORE-6070 Thank you.

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

Hi Karol; I was investigating what is doing the query optimizer about the MOVI_FECH index. I found that using "MOVI.FECH between ...20190301 and 20190412" does not use MOVI_FECH index. I also found that if I do "MOVI.FECH >= 20190301 and MOVI.FECH >= 20190412" does not use MOVI_FECH

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

Hi Karol; But this is not the problem, because 2.5.0 does this query very fast, with no special cache settings. The very important diference is the plan. 2.5.8 is not using MOVI_FECH index, so it must read more than one millon records to analize ("MOVI"."FECH" between '20190301' and '2019041

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

>>I enabled in firebird.conf the parameter DefaultDbCachePages = 2048, >>but >>after that the performace was 1 minute slower.Yes, i am talking about this >>param you can also set it indyvidually for database. And your value is small. >>If your db pagesize is 16KB then cache in your case is only

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

Hi; I think there is not a problem in my query, but there is a problem in the query optimiezar; Under 2.5.0 the query optimizer builds a plan using MOVI_FECH: PLAN SORT ((MOVI INDEX (MOVI_PROC, MOVI_FECH))) But under 2.5.8 the query optimizer does not use MOVI_FECH PLAN SOR

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

Thank you but, I don't understand where is the relation.

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

Hi Andras; Thank you!. Problem is, my application can query the database using a combination of more than 100 conditions from 18 diferent tables. Users can combine conditions in thousands of ways, so I can't build the perfect query for each case. Also, exaclty the same query system have t

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

28.05.2019 22:36, danysch...@yahoo.com [firebird-support] wrote: > I don't think this problem is about parameters, there is something that > changed a lot > between 2.5.0 and 2.5.8 that is making this queries to go that slow. http://tracker.firebirdsql.org/browse/CORE-4690 -- WBR, SD.

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

One of the first things Firebird taught me (well, I don't remember whether if it was Firebird 0.9.4 or InterBase 5.6), was to never use IN(). At that time, the optimizer didn't check if the subselect was correlated(*) or not, and hence executed the subselect for every possible row in the outer

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

Hi Karol; I use zip like you. Then I execute like SuperClassic. My application uses embeded, which is SuperClassic too. If I execute the query from my application (embeded FB) I get exactly same performance than when I do it from FlameRobin. I enabled in firebird.conf the parameter Default

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

#x27;20190301' and '20190412') and "MOVI"."MIEM" = '15JMS45D7A' and "MOVI"."HECH" = 'N' order by "PROC") select pro

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

Ok you use FB2.5. What is your server type superserver, classic or superclassic. I never use installer only zip kits then i do not know which server type is default.If you use superserver then default values are super-small and you do not benefit from cache. Increase page buffers. Without this,

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

Hi Karol; Please note that I am comparing 2.5.0 vs. 2.5.8; so I am not talking about 3.x; but I also tried 3.x and it is so slow two. I never modified PagePuffers and PageSize. I always use all the Firebired defaults. Thank you.

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

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

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","P

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

HiShow the query plan for both server versionsRegards,Karol Bieniaszewski null

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

Well ... thank you but ... it was not a problem in older 2.5.x versions. But I also tried with: select "PROC" from "PROC" where exists (select 1 from But it also needs 24 minutes. I know it is better a join, but in my queries it is possible to check "IN" up to 18 other sub-queries

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

24.05.2019 21:25, 'Dany Schaer' danysch...@yahoo.com [firebird-support] wrote: > What can be the problem with this query undewr 2.5.8? "IN" predicate is in trouble. Get rid of it if possible. -- WBR, SD. +++

[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! I want to share this query with you to find out what can be the problema now with this query example: The test database has PROC with 46k records and MOVI with 1200k records. select "PROC" from "PROC" where "PROC" in ( select distinct "PROC" from