Give this a shot... blazing fast! http://www.datenhaus.de/Downloads/VBA-NWind-StarSchema.zip SQLite with DHRichClient Wrapper. http://www.thecommon.net/3.html The toolset is thought mainly as a feature-rich vbRuntime-enhancement, which aims to lower the dependencies into the COM-based MS-ToolStack (it contains replacements for e.g. DAO/ADO/JET, MS-XML, DCOM, the Scripting-Dictionary, etc.).
Thanks for the tip. I "know" about Star Schemas and their advantages for data mining from a theoretical point of view. The problem is that I have never actually done this sort of thing "with my own two hands" (I develop and my use of datbases has never run into performance issues. I have never needed to analyze data on the order of the massive amounts that I am talking about here), so I will probably make lots of mistakes at first. I was just noticing that initially, queries are taking for ever. I thought about the problem a little bit and I realized that I was making a silly mistake. By rearranging my tables a little bit, I got 10x performance gains. Now it is resonable, but I can see that as soon as my queries require joins, it is probably going to be painful.
Yep...that's the point of using star schemas - to avoid the need for too many joins. I've been in data architecture and data warehousing for several years, but haven't applied that knowledge to market data. I'm going to look into it though.
It would be too slow for market data. I tried it and it works only on small set of data. Also it is not designed for such kind of analysis.
Try loading the DHRichClient Star Schema Example. It build an in memory SQL DB that performs really well without the MS Stack overhead.
Btw, just to clarify, I plan on using the analysis as part of my nightly homework for the next day. As a discretionary trader, I'm not concerned with real-time data analysis in any database.
Thank you guys. The thread is quite old now, but very informative for anyone interested in doing analysis with SQL Server or similar databases.