historical tick data SQL database

realtime data feed providers, historical data databases

Moderator: moderators

User avatar
michal.kreslik
rank: 1000+ posts
rank: 1000+ posts
Posts: 1032
Joined: Sat May 13, 2006 2:40 am
Reputation: 0
Location: Monte Carlo, Monaco
Real name: Michal Kreslik
Gender: Male
Contact:

historical tick data SQL database

Postby michal.kreslik » Wed Nov 22, 2006 9:01 pm

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

Please add www.kreslik.com to your ad blocker white list.
Thank you for your support.

dragan
rank: <50 posts
rank: <50 posts
Posts: 1
Joined: Thu Jan 18, 2007 7:25 pm
Reputation: 0
Gender: Male

Postby dragan » Thu Jan 18, 2007 7:40 pm

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?

User avatar
michal.kreslik
rank: 1000+ posts
rank: 1000+ posts
Posts: 1032
Joined: Sat May 13, 2006 2:40 am
Reputation: 0
Location: Monte Carlo, Monaco
Real name: Michal Kreslik
Gender: Male
Contact:

Postby michal.kreslik » Thu Jan 18, 2007 11:17 pm

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

riddler
rank: <50 posts
rank: <50 posts
Posts: 4
Joined: Thu Feb 08, 2007 5:25 am
Reputation: 0
Gender: None specified

Postby riddler » Thu Feb 08, 2007 5:29 am

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.

riddler
rank: <50 posts
rank: <50 posts
Posts: 4
Joined: Thu Feb 08, 2007 5:25 am
Reputation: 0
Gender: None specified

Postby riddler » Thu Feb 08, 2007 5:31 am

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

Code: Select all

CREATE TABLE `audusd` (
   `symbol` char(6) NOT NULL default 'audusd',
   `period` mediumint unsigned NOT NULL default 1,
   `time` datetime NOT NULL,
   `open` double unsigned NOT NULL,
   `low` double unsigned NOT NULL,
   `high` double unsigned NOT NULL,
   `close` double unsigned NOT NULL,
   `volume` double NOT NULL,
   `source` char(20) NOT NULL default 'Databank',
   `created` timestamp NULL default NULL,
   `updated` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
   PRIMARY KEY  (`time`, `period`, `source`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;


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

Please add www.kreslik.com to your ad blocker white list.
Thank you for your support.

User avatar
michal.kreslik
rank: 1000+ posts
rank: 1000+ posts
Posts: 1032
Joined: Sat May 13, 2006 2:40 am
Reputation: 0
Location: Monte Carlo, Monaco
Real name: Michal Kreslik
Gender: Male
Contact:

Postby michal.kreslik » Thu Feb 08, 2007 5:51 am

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
Attachments
FXdataDB_Scripted_All.zip
(4.14 KiB) Downloaded 129 times

riddler
rank: <50 posts
rank: <50 posts
Posts: 4
Joined: Thu Feb 08, 2007 5:25 am
Reputation: 0
Gender: None specified

Postby riddler » Thu Feb 08, 2007 6:00 am

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.

User avatar
michal.kreslik
rank: 1000+ posts
rank: 1000+ posts
Posts: 1032
Joined: Sat May 13, 2006 2:40 am
Reputation: 0
Location: Monte Carlo, Monaco
Real name: Michal Kreslik
Gender: Male
Contact:

Postby michal.kreslik » Fri Feb 09, 2007 4:56 am

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

riddler
rank: <50 posts
rank: <50 posts
Posts: 4
Joined: Thu Feb 08, 2007 5:25 am
Reputation: 0
Gender: None specified

Postby riddler » Fri Feb 09, 2007 2:19 pm

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

ezTrader
rank: <50 posts
rank: <50 posts
Posts: 3
Joined: Wed Jun 28, 2006 3:05 pm
Reputation: 0
Gender: None specified

Postby ezTrader » Tue May 01, 2007 4:59 pm

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

Please add www.kreslik.com to your ad blocker white list.
Thank you for your support.


Return to “data feeds & historical data”