How to effectively store a high amount of rows in a database

Question

What's the best way to store a high amount of data in a database? I need to store values of various environmental sensors with timestamps. I have done some benchmarks with SQLCE, it works fine for a few 100,000 rows, but if it goes to the millions, the selects will get horrible slow. My actual tables:

Datapoint:[DatastreamID:int, Timestamp:datetime, Value:float]  
Datastream: [ID:int{unique index}, Uint:nvarchar, Tag:nvarchar]

If I query for Datapoints of a specific Datastream and a date range, it takes ages. Especially if I run it on a embedded WindowsCE device. And that is the main problem. On my development machine a query took's ~1sek, but on the CE device it took's ~5min

every 5min I log 20 sensors, 12 per hour * 24h * 365days = 105,120 * 20 sensors = 2,102,400(rows) per year

But it could be even more sensors!

I thought about some kind of webservice backend, but the device may not always have a connection to the internet / server.

The data must be able to display on the device itself.

How can I speed up the things? choose an other table layout, use an other database (sqlite)? At the moment I use .netcf20 and SQLCE3.5

Some advices?

Answer 1

I'm sure any relational database would suit your needs. SQL Server, Oracle, etc. The important thing is to create good indexes so that your queries are efficient. If you have to do a table scan just to find a single record, it will be slow no matter which database you use.

If you always find yourself querying for a specific DataStreamID and Timestamp value, create an index for it. That way it will do an index seek instead of a scan.