Searching historical data in Sql

M

morphtrade

Hi,
I have historical one min and tick data save sql database server. I search this historical data for patterns which is very time consuming not only in writing the complex t-sql scripts but also executing them.
If I calculate something like ema then it take ages to execute. Any advice or suggestion on how can I make this process time efficient, for eg. buying historical data with indicator / study values included or using some tools, that will be much appreciated.

Cheers
M
 
A lot of the performance in SQL is dependent on how the query is written and the table is indexed. You may also want to look at a computed column. My first guess would be the query can you post your code? You could also try posting it at www.sqlservercentral.com.

Doug
 
this has been posted before. you dont put time series data into a relational database. you put it into a timeseries (vector) database.

ignore all the posts that say you should continue using mysql, oracle, sqlserver, ... for this work.
 
You can make it very fast to query the time series data with proper indexing, but my experience is that this can make updates extremely slow once the database gets very large. Using a relational database to store tick data isn't worth it. The relational database doesn't give you much in return for the additional hassle. Just use binary files.
 
I don't mean to hijack the thread but I have a Q.

I have zero knowledge when it comes to databases. I currently store all my tick, minute, and daily data in plain text ASCII files. When I'm backtesting using software I wrote, the majority of the time spent is loading in the data (probably 90%). If I use a database of some type, will it speed up the process? Would binary files be faster to load in? I have about 100 gigs worth of data.
 
It seems to me that text files and loading in the data would be prohibitively expensive in the RAM department.

I am using SQL relational database but I am going to need to look at these timeseries (vector) databases. I haven't heard of them before..
 
Quote from morphtrade:

Hi,
I have historical one min and tick data save sql database server. I search this historical data for patterns which is very time consuming not only in writing the complex t-sql scripts but also executing them.
If I calculate something like ema then it take ages to execute. Any advice or suggestion on how can I make this process time efficient, for eg. buying historical data with indicator / study values included or using some tools, that will be much appreciated.

Cheers
M
More...

I did a lot of relational database query tuning a very long time ago, so the following is dated and maybe no longer relevant. FWIW, here is what comes to mind:

1) what everyone said about indexing is right but you can get around the update speed issue by making indexed read only versions of your tables and syncing them periodically. if you don't update them too often you can just drop the indexes, update, then rebuild the indexes

2) join order sometimes makes a huge difference

3) try writing your queries in stored procedures

4)sql for smarties by selko is/was a good reference for performance tuning
 
If I calculate something like ema then it take ages to execute. Any advice or suggestion on how can I make this process time efficient, for eg. buying historical data with indicator / study values included or using some tools, that will be much appreciated.
More...

Create a second table to contain all the caculated data. For example, this table would have a column, 20_Day_EMA. Calculate the 20-Day EMA and store the data.

This way you compute the 20-Day EMA once.
 
Quote from shortorlong:

Can someone point me to a good vector DBMS? I cant seem to find anything. Wrong keyword perhaps?
More...
Try FAME, now owned by Sunguard I think.

Note: I don't particularly like it. Very awkward.
 
You must log in or register to reply here.
Back
Top