Subject AW: [firebird-support] Performance optimation?
Author checkmail

Hello Mark,

-That is not surprising. You are executing a query and for each row you are
-executing another query.
Use joins and/or subqueries instead.


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


(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


Table a

2014-01-01 01:00:00
2014-01-01 01:10:00
2014-01-01 01:20:00

Table b



Thank you

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

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (2)


Visit 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


Visit Your Group

·         New Members 5