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,Karol >>Bieniaszewski null
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 subqueries. It is pointless there in any case.
--
WBR, SD.
++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-dig...@yahoogroups.com
firebird-support-fullfeatu...@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
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 index. The only expresion that use the MOVI_FECH index is when I do "MOVI.FECH = 20190412". This is the problem, I guess. I will report the case.
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 '20190412'). This is the MOVI ddl:
CREATE TABLE MOVI
(
MOVI Char(10) NOT NULL COLLATE ES_ES_AI,
PROC Char(10) NOT NULL COLLATE ES_ES_AI,
MIEM Char(10) NOT NULL COLLATE ES_ES_AI,
TIPO Char(1) NOT NULL COLLATE ES_ES_AI,
AVIS Char(3) NOT NULL COLLATE ES_ES_AI,
HECH Char(1) NOT NULL COLLATE ES_ES_AI,
FECH Char(8) NOT NULL COLLATE ES_ES_AI,
HORA Char(4) NOT NULL COLLATE ES_ES_AI,
DSCR Varchar(200) NOT NULL COLLATE ES_ES_AI,
FOJA Varchar(10) NOT NULL COLLATE ES_ES_AI,
PRES Char(1) NOT NULL COLLATE ES_ES_AI,
REPI Char(1) NOT NULL COLLATE ES_ES_AI,
OWNR Char(10) NOT NULL COLLATE ES_ES_AI,
TEXT Blob sub_type 0,
"EDIT" Char(23) NOT NULL COLLATE ES_ES_AI,
CONSTRAINT MOVI_PK PRIMARY KEY (MOVI)
);
CREATE INDEX MOVI_FECH ON MOVI (FECH,HORA);
CREATE INDEX MOVI_HECH ON MOVI (HECH);
CREATE INDEX MOVI_MIEM ON MOVI (MIEM);
CREATE INDEX MOVI_OWNR ON MOVI (OWNR);
CREATE INDEX MOVI_PRES ON MOVI (PRES);
CREATE INDEX MOVI_PROC ON MOVI (PROC);
CREATE INDEX MOVI_REPI ON MOVI (REPI);
CREATE INDEX MOVI_TIPO ON MOVI (TIPO);
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
>>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 32MB which is >>value of cache from windows 3.1 ;-) days? If yor page sise is 4KB than it is >>8MB...And about changed plan, it is not releated to this setting but this >>only increase visibility in your small cache settings.Regards,Karol >>Bieniaszewski null
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: 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. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
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 DefaultDbCachePages = 2048, but after that the performace was 1 minute slower. 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. What parameter (and how) do I have to change page buffer parameter?. Is it possible to set it in the connection parameters?. 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
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, all your queries are so slow as your harddrive. As you know, RAM is magnitude faster.Regards,Karol Bieniaszewski null
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 with big cache Regards, Karol Bieniaszewski
