I need some advice on the best way to design a database that's going to hold time series data. I have many different symbols I want to record data for, and for each symbol I might have 1 - 10 data points per time frame. Think of a symbol as a key with 1 - 4 dimensions. So for symbol <id1, id2, id3> I might want to store 10 different data points each time step, while symbol <id1> might need just 4 data points.

Right now, in SQL Server 2005, I store groups of related data in different tables. For the symbols, I just have one column per dimension (e.g. columns {id1, int}, {id2, int}, {id3, varchar(50)}). Then I have a datetime with the timestamp.

One thing I might do is get all the data for a particular timestamp: easy enough, just select where timestamp='___'. Another thing I might do is request a particular symbol: where id1=XX and id2=XX and id3=XX. I might also just specify a desired id1, but nothing for id2 or id3 (i.e. I want all id2's and id3's). In general, I won't specify a constraint on idY unless I've specified constraints on idX where X<Y. So, for example, I would never have a query where I just specify a constraint on id3.

The problem with this is that with tens of millions of rows in a given table, querying for a particular <id1, id2, id3> (there are about 75k unique such trinary keys) across multiple timestamps is slow. I need this query to be under 1 or 2 seconds. I tried putting a clustered index on the timestamp column, and a non-clustered index on the set of columns that compose the symbol. Doing this in a table with 200 million rows and 3 columns in the symbol, asking for every entry for a particular <id1, id2, id3> takes over 20 seconds. Am I just pushing SQL Server too far here?

In practice I want to ask for all data for a specified timestamp or all data for a range of timestamps for a specified symbol. For client apps that need this data, I had to resort to writing an app that loads relevant data from the DB into memory, and my client apps get data from this server instead of straight from the database. But after some google searching I found suggestions that SQL Server should be fine for fast queries of time series data, yet I haven't found a specific solution confirming this.

Am I doing something wrong with my indexes here? I can always add more - disk space isn't an issue - but I would like inserts/deletes (generally done as a complete dataset for a given timestamp) to take under 5 seconds. I thought about breaking up data into different tables, but then querying across symbols and/or across timestamps seems to become a huge hassle. Is there a better way to design this, or a better system altogether?