Any guess on how sqlite would compare with mysql or postgres in the same type of performance test? The performance data on sqlite from their web site seems very dated... Thank you,
Two different targets... SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. Sqlite's in memory database implementation is hard to beat performance wise. It's ideal as an application specific database but it's clearly not an enterprise RDBS. Some good demo/example can be found here: http://thecommon.net/3.html Checkout the metastock and nwind star schema demos.
I think you doing great on the first half of your Relational database. However, you have not addressed the other half of the database issues if you are really going to try to use this animal. These issues are much harder to handle. For example how are you going to DELETE Rows when you table gets too big to expand any further? The process of trying to delete row 1 while trying to INSERT row 100,000 may be time cost prohibitive. Or then there is overlaying with an UPDATE of row 1 with row 10,000 data. This circular method works but requires a control table to know what processing is going on where. Than as someone mentioned there is the multi tread issue of what happens in the back ground and foreground. Writes using a back ground tread are fine, but SELECTs to the data should be coordinated with this process. So my point is IMO that the time to tweak the database is after all of this processing is defined otherwise you may have to re do it all.
@Schemer, one reason is productivity! I've a freeware app "Zen Analyzer v1.2" on my Website (Support/freeware): http://www.zentrader.de/html/support1.html This tool analyses a OHLC price data set, writing the data in a in-memory SQLite DB and make the TA evaluations via SQL. The hosting language is PowerBasic and using the mighty SQL commands I could avoid to program own complex arrays in PB and so on... Using SQLite with the in-memory option a user even don't recognize, that there is a SQL DB used in the backround, because it's lightning fast... bye, Volker
Could someone please show me a piece of Sql that needs to be executed at some data? I'm having a hard time trying to understand why Sql is relevant in trading.
It's simple. Suppose you have a bunch of tick data in .csv files. Are you going to read a gigabyte-sized .csv file into your program every time you start? It would take a long time to read the data on startup and you'd be storing a huge amount of data in RAM unnecessarily. If the tick data is imported in a database you have fast random access to any tick or group of ticks without storing everything in an array in RAM.
sql is actually really slow for accessing tick data. (although probably faster than csv) sql is very powerful. many implementations of sql are turing complete, so in theory in these languages you could program any application you want using entirely sql. however in practice and for the most part, tick data is used sequentially and not randomly accessed. this is why we do not use sql in tradelink for tick data playback, to maximize speed. (we don't load all the data into memory though) however.... there are lots of other types of financial and trading databases where it is useful to build more randomized queries of data. this could include summarized data sources like indicators, bars, news. eg crude example : select * from NEWS where symbol = { 'SPY', 'CLV' } some of these sources may be generated from sequential sources like tickdata. also it's not uncommon for hedge funds who acquire lots of data to use sql as a data warehouse to hold their raw data acquisitions, and then use sql to mangle and massage the data into the more practical formats required by the investment/modeling/trading applications. these apps may change from time to time but keeping the raw formats in a single place gives you a starting point and some mangling tools. sql is great for this sort of stuff. sql also gets used on the backend in terms of transactional stuff like trade reconciliation, NAV calculations, reporting, etc.
Hi steve, tradelink, thank you for input I'm aware what Sql are in normal usage , but in trading... and that is why I'm asking. I can't see any benefits in trading other than 3rd party applications that work with sql queries, but for developing something on your own it seems strange.. maybe it's just me *LOL* so for instance.. one could write: select * from TickTable where CompanyTicker = 'MSFT' and Date between ..... and ... and...etc. Why not just have that in a simple file storage, not .csv, but binary, index the binary files, the naming could reflect the company etc. I can only see Sqlite as an easy road to do some work, not as a road that should be chosen based on performance.