>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