Josef Gschwendtner wrote:

> Dear Firebird users,
>
> does anybody have an idea how to solve the following problem in a clever
> fashion?
>
> Our Situation:
> ==============
> We have a database with to tables (amongst others).
> In table1 (MDP) are several thousand "machine-datapoints" (e.g
> machine-speed or temperature)
> For each of these datapoints we get data (values) (e.g. 5 m/s) which is
> stored in table2 (MD).
>
> Some datapoints are sending a value each second, some only once a day.
> Therefore table MD has many million records. Each record has a foreign
> key MDP, the value and the timestamp when the value has been measured.
> On the timestamp-field in MD is an ascending index.
>
> Our problem:
> ============
> We need to view the (active) values/states of all datapoints at a
> certain time, which is given by the user.
>
> Therefore the challenge is, to find out (as fast as possible) the last
> stored value/state of each datapoint before the given timestamp.
>
>
> Thank you for sharing your knowledge.
> Josef Gschwendtner
>
>
>

Josef,

You could use an SP or sub-selects and test for performance.

I don't know your table structures, but I think you will get the idea.

select
MDP.Register,
(select first 1 TimeStamp from MD where MD.MDP_ID = MDP.MDP_ID and
MD.TimeStamp <= :UserSuppliedValue order by TimeStamp desc),
(select first 1 Value from MD where MD.MDP_ID = MDP.MDP_ID and
MD.TimeStamp <= :UserSuppliedValue order by TimeStamp desc)
from
MDP

You should have an *descending* index on MD.TimeStamp

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br