Kreslik.com - traders community since 2006

I'm working on a huge Forex historical tick data SQL database in MS SQL Server 2005 and C#.NET.

Since there will be several hundred million rows in the database, I am striving to make the row design as compact as possible. That's why I'm using byte fields as primary keys in the linked auxiliary tables whenever possible.

Alas, it seems that the Microsoft Visual Studio 2005 doesn't support automatical primary key incrementing if the primary key is of type byte (or TinyInt type in SQL terminology).

Visual Studio only seems to support AutoIncrement with Int16, Int32 and Int64 data types, which is a waste of data space:



Does anyone have any experience with this?

Thanks,
Michal

Hi,

If your primary key is of type Byte it means that will have values less then 256 which is very small table, why would save then sapce in so small table you can put it int that is not some big problem I think compared to other bif tables. If you like to use byte have you tried do this with triggers?

Regular int translates to Int32 in SQL and that takes four times as much space in the database (= 4 bytes instead of 1) as the byte (TinyInt) does.

If you consider that my current SQL tick database uses byte fields and it's about 40 gigabytes big, then you definitely will understand why Int32 nor even Int16 are simply not an option here.

Michal

Hello I have a simple mysql schema and I'm using Alpari databank as the source -- I'm using regular INT which I think is 32bits and it's working okay. Lookup times are okay. I have 20 pairs and 2-3 years worth of minute data. I think it's about 1-1.5 gigs.

Oh, sorry I just realized, I eliminated a numeric primary key all together! Here's what it looks like



Of course this is no way the most efficient way to do it but it's working okay for me so for.

My "SQL history engine" MS SQL Server 2005 database create script is attached below.

Currenty it features 255 million+ ticks for 16 FX pairs over the last 7 years and the database size is nearly 100GBs with all the indexes.

I'm feeding this carefully sifted and clean data to my NeoTicker and I'm also using it as a data source for running various statistics, written in C#.

Michal

Hello,

I've wanted tick data myself but the best I could do was minute data from alpari. I'm actually still in the process of developing my 'environment' -- for writing trading systems, and the backtester and optimizer etc.

Anyway, it's nice to known you have such high quality db. Once my backtester is working properly I'll probably be looking around for better data. Can you tell me the source of your ticks? Is it a free source or something private? Thanks.

riddler wrote:Hello,

I've wanted tick data myself but the best I could do was minute data from alpari. I'm actually still in the process of developing my 'environment' -- for writing trading systems, and the backtester and optimizer etc.

Anyway, it's nice to known you have such high quality db. Once my backtester is working properly I'll probably be looking around for better data. Can you tell me the source of your ticks? Is it a free source or something private? Thanks.

Hi,

it's easy to find free tick history data on the internet, have a look at this list, for example:

http://kreslik.com/forums/viewtopic.php?t=92

However, the real quest here is not in finding and downloading the data but rather in setting up a reliable framework that would import, check, clean, store, index and prepare the data for work.

Michal

Thanks for the pointer michal! I have the framework you mentioned for mt4 hst files. I can import them into mysql and 'update' the db from 'newer' hst files. At the same time, I'm also using tick feeds to build higher timeframes which I commit to db as well.

Once the rest of my setup (like backtester) is working I will do the same for GAIN data.

Regards,
Divya

michal, what version of sql server are you using? I mean, is it the Developer's Edition or just the free version?