SQL for trading...?

[skyheights]

thstart:

Thank you for sharing your interesting perspective and ideas with us. And thank you for entertaining many different ET voices. You were rarely defensive, and even less offensive. If only most ETs displayed your degree of aplomb. It sounds like your solution will rock!

 

[PocketChange]

Quote from PowerST:

The problem this is that the quantity of data thstart and I are discussing may not fit into memory. We are discussing larger scale testing that can easily exceed physical memory.

thstart already discussed SQLite in previous posts to this thread. He said:

The In Memory limits you are hitting are because you are tied to your fixed infrastructure.

I'm a big fan of SQLite in memory databases and a huge fan of in memory Data grids that scale to terabytes over a networks of powerful processors.

Checkout Gigaspaces.com XAP ... Data Staging over a grid may cut your analysis process down significantly.

========================

 

[fundjunkie]

Hi,
I've pondered this poll for a few days and decided not to vote. I feel that the poll choices do not reflect fully my views and compel the voter, unduely, to take a polarized viewpoint.

That said, this has been an interesting thread. Perhaps non-inflamatory discussion is not dead after all!

Thx
D

 

[PowerST]

Quote from fundjunkie:

Hi,
I've pondered this poll for a few days and decided not to vote. I feel that the poll choices do not reflect fully my views and compel the voter, unduely, to take a polarized viewpoint.

That said, this has been an interesting thread. Perhaps non-inflamatory discussion is not dead after all!

Thx
D

If you find the topic interesting, let's keep the discussion going! I am myself interested to hear your opinions. Not necessary to vote in the original poll. Specifically my interest is software approaches to large scale backtesting. If you have opinions (if that is the topic you refer to having views about) I encourage you to post.

 

[fundjunkie]

Quote from PowerST:

If you find the topic interesting, let's keep the discussion going! I am myself interested to hear your opinions. Not necessary to vote in the original poll. Specifically my interest is software approaches to large scale backtesting. If you have opinions (if that is the topic you refer to having views about) I encourage you to post.

Yes, this topic is interesting; and of interest to me. Whether it can continue and meander along a modified path remains to be seen.

My point related to the poll options rather than to the ground covered by the discussion. That said, I have considered PM'ing CloroxCowboy to ask him to explain in more detail why he think Sql Server is the right vehicle for trading support. He's stated that he disagrees with what's been said but I've not seen much from him that substantively supports his own claims (and shows scalability too). Perhaps he'll return and put more meat on that particular bone...

SQLite would get my vote for the "one man band" operators out there and it has had my attention for a while. But it hasn't fully reeled me in and I do wonder whether there are other alternatives that might work better. I am not a database expert (until I implement and teach myself competency) but I have been toying with the idea of using an object database to store data as .net objects directly. Intuitively it sounds as though it would be quite quick. I believe they are a class of navigational as opposed to relational database. Because of the poor docuemntation out there covering our usage domain I've been unable to determine if the idea has legs or not.

That lack of information is what's stopped me from making a firm technology choice and developing a clear implementation plan. This thread has been helpful in that regard. For that I am most grateful.


Thx
D

 

[rdg]

Quote from nitro:

I have been doing a little reading, and it seems that the InfoBright engine for MySQL seems really interesting:

http://www.infobright.org/
http://dev.mysql.com/tech-resources/articles/datawarehousing_mysql_infobright.html

nitroEDB also seems interesting (nice name too! )
http://www.nitrosecurity.com/information/tech/EDB/

Comments are welcome.

nitro, I don't know if you're still looking at this, but over the weekend I setup Infobright (3.2 RC1) and have started some testing. Currently I have to say it is awesome.

As a first test, I have loaded ~250 million rows of tick data. I tried the same in SQL Server and it totally choked. One of the most amazing things: this data set is about 16GB stored as CSV, and with NTFS compression it's about 4GB. In Infobright it's under 200 megs. Crazy. Before I gave up on SQL Server the indexes alone were 2GB and the total size was about 20. Without defining any characteristics about how I want to reference the data, I can get results to all sorts of queries in a hurry.

I'm going to load in more data and keep testing it. I'll reply here again if I hit a wall at some point.

 

[rdg]

Quote from rdg:

nitro, I don't know if you're still looking at this, but over the weekend I setup Infobright (3.2 RC1) and have started some testing. Currently I have to say it is awesome.

As a first test, I have loaded ~250 million rows of tick data. I tried the same in SQL Server and it totally choked. One of the most amazing things: this data set is about 16GB stored as CSV, and with NTFS compression it's about 4GB. In Infobright it's under 200 megs. Crazy. Before I gave up on SQL Server the indexes alone were 2GB and the total size was about 20. Without defining any characteristics about how I want to reference the data, I can get results to all sorts of queries in a hurry.

I'm going to load in more data and keep testing it. I'll reply here again if I hit a wall at some point.

Infobright is still going strong. The db is over 400 million rows now which is over 80% of my recent data. I had to redesign the layout a few times, but I think it's solid now. At first I didn't realize there was a fake enum type specified by a comment of "lookup" for VARCHARs, so queries are even faster now.

The only issue I'm aware of right now is that it is sensitive to load order. I'm not sure what the optimal load order is for my data set given how I plan to make use of it in the nominal case, so I imagine the performance will eventually degrade with some queries.

All 400 million rows are stored in less than 300 MB of space. The reported compression ratio is 141:1.

 

[Trader922]

Quote from rdg:

Infobright is still going strong. The db is over 400 million rows now which is over 80% of my recent data. I had to redesign the layout a few times, but I think it's solid now. At first I didn't realize there was a fake enum type specified by a comment of "lookup" for VARCHARs, so queries are even faster now.

The only issue I'm aware of right now is that it is sensitive to load order. I'm not sure what the optimal load order is for my data set given how I plan to make use of it in the nominal case, so I imagine the performance will eventually degrade with some queries.

All 400 million rows are stored in less than 300 MB of space. The reported compression ratio is 141:1.

Any further findings on Infobright? I am just beginning to use it for a tick database myself.

Regards,
Eric

 

[nitro]

rdg,

Sorry I completely missed this post!

I really like infobright (IB) too. But I have a question for you. IB sits ontop of MySQL. MySQL does not support (AFAIK) DateTimes that have sub-fractional seconds, for example a time stamp like 12:30:23:454:1234 is not supported. This used to be a problem with MS SQLServer, but they recently added a DateTime2 that supports nano second resolution times.

How do you deal with timestamps in IB for your timeseries? Do you do store the timestamps in a string, and then do some goofy conversion every time you need to?

Quote from rdg:

nitro, I don't know if you're still looking at this, but over the weekend I setup Infobright (3.2 RC1) and have started some testing. Currently I have to say it is awesome.

As a first test, I have loaded ~250 million rows of tick data. I tried the same in SQL Server and it totally choked. One of the most amazing things: this data set is about 16GB stored as CSV, and with NTFS compression it's about 4GB. In Infobright it's under 200 megs. Crazy. Before I gave up on SQL Server the indexes alone were 2GB and the total size was about 20. Without defining any characteristics about how I want to reference the data, I can get results to all sorts of queries in a hurry.

I'm going to load in more data and keep testing it. I'll reply here again if I hit a wall at some point.

 

[thstart]

Quote from nitro:

... sub-fractional seconds...Do you do store the timestamps in a string, and then do some goofy conversion every time you need to?...

This made for each tick will slow down processing significantly.