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: