How to optimize access of this Data ?

Question

I have a table which consists of 200 Companies Stock prices for 5 years. This is one large table which consists of Company Name, Stock Open, High, Low, Close, Date

I am now required to do some processing on the same and also let users [up to 10] access this database to fetch reports on different sets of parameters and queries.

Should I use the database as it is or do you have any suggestion to make it more optimized.

Thanks.

Answer 1

If it is really just company name with that data then it is already normalized. If there was more about the company like address, phone, etc then you would want to break that out into a separate table.

Answer 2

i'd have a table for the company, and a table for the stock prices on a given day (the open/high/low/close bits), to save duplicating the company info everywhere.

Answer 3

Pull out the name and use an integer ID. It should be faster and will tolerate name changes. The stock symbol can also be extracted to the parent table.

Answer 4

I think you need to consider the report, are they always going to be month by month, for example? if so you could create a table of aggregated data.

Otherwise i think careful indexes are your only option for performance

Answer 5

I would add an UID field and several Dimensions for Date (i.e. years table, years+months table, years+quarters table, fiscal years table, etc.).

Answer 6

Normalized and optimized are not always the same thing.

What are your users going to do with the data?

Answer 7

This is not an optimization (although you could argue that it is a normalization provided companies can change name):

CREATE TABLE company (
  id INTEGER PRIMARY KEY, -- Well, this would be a serial, but that works different in different DBMS
  name VARCHAR(256) UNIQUE
);

CREATE TABLE price (
  company_id INTEGER REFERENCES company(id) NOT NULL,
  date  TIMESTAMP NOT NULL,
  open  DECIMAL, -- Just grabbed a type here, probably not right for you.
  high  DECIMAL,
  low   DECIMAL,
  close DECIMAL,

  PRIMARY KEY(company_id, date)
);

See here for info on the key generation.

How do you handle companies changing name, by the way? By ignoring it would be the simple answer, but is it correct? :)

So, anyway if the table grows too big for good performance I would just partition it.

Answer 8

A misquote of someone:

Rules of Optimization

1. Don't do it.
2. For EXPERTS only: Don't do it yet.

If the question is "... do I leave it alone or do I make it more optimized", leave it alone until you know, by measurement, that there is a problem.

If there is a problem with the query or update of the table, then update your question with details about the query, any indexes, how frequently the table is updated/accessed, etc. You'll get all kinds of suggestions at that point.

As was mentioned earlier, as far as normalization goes, you might consider extracting Company Name to its own table if the same Company Name appears multiple times in the table.