Here's a question that I've been grappling with recently: What database to use for storing EOD data? I currently have over a couple of gigs of data, spread all around the place, and am thinking of integrating it all into one database. Am considering SQL Server or MySQL and the ever trusty text file (with a few added frills of course). Can't think of any other alternatives. Would anyone have any suggestions?

That is pretty much what my initial tests are revealing as well. Much faster to use text files (indexed so far by name) rather than MySQL. I haven't got around to testing SQL Server yet, but don't expect much better.

And I don't want to even imagine the speed what it'd be like on 7500 stocks

Here's a question that I've been grappling with recently: What database to use for storing EOD data? I currently have over a couple of gigs of data, spread all around the place, and am thinking of integrating it all into one database. Am considering SQL Server or MySQL and the ever trusty text file (with a few added frills of course). Can't think of any other alternatives. Would anyone have any suggestions?

The answer depends on many factors. The four most important factors, in my opinion, are:

1. Which operating system do you want to use to run your database on?

If your answer is a Microsoft product, then you are rather limited in your choices compared to Unix derivatives. I personally prefer FreeBSD or Gentoo Linux. Many of the RDBMS (Relational DataBase Management Systems) available for free work better with Unix. Some of them will only run on Unix. Most large financial development shops will use a Unix type operating system to run their databases on.

2. What is your level of computer usage/programming skills?

Many Unix type operating systems are free. However the learning curve is much higher than an Microsoft alternative. Also, using a relational database requires some knowledge of relational algebra concepts and computer skill. The potential for benefit of using an RDBMS is directly related to your (or your team's) computer skill and/or savvy.

3. What will you do with the data and what kind of performance do you need?

If your needs are simplistic, such as storing all records sequentially by timestamp in one table with no relations between multiple tables, then binary files are a better route. Actually, most RDBMS software use binary files (berkley db) as their backends. Even if you use a binary file, you will need to know how to set your own indexes and write your own code to retrieve the data in the most efficient manner. All of this functionality (and more) is what an RDBMS provides.

The power of a relational database fully utilized when you have different tables of data that are related to each other. These relations are defined by the schema (something you create). If designed correctly, it can be a very powerful and productive tool.

4. How much do you want to spend?

If you are willing and able to actually spend money on a propietary rdbms system such as Sybase, Oracle or DB2 you should already know all of the things I have mentioned. For the most part, these propietary systems will cost you alot. They do provide some customized features that are only found in each product. But for the most part, MySQL and PostgreSQL will suit most developer's needs. Best of all, they are free. All will require time and effort to integrate into your application.

I am also contemplating what to use. I have used both MySQL and PostgreSQL in production environments. If I were only going to have simple tables with no relations, I would choose MySQL. If I'm going to have complex relations and rules, I would choose PostgreSQL. MySQL is known for it's speed. PostgreSQL is known for it's features. Here are the links to both systems:

Last but not least, it might be more cost effective for you to purchase a "Trading System Application" that will store the data for you and give you tools to manipulate it. Designing your own application from scratch is not a task to be taken lightly. You should only do this if:

1. You have reached the limits of the trading system apps available on the market.
2. You get personal enjoyment out of the challenge of doing it yourself.
3. You're getting paid by someone else to do it.

Yes this is probably true for strict reads from simple tables (assuming both MySQL table is indexed and using binary tree index in file). However that's about the extent of what you can do with a "text" or binary file. Otherwise all banks, trading firms, and amazon would use text or binary files.

Most trading IT shops that I have consulted for use RDBMS to store their data. With RDBMS, you gain the ability to add things like procedures to update some other table value (such as a moving average). This way you don't have to incur the cost of computing that value ever again. It all depends on how complex your system is. I think you will be hard pressed to find shops/teams that use binary files as the main method of storing their Terabytes of data. Look at how fast Amazon.com or Google are.... All of that is database driven.