Windows Databases

Discussion in 'Data Sets and Feeds' started by Craig66, Jul 22, 2008.

  1. Craig66

    Craig66

    Does anybody else use a windows based database to store quote data, if so which database do you use? I looked at the MS desktop SQL engine, but it has the 4 GIG limit. I also had a look at Postgres, but the C++ API is fairly opaque and does not seem well supported on Windows.

    Has anybody used MySQL? what are people doing in general?

    Any advise is appreciated.
     
  2. logis

    logis

    from my experience with mysql there is a problem storing the quotes in real time (more than 10,000 msg per sec.)
    so now i am not saving to any d.b. just to disk as txt files
     
  3. chvid

    chvid

    I use mysql but I am on a java platform and I don't do high frequency.

    Of the big databases (Oracle, Postgres and mysql) mysql, in my experience, is the fastest and the one that is the easiest to use.

    If performance is an issue - the best route may be to build your own database on top of random access binary files.

    Probably not as nasty as it sounds.

    But good luck with that :)
     
  4. Your best bet is to save into your own stream with Bid, Ask and last trade. You will derive your OHLC bars (whatever timeframe you need) of the tick data in the files. Then based your studies of the bars (RSI, Stoch, ....).

    You more likely need one file per equity per day (ie: QuoteFile.GOOG.2008.07.22.txt). This way you can specify how many days you want to load, etc...

    The storage can be huge if you are storing a lot of days and equities. The code that laods the files more likely be C++ if you want to be real fast. Ifyou have lots of data you probably will want a 64bit server.

    I do not know your full requirements but I have done something similar before for histrical analysis realtime studies.....
     
  5. promagma

    promagma

    I use mySQL with Java and it is fast. It is faster than MS SQL server .... I tested it. But if you have to write a complicated query, it is much harder in mySQL. You can throw anything at the MS SQL Server, and it will figure it out run things optimally. The mySQL query parser is much more picky, a real pain in the neck.
     
  6. Craig66

    Craig66

    I had a look at MySQL last night, I didn't realize how far it had come, the tools are much easier to use than Postgres.
    I'll give MySQL a shot, thanks for all the replies.
     
  7. MySQL has two modes: one is very fast (ISAM), the other is very slow (InnoDB). The slow one supports transactions, logs and other staff you don't need for local quotes storage, so make sure you go ISAM route.

    All this is good for EOD and other low-frequency data. When we start dealing with intraday quotes, regular databases are completely useless. I don't have $$$,$$$ for a specialized financial database, so I store quotes as plain files, utilizing file system's directory structure for indexing.

    When I make my first $100K trading, I'd buy a (future) eigth-core xeon x4 board, load it with 256GB of RAM, and do all processing in main memory ... dreams sweet dreams ...
     
  8. Craig66

    Craig66

    Cheers for that, I have switched it over to ISAM. One annoying thing I have found with MySQL is the time stamp only goes down to 1 second resolution, so you have to construct some type of composite time element, oh well, it is free.
     
  9. promagma

    promagma

    Yes, a flat file will give top speed if all you need to do is store data and read it back sequentially. But ... a database is more versatile and will run just as fast, as long as your tables have the correct indexes, and you know how to do a streaming recordset to read the data.

    If you use mySQL, check out HeidiSQL. It is a great front end tool.