I'd really appreciate a SQL forum to exchange ideas.
I'm in the throes of getting my own head around all that MS SQL has to offer. Had been using VBA in Excel for years and was very comfortable with it. But then I decided to build a database of French racing ('cos I live there), as there's no decent one to be had here for love nor money. Courtesy of Paris-Turf (= Racing Post) and France Galop (= BHA + Jockey Club), I managed to get myself a 30-year database in a load of text files. I initially had a look at the Proform SQL code, but was finding it tough slogging to make full sense of it. I decided, therefore, to stick with VBA for Excel.
However a chance posting by arkroyal on the Proform forum ended up with us exchanging emails on the use of MS SQL and it reawakened my interest. (I thought he was still continuing with it, but it looks like he was off setting up excellent forums, or other silly nonsense like that. Tch, tch, tch...) I loaded up all of my text files into a SQL database using the same server as the Proform database on my desktop, using just two tables: a race table and a horse table. At first, each (simple) query was taking about 20 seconds to execute, but this worsened to about 40 seconds and remained at that. I was disappointed, but decided to persevere nonetheless. However, I installed the 2012 Developer edition after Christmas, using a different server, and the performance difference is incredible. I don't think any query has taken more than 3 seconds to execute. I'm hooked and there's no way I'm going to move away.
Well, my learning curve is still in the vertical, but I've got loads of time now, as I retired last November. I've found some great learning resources along the way. The first is a forum essentially for professional database administrators, but it also caters for neophytes, such as myself, with excellent articles, hand-holding guides and even a newbie forum:
Code:
http://www.sqlservercentral.com/
Second is a literature resource, with hundreds of books on the different flavours of SQL, as well as other programming languages. The free downloads can be done at one per hour:
Third is an application called QlikView. It can import data from any database, text, .CSV or Excel files and with a little deft programming some pretty good analysis can be done. I'm not familiar enough with it to give it the description it deserves, so it's best to check it out for yourselves. It's free for personal use, meaning that the document files you save from within the application are tied to your particular machine and cannot be opened by any one else. Some books on it can be found at the link above and there are some tutorial videos on YouTube. It can be found at:
Allowing for the fact that I'm still very much feeling my way around in the dark as far as MS SQL is concerned, I think the main thing I've learned is that my database of two tables with approx 0.25 and 2.5 million rows (37 and 40 columns), respectively, is really miniscule when compared with the industrial DBs in use today. So I doubt if putting constraints on these tables, such as primary/foreign keys, and creating indexes will make any difference to the performance. Would it not be better to keep everything in one table and not worry about joins and all that? Maybe someone who knows more about it can point out the error of my thinking. The more simple we can keep it, the easier it will be to analyse the data.
Anyway, that's where I'm coming from. I'd really enjoy having a coding forum dedicated to horse racing to come to.
Ray