>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/
