efficient way for storing query results?

Question

I have a requirement like running 'n' numbers of select queries at fixed time intervals and storing that data. These results need to be pulled later upon a client's demand.

My question is:

• 1) Is it okay to store it as csv files? Or could you suggest another format?
• 2) Or, should it be stored as clob variable in a db?

Please suggest any compression techniques to store these query results; also, is it possible to store only revisions of previous resultsets instead of storing the whole resultset?

note:

• The minimum time interval is hourly.
• The number of queries (n) will be varying (currently 10 to 200 queries.)
• The resultset size of each query is also varying (say 10 to 1,000,000 but mostly around 10k.)
• The resultset data fetched between each time intervals doesn't differ much. (The row value will not be updated frequently.)

I am new to computer science and programming and also not very aware about storage or db designs.

Answer 1

I think you need to store the data in a database. SQL database can serve you the best. Regarding to storing the data in fixed interval of time, you just need to make effect of the change in the data set instead of storing the whole data again and again. I don't know what is your requirement and how much infrastructure you can afford. If you have such huge queries, I recommend you to work in Distributed System. Use NOSQL database for better performance.

Answer 2

It sounds like you should be building a data warehouse.

Answer 3

Performance-wise I suppose it would be better to have a table which purpose is to store the query results.