3 dimensional table

[Freddie Leatham]

Hi,

I have a table that lists the historical prices of stocks. the table headers are simply date, stock, and price. The list is approx 100k long and is likely to grow exponentially as we cover more stocks but i think that for the moment, we need the provision for up to a million records. This table is fine and a basic query (select * from prices) will retrieve the whole table in 3 seconds. however i have another query (called holdings) that will show the number of shares held in each stock on every day listed in the datelist table. This again is fine and produces a similar number of results, however when i try to bring in the prices to the query, the query takes minutes to run.

The query i am using goes something like this:

SELECT h.date, h.stockid, h.holding, p.price FROM (embedded holding query) h, prices p
WHERE p.date = h.date AND p.stockid = h.stockid

Am i going about this the correct way or is there method to do this that will make the query run much faster. I toyed with the idea of creating an index field in the prices table that was a concatenated combination of the date and stockcode, but never go round to doing it. could this work better?

Thanks,

Freddie