[firebird-support] Re: Trace cause of performance drop
Firebird version is: WI-V6.3.2.26540 Firebird 2.5 I am not very familiar with working with the Trace API at all at this point. So this will be a later task, should I not be able to locate the problem here. Unless you have a suggestion here? Michael
Re: [firebird-support] Re: Trace cause of performance drop
On 16 Dec 2014 00:30:18 -0800, michael.vilhelm...@microcom.dk [firebird-support] firebird-support@yahoogroups.com wrote: Firebird version is: WI-V6.3.2.26540 Firebird 2.5 That is not actually a Firebird version; that is a version reported through an old API call for Interbase compatibility. However build number 26540 suggests it is Firebird 2.5.2. I am not very familiar with working with the Trace API at all at this point. So this will be a later task, should I not be able to locate the problem here. Unless you have a suggestion here? My first suggestion would be to upgrade to 2.5.3 Update 1 and backup and restore your database. Mark
Re: [firebird-support] Re: Trace cause of performance drop
I stand corrected :) Version is 2.5.2.26540. We have planned an update to the newest Firebird version december 25. This database runs live and due to the christmas shopping users are online from 6 to 23.30.And the database must not be unavailable. So - we will wait. Backup / Restore has been done recently, and we have another planed medio january. Sinse the database is almost 100Gb this takes 85 minutes to dump and almost 480 minutes to restore. So we have to have a bigger window as we have right now. This customer has 3 shops in Copenhagen airport. Therefore they are online every day from 6 to at least 22. Mivi
Re: [firebird-support] Re: Trace cause of performance drop
2014-12-16 9:39 GMT+01:00 Mark Rotteveel m...@lawinegevaar.nl [firebird-support] firebird-support@yahoogroups.com: My first suggestion would be to upgrade to 2.5.3 Update 1 and backup and restore your database. Why is needed to backup + restore the database when upgrade to 2.5.3. As I know is only necessary to avoid lock conversion denied. Is there anything else?
Re: [firebird-support] Re: Trace cause of performance drop
On Tue, 16 Dec 2014 12:00:45 +0100, Jesus Garcia jeg...@gmail.com [firebird-support] firebird-support@yahoogroups.com wrote: 2014-12-16 9:39 GMT+01:00 Mark Rotteveel m...@lawinegevaar.nl [firebird-support] firebird-support@yahoogroups.com: My first suggestion would be to upgrade to 2.5.3 Update 1 and backup and restore your database. Why is needed to backup + restore the database when upgrade to 2.5.3. As I know is only necessary to avoid lock conversion denied. Is there anything else? I don't know the history of this DB, for all I know it was created with 2.5.1 and suffers from the index bug. Also given the described problem, recreating the database (what a restore does) might solve it as well. I admit it is an easy answer to what might be a complex problem. Mark
[firebird-support] Re: FB 2.5.3 32bit and Db size limits test
no is not a problem of memory usage. If i close the connection after 1 insert , then reopen the DB and keep inserting i do not have the memory error i use FIREDAC and DELPHIXE7 i am not sure if is a question of buffering on the FIREDAC libraries i've opened a ticket to the FIREDAC support to check for it it seems that also DBEXPRESS drivers have the same issue if you are making a continuos insert (around 1 records) this is not a real application i wanted only to check sizes i have also another issue if i try to install FB64 on a W2008 server i have a big performance issue it is a DC controller, and i know all the story about it so i have insalled the DB in a separated virtual disk where is already installed also Exchange i've tried to install both Classic, and superserver but to save a blob with a 10 mb of image in a simple table i need around 9 seconds, that seems too much
Re: [firebird-support] Performance optimation?
Hello, I save values in some tables (simpler description) First a Table who saved the timestamp of the mensuration Table A timestamps ID primary key TS timestamp Second a Table with the measured data (25 records/measured sensors will be saved every 10 Minutes, one record in Table A, 25 in Table B) Table B mensuration ID primary key ID_counter integer of item to measure ID_Timestamp foreign key of Table A Value (double precision) Now I would like to make an analysis. At the time, I do this: for select cast(ts as date) as mz from tablea where ts = “criteria from” and ts “criteria to” group by mz) into :messzeit do begin f_messwert = null; MESSWERTE = ''; for select a.id_counter, sum(a.value) from tableb a left join tablea b on a.id_timestamp = b.id where cast(b.ts as date) = :messzeit group by a.id_counter into :i_zae, :f_messwert do begin if(f_messwert is null) then f_messwert = 0; MESSWERTE = MESSWERTE || cast(:i_zae as varchar(4)) || '=' || cast(:f_messwert as varchar(8)) || ';'; end suspend; end The Result is one returned record for each day (day, conter 1 = 123; counter 2 = 222;…) It takes a long time but I must integrate the tablea on the second part of the statement. How can I optimize this in firebird? One thing to notice, Olaf, is that where cast(b.ts as date) will never use an index (if you have an index on TS). I would suggest changing that part of the query to something like: where cast(b.ts as date) = :messzeit and b.ts between :messzeit and :messzeit + 1 The first line limits the result to what you want, the second uses an index (if you have one). Maybe you could replace your two 'for select's with one, like this: begin messzeit2 = null; for select cast(a.ts as date), b.id_counter, sum(b.value) from tableb b join tablea a on b.id_timestamp = a.id where a.ts = “criteria from” and a.ts “criteria to” group by 1, 2 into :messzeit, :i_zae, :f_messwert do begin if (messzeit messzeit2) then begin messzeit2 = messzeit; f_messwert = null; MESSWERTE = ''; end if(f_messwert is null) then f_messwert = 0; MESSWERTE = MESSWERTE || cast(:i_zae as varchar(4)) || '=' || cast(:f_messwert as varchar(8)) || ';'; end suspend; end Does this work, and is it any faster? If this is a new project that should end up with a database lasting for quite some time, I would recommend you to reconsider your primary key. The reason being that primary keys ought to never have any business meaning (doesn't really matter if it is an integer, GUID or whatever). If you are certain that requirements will never change, then fine, but if things later could change so that there could be several records with the same timestamp, then this is something that is considerably simpler to fix if the timestamp is not a primary key. HTH, Set ++ 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: [firebird-support] Performance optimation?
SETsetknfs One thing to notice, Olaf, is that where cast(b.ts as SETsetknfs date) will never use an index (if you have an index on SETsetknfs TS). Just a note: afair, it can use an index if you have the index key defined also as cast(b.ts as date). Carlos Firebird Performance in Detail - http://videos.firebirddevelopersday.com www.firebirdnews.org - www.FireBase.com.br Hello, I save values in some tables (simpler description) First a Table who saved the timestamp of the mensuration Table A timestamps ID primary key TS timestamp Second a Table with the measured data (25 records/measured sensors will be saved every 10 Minutes, one record in Table A, 25 in Table B) Table B mensuration ID primary key ID_counter integer of item to measure ID_Timestamp foreign key of Table A Value (double precision) Now I would like to make an analysis. At the time, I do this: for select cast(ts as date) as mz from tablea where ts = “criteria from” and ts “criteria to” group by mz) into :messzeit do begin f_messwert = null; MESSWERTE = ''; for select a.id_counter, sum(a.value) from tableb a left join tablea b on a.id_timestamp = b.id where cast(b.ts as date) = :messzeit group by a.id_counter into :i_zae, :f_messwert do begin if(f_messwert is null) then f_messwert = 0; MESSWERTE = MESSWERTE || cast(:i_zae as varchar(4)) || '=' || cast(:f_messwert as varchar(8)) || ';'; end suspend; end The Result is one returned record for each day (day, conter 1 = 123; counter 2 = 222;…) It takes a long time but I must integrate the tablea on the second part of the statement. How can I optimize this in firebird? SETsetknfs One thing to notice, Olaf, is that where cast(b.ts as SETsetknfs date) will never use an index (if you have an index on SETsetknfs TS). I would suggest changing that part of the query to something like: ++ 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/
Odp: [firebird-support] Re: FB 2.5.3 32bit and Db size limits test
Hi, Can you provide source code with this insert action / also dfm I have Xe7 and i can test this About FB on DC this is one hell with Exchange. And it is much bigger problem if you have older version of FB then 2.5.3 (may be 2.5.2 fix i do not remember) on Win2008 with bigger then 4GB RAM Regards, Karol Bieniaszewski - Reply message - Od: af_12...@yahoo.com [firebird-support] firebird-support@yahoogroups.com Do: firebird-support@yahoogroups.com Temat: [firebird-support] Re: FB 2.5.3 32bit and Db size limits test Data: wt., gru 16, 2014 12:27 no is not a problem of memory usage.If i close the connection after 1 insert , then reopen the DB and keep inserting i do not have the memory error i use FIREDAC and DELPHIXE7 i am not sure if is a question of buffering on the FIREDAC librariesi've opened a ticket to the FIREDAC support to check for it it seems that also DBEXPRESS drivers have the same issue if you are making a continuos insert (around 1 records)this is not a real application i wanted only to check sizes i have also another issue if i try to install FB64 on a W2008 server i have a big performance issueit is a DC controller, and i know all the story about it so i have insalled the DB in a separated virtual disk where is already installed also Exchangei've tried to install both Classic, and superserver but to save a blob with a 10 mb of image in a simple table i need around 9 seconds, that seems too much
Re: [firebird-support] Re: performance of subselect with group by
Hello, Thanks for the answer. How can I optimize if I want to use a DML command in conjunction with a subselect, e.g. delete from test where Id in ( select min(t.Id) FROM test t group by t.reference, t.key having count(*) 1 ) am Dienstag, 16. Dezember 2014 um 08:27 schrieben Sie: 15.12.2014 23:27, bjoern.rei...@fau.de wrote: select min(t.Id) FROM test t group by t.reference, t.key having count(*) 1 costs 1000 Non indexed reads select * from test where Id in ( select min(t.Id) FROM test t group by t.reference, t.key having count(*) 1 ) costs 1001000 non indexed reads Why? IN predicate is always evaluated for the every row, because internally it's transformed into a correlated EXISTS equivalent: select * from test where exists ( select * FROM test t group by t.reference, t.key having count(*) 1 and min(t.Id) = ::id ) Dmitry ++ 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/ Björn ++ 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/
[firebird-support] Re: performance of subselect with group by
Hello, Thanks for the answer. How can I optimize if I want to use a DML command in conjunction with a subselect, e.g. delete from test where Id in ( select min(t.Id) FROM test t group by t.reference, t.key having count(*) 1 ) Hi Björn! I don't think there is any simple way to make a delete with a subselect as the only part of a where clause perform great on largish tables. That is, using EXECUTE BLOCK (which doesn't exist on older Firebird versions) should perform OK: execute block as declare variable id integer; begin for select min(t.Id) FROM test t group by t.reference, t.key having count(*) 1 into :id do delete from test where Id = :id; end HTH, Set
Re: [firebird-support] Re: performance of subselect with group by
Hello, well, that's not the answer I wanted to get, but I've to deal with that fact. I've to check wether EXECUTE STATEMENT can execute EXECUTE BLOCKs, as the DELETE Statement is build dynamically in a proc. Hi Björn! I don't think there is any simple way to make a delete with a subselect as the only part of a where clause perform great on largish tables. That is, using EXECUTE BLOCK (which doesn't exist on older Firebird versions) should perform OK: execute block as declare variable id integer; begin for select min(t.Id) FROM test t group by t.reference, t.key having count(*) 1 into :id do delete from test where Id = :id; end HTH, Set Björn -- Björn Reimer - RRZE ++ 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/
[firebird-support] Re: performance of subselect with group by
I don't think there is any simple way to make a delete with a subselect as the only part of a where clause perform great on largish tables. That is, using EXECUTE BLOCK (which doesn't exist on older Firebird versions) should perform OK: execute block as declare variable id integer; begin for select min(t.Id) FROM test t group by t.reference, t.key having count(*) 1 into :id do delete from test where Id = :id; end HTH, Set well, that's not the answer I wanted to get, but I've to deal with that fact. I've to check wether EXECUTE STATEMENT can execute EXECUTE BLOCKs, as the DELETE Statement is build dynamically in a proc. Interesting, Björn, I'd expect execute block to work wherever a query could be used, but hadn't tried it inside EXECUTE STATEMENT until you said you would have to try. However, it made me curious, so I wrote: execute block returns(myanswer varchar(32)) as declare variable es varchar(500); begin es = 'execute block returns(ma varchar(32)) as ' || ' declare variable es2 varchar(500); ' || ' begin ' || ' es2 = ''execute block returns(ma2 varchar(32)) as ' || ' begin ' || ' select Hooray from rdb$database into ma2; ' || ' suspend; '|| ' end''; ' || ' execute statement es2 into :ma; ' || ' suspend; ' || ' end'; execute statement es into :myanswer; suspend; end just to see if it worked. It actually returned Hooray, so yes, EXECUTE STATEMENT can execute EXECUTE BLOCK and they can even be nested within each other! Set
Re: [firebird-support] Re: performance of subselect with group by
Well done Set Greetings. Walter. On Tue, Dec 16, 2014 at 4:30 PM, Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support] firebird-support@yahoogroups.com wrote: I don't think there is any simple way to make a delete with a subselect as the only part of a where clause perform great on largish tables. That is, using EXECUTE BLOCK (which doesn't exist on older Firebird versions) should perform OK: execute block as declare variable id integer; begin for select min(t.Id) FROM test t group by t.reference, t.key having count(*) 1 into :id do delete from test where Id = :id; end HTH, Set well, that's not the answer I wanted to get, but I've to deal with that fact. I've to check wether EXECUTE STATEMENT can execute EXECUTE BLOCKs, as the DELETE Statement is build dynamically in a proc. Interesting, Björn, I'd expect execute block to work wherever a query could be used, but hadn't tried it inside EXECUTE STATEMENT until you said you would have to try. However, it made me curious, so I wrote: execute block returns(myanswer varchar(32)) as declare variable es varchar(500); begin es = 'execute block returns(ma varchar(32)) as ' || ' declare variable es2 varchar(500); ' || ' begin ' || ' es2 = ''execute block returns(ma2 varchar(32)) as ' || ' begin ' || ' select Hooray from rdb$database into ma2; ' || ' suspend; '|| ' end''; ' || ' execute statement es2 into :ma; ' || ' suspend; ' || ' end'; execute statement es into :myanswer; suspend; end just to see if it worked. It actually returned Hooray, so yes, EXECUTE STATEMENT can execute EXECUTE BLOCK and they can even be nested within each other! Set
