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/
Re: [firebird-support] Performance optimation?
On Mon, 15 Dec 2014 16:01:27 +0100, 'checkmail' check_m...@satron.de [firebird-support] firebird-support@yahoogroups.com wrote: 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? That is not surprising. You are executing a query and for each row you are executing another query. Use joins and/or subqueries instead. Mark
