Hi all,

I have a database that stores a large amount of data points in a few tables
and am having a problem getting good performance for batch reads. The
integer point table looks like this (haven't got the actual creation script
with me here):

Int_Values
(
PointID bigint,
TransID bitint,
TransTime timestamp,
PointValue int
)

I have a descending index on TransTime and PointID.

The user requirements are that around 600 points are stored with a years
worth of data, and these will store their values on change at a minimum of
every 10 seconds. There are also another thousand points but these will
generate a lot less records, (a few a day).

Worst case scenario: 600 tags X changing 6 times a minute X 60 X 24 X 365 =
1,892,160,000 records in one table.

Individual records are being retrieved using the following sql:

SELECT first 1 TRANSID, PointID FROM INT_VALUES
WHERE PointID = 1
AND TransTime < '2009-10-22 15:20:42'
ORDER BY TransTime Desc

But I want to be able to read multiple or all tags in one go and when using
Group By I find the performance drops dramatically:

SELECT MAX(TRANSID), PointID FROM INT_VALUES
WHERE PointID in (1.... etc)
AND TransTime < '2009-10-22 15:20:42'
Group By PointID

Can better indexing improve this or are the performance expectations too
high in this situation?

Kind Regards
Spike