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/
