-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

Sometimes, there is the normalization of the database a disadvantage. I should save the timestamp and data in one table, so I save the time in one and the ID of the time in the second table with data, because for each time I save 20+ records (values).

I need for each time (per hour, per day, per month) only one row returned and in the data-field all items like (item1:sumofitem1;item2:sumofitem2;…) Very fast is the following statement:

for select extract (day from a.messzeit) || '.' || extract (month from a.messzeit) || '.' || extract(year from a.messzeit) || ' ' ||

extract(hour from a.messzeit) || ':00:00' as mz, b.id_zaehler, sum(b.wert) from te_messzeiten a left join te_werte b on a.id =        b.id_messzeit

where a.messzeit >= :zr_von and a.messzeit < :zr_von + 1

group by mz, b.id_zaehler

into :messzeit, :messwerte, :messwerte2 do

suspend;

(zr_von is the condition (timestamp))

But in this case I get for each item and each time a record returned. This is not what I need.

What solution can I use for a faster result?

(It should be, vor example group by day:

2014-01-01 01:00:00 01:100,0;02:199,1;03:222,9 (grouped for each hour for the day for each item)
2014-01-01 02:00:00 01:300,0;02:198,1;03:211,7

Source:

Table a

ID;Timestamp
1; 2014-01-01 01:00:00
2; 2014-01-01 01:10:00
3; 2014-01-01 01:20:00

Table b

ID;ID_TableA;Item;Value

1;1;10;0.32
2;1;11;0.44
3;1;12;1.22
4;2;10;0.39
5;2;11;0.45
6;2;12;1.21

Thank you

Posted by: Mark Rotteveel <mark@...>

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit Your Group