> in the mean time i have 8,5 miljon records in it
>
> this is a query i use
>
> Select
> sensorid,
> datadate,
> sum(measurevalue)
> from data
> where SensorId=153 and
> dataDate>=39125 and
> DataDate<=40524 and
> ValueIndex=1
> group by
> datadate,
> sensorid
>
> only opening takes 1,5 second. I used many indexes but none of them did get it faster
>
> ValueIndex can only be 1 or 2 or null
> SensorId 290 different sensor id's
> date 1976 diferent dates
>
> which index should i use ?

How many has ValueIndex = 1?
How many has SensorID 153?
How many have DataDate between 39125 and 40524?

If the number to one or more of these three questions are relatively low, then using an index on these fields may be benefitial for this particular query - if they're all high, then it might be that using NATURAL is the best you can get. Due to the architecture, Firebird has to look at every of the records it counts to see whether it should be counted or not (some of the records may not be visible to the transaction that is doing the counting).

> After running the query i use a while loop to get the values in
> memory this takes also 2,5 second with ibx components what is the
> beste way to do this are there faster components ?

I know nothing about ibx. With IBO it is better to use a cursor component than a query component unless there is a reason for the set to be bidirectional.

Another thing is to consider whether it is necessary to fetch all records. In some cases it is (e.g. if you're creating invoice to customers, then the invoice should of course contain information about everything they bought), in other cases it isn't (e.g. when having a search function, it is rare that people need to be able to see more than a few records at a time - at least not without executing another query).

HTH,
Set