Tick Database, Now Want to Run SQL

Thanks for all those valuable comments. I think I pretty much decided on the following:

Strictly splitting business logic between a) back testing / trading strategy related data management needs and b) data supply to run independent analytics, feed other unrelated apps,...

a) I decided to stick to my own custom binary time series data storage for backtesting purpose. All the back tests need is random access to a time series specified by start and end time stamp. I run each symbol of tick based data in a separate file and despite those being in the gigabyte region can access the time series at very fast speeds, faster than I have seen with any database I tried so far. Any analytical data that are output by the strategy (trades, TCA, risk, indicators, statistics) are stored in an in-memory database in order to query such data for research purposes post raw data iteration in the strategy modules. The query process is manual. In that there is a clear segregation between time series based data and more relational data on the other side.

b) I will spend more time on this later as I have not fully decided but currently I err on the side of a relational database. I do not need low latency access, only load couple 100 bars or 1000 ticks per symbol into some other applications or excel. I like to store tick and bar data of about 600-700 symbols in such database. What database would you recommend for such purpose? I want to access it through Excel, and .Net applications. As I need bars in Excel would you recommend a database that I can query with SQL and aggregate bars from ticks through an SQL query on the fly or store ticks and 1-minute bars in the database and aggregate bars from 1-minute bars given I do not need finer granularity bars? Would really appreciate some specific database recommendation for the specify example:

Data storage specs: 600-700 symbols, tick data, (fx, stocks, futures, indexes). Can store 1-minute bars as well. Storing about max 1 year history.

Read specs: No low latency requirements, want to read tick data between start and end time stamp of a given symbol; want to retrieve bars of any requested compression of a given symbol between start and end time stamp (would it be fast enough to compress tick data to bars on the fly through a query or will that be very very slow? ); want to query the average price of a given set of symbols at a specified time stamp.

Write specs: Need access to the previously closed 1-minute bar at the very least, so need to store either bars every minute (when the symbol in question is currently traded) or ticks in batches every minute.

Which database can match or exceed those specs? It does not sound extremely demanding to me, writing tick based data in batches every minute over 600-700 symbols could possibly be an issue for an RMDBS? Any ideas or suggestions? I emphasize access through Excel (thus SQL queries I guess) because its the lowest common denominator.

Thanks in advance for any recommendations.

Quote from Makis:

You got your requirements right, so for whatever is worth to you... I can tell you, you are on the right path, at least design-wise. Thats how the big boys do it, but they have the resources to put different teams/budgets on it. On one hand the real time capture team uses a column based database (kdb+, oneTick) to capture market data and a different team captures transactions on a relational database (oracle, Sybase)

The first database usually has 2 tables (trades,quotes) and queries never do joins, just extract part of the column and real-time calculate average spreads, realized volatility, intraday correlations, volume profiles, vwaps etc...
The transactions database has several tables and any meaningful query joins multiple tables.
The big difference is that the column based database is expected to capture thousands more messages per second than the relational database.

For people that try to build something at home, (usually after returning from work) this would be not be easy. I am not aware of a single product that can match the two database words, but in some kludgy way it can be done. I think that a column based database can accommodate your needs (need some crafty queries, that you will store as custom functions) but not the other way around.
More...
 
Quote from amazingIndustry:

Thanks for all those valuable comments. I think I pretty much decided on the following:

Strictly splitting business logic between a) back testing / trading strategy related data management needs and b) data supply to run independent analytics, feed other unrelated apps,...

a) I decided to stick to my own custom binary time series data storage for backtesting purpose. All the back tests need is random access to a time series specified by start and end time stamp. I run each symbol of tick based data in a separate file and despite those being in the gigabyte region can access the time series at very fast speeds, faster than I have seen with any database I tried so far. Any analytical data that are output by the strategy (trades, TCA, risk, indicators, statistics) are stored in an in-memory database in order to query such data for research purposes post raw data iteration in the strategy modules. The query process is manual. In that there is a clear segregation between time series based data and more relational data on the other side.

b) I will spend more time on this later as I have not fully decided but currently I err on the side of a relational database. I do not need low latency access, only load couple 100 bars or 1000 ticks per symbol into some other applications or excel. I like to store tick and bar data of about 600-700 symbols in such database. What database would you recommend for such purpose? I want to access it through Excel, and .Net applications. As I need bars in Excel would you recommend a database that I can query with SQL and aggregate bars from ticks through an SQL query on the fly or store ticks and 1-minute bars in the database and aggregate bars from 1-minute bars given I do not need finer granularity bars? Would really appreciate some specific database recommendation for the specify example:

Data storage specs: 600-700 symbols, tick data, (fx, stocks, futures, indexes). Can store 1-minute bars as well. Storing about max 1 year history.

Read specs: No low latency requirements, want to read tick data between start and end time stamp of a given symbol; want to retrieve bars of any requested compression of a given symbol between start and end time stamp (would it be fast enough to compress tick data to bars on the fly through a query or will that be very very slow? ); want to query the average price of a given set of symbols at a specified time stamp.

Write specs: Need access to the previously closed 1-minute bar at the very least, so need to store either bars every minute (when the symbol in question is currently traded) or ticks in batches every minute.

Which database can match or exceed those specs? It does not sound extremely demanding to me, writing tick based data in batches every minute over 600-700 symbols could possibly be an issue for an RMDBS? Any ideas or suggestions? I emphasize access through Excel (thus SQL queries I guess) because its the lowest common denominator.

Thanks in advance for any recommendations.
More...

I do not have much experience with tick data on relational databases, but since the message rate is not that big and query latency is not critical i think any respectable RDBMS will do fine. You can look at mysql inmemory engines and timesten, which can work in hybrid memory/disk mode.
 
thanks I am currently playing with TimesTen in my spare time and will report back once I got around to run some performance tests.

Quote from Makis:

I do not have much experience with tick data on relational databases, but since the message rate is not that big and query latency is not critical i think any respectable RDBMS will do fine. You can look at mysql inmemory engines and timesten, which can work in hybrid memory/disk mode.
More...
 
Quote from amazingIndustry:

I focus on fx markets and generally I do not see the optimization opportunities you indicated. On busy days I capture about 200,000 ticks (quotes) per symbol, there is nothing to optimize about it. Fx data are bid and offer, only. I experimented with only storing bid and offer deltas , the offer being a delta from the bid itself. I found that the bottleneck at a certain level is not how fast you get quotes into memory (including the overhead of deserialization, building back the quote from deltas, merging multi-symbol streams into one consolidated time stamp sorted stream) but the real bottleneck becomes the strategy algorithms that consume the quotes, trades,.... I ended up storing binary representations of full bid/offer quotes which ended up to be 16bytes, including time stamp (down to microseconds). I currently max out at 5 million ticks/quotes per second, and that not at the data feed module but my strategy module, with my current quad core hardware on one single machine the strategies cannot chew through more than 5 million ticks a second no matter how many more ticks per second my data feed can provide on the producer side. The simple reason for that is that all indicators, all analytics necessary for the strategy engine to make trading decisions are calculated on the fly. It would defeat the whole purpose of such platform if I started to pre-calculate analytics, store them in files or databases and loaded those alongside the quote data in a backtest. That feels to me like a Python or R, Matlab approach to profiling and testing ideas. I want to run a testing platform that is nearly identical to the live trading box, no porting of code necessary.
More...

I'm curious about your implementation. I too have written a "custom binary time series data storage for backtesting purpose". All in c#.

I did a few optimization that reduced the storage size of the file, but it hurt the read speed. I'm content with this, because any of the strategies we run can only handle about 200-300 messages to second, just from invoking a few private methods, interfaces, updating collections, etc.

So we don't receive any benefit if our data stream could stream any higher rates than that. Is this your experience as well?

- Thanks
 
Quote from DevBrian:

I'm curious about your implementation. I too have written a "custom binary time series data storage for backtesting purpose". All in c#.

I did a few optimization that reduced the storage size of the file, but it hurt the read speed. I'm content with this, because any of the strategies we run can only handle about 200-300 messages to second, just from invoking a few private methods, interfaces, updating collections, etc.

So we don't receive any benefit if our data stream could stream any higher rates than that. Is this your experience as well?

- Thanks
More...

That is too little. I suggest you get rid of most of the floating point arithmetic and replace it with integers. Then lean how to make fast divisions (hint: multiple - shift instead of division).

You should be able to pull about 4-5 million ticks per second into your strategies, and I seriously fail to see how those go down to 200-300 messages per second outside of REALLY crappy programming. 200.000 to 300.000 with a HEAVY strategy - ok, but 200-300? That is FACTORS too low.

Put a profiler on it and find where you waste like 90% of your time upward.
 
Quote from NetTecture:

That is too little. I suggest you get rid of most of the floating point arithmetic and replace it with integers. Then lean how to make fast divisions (hint: multiple - shift instead of division).

You should be able to pull about 4-5 million ticks per second into your strategies, and I seriously fail to see how those go down to 200-300 messages per second outside of REALLY crappy programming. 200.000 to 300.000 with a HEAVY strategy - ok, but 200-300? That is FACTORS too low.

Put a profiler on it and find where you waste like 90% of your time upward.
More...

I'm sorry. I did mean 200k-300k, not 200-300.

I was able to strip things down, to where I could pump 5 million messages per second. Few things I did:

- Minimized the number of private method calls.
- Removed interface calls.
- No locks/no multi-threading/no arithmetic
- No maintaining of any "Last Quote" type collections

With a strategy that does absolutely nothing, and without the above, it can go 5 million messages a second. I add the above, it goes back down to 200k-300k message per second.

I can live without the above. But any strategy will likely reintroduce this level of overhead, and I'm back to 200k-300k messages per second.
 
Quote from NetTecture:

That is too little. I suggest you get rid of most of the floating point arithmetic and replace it with integers. Then lean how to make fast divisions (hint: multiple - shift instead of division).

You should be able to pull about 4-5 million ticks per second into your strategies, and I seriously fail to see how those go down to 200-300 messages per second outside of REALLY crappy programming. 200.000 to 300.000 with a HEAVY strategy - ok, but 200-300? That is FACTORS too low.

Put a profiler on it and find where you waste like 90% of your time upward.
More...

I'm sorry, I meant to say 200k-300k.
 
I read the first post in this thread, and too quickly browsed the rest, then sorry if someone already answered this.

Dont use SQL. It is a dead end. SQL (and alike) are meant for queries where speed is not an issue. You should use a memory array for storing all the data you want. Reading from RAM is the only method used by programmers to access data where speed is critical.
 
Sorry, I meant to say 200k-300k messages per second.

ps - if I'm repeating myself, ET isn't showing my posts, so I'm trying again.
 
Yes obviously your weakest link will be your bottleneck. No matter how fast you can stream the data at rate y, if your strategies can only consume at rate x, with x< y then you are constrained by x.

Quote from DevBrian:

I'm curious about your implementation. I too have written a "custom binary time series data storage for backtesting purpose". All in c#.

I did a few optimization that reduced the storage size of the file, but it hurt the read speed. I'm content with this, because any of the strategies we run can only handle about 200-300 messages to second, just from invoking a few private methods, interfaces, updating collections, etc.

So we don't receive any benefit if our data stream could stream any higher rates than that. Is this your experience as well?

- Thanks
More...
 
You must log in or register to reply here.
Back
Top