SQL Transactions - Faster Faster!

So I went into the office yesterday and built a good bit of the stuff from the last post. Still some work to do, but it should be a really useful tool. Here's some of the stuff I learned along the way.

The big one: SQL Transactions rock. You tell the console "start transaction", and that tells the SQL server (mySQL 5.something in this case) "I'm about to do a number of queries, but don't worry about writing the results to disk until the end - Nothing I'm going to do is dependent on what came before it in this transaction."

Once you're done doing your thing, you can "commit" or "rollback". That makes it a nice testing tool as well.

In bootstrapping myself to a properly working system, I ended up with a 74MB flat text file full of SQL INSERTs that needed to be processed. Stupidly, I just told the console "source [my-file]". It took four hours. Tried the same thing again with transactions, and it was done in less than a minute.

What else did I learn? Well, I picked up a few things about the GD library. It's surprisingly easy. I couldn't find a graphing frontend to it that worked on Windows to my satisfaction, so I just started writing a few graphing functions of my own, and they work pretty well.

Oh yeah, I had never used VIEWs in SQL before, but they seem to be pretty powerful. They solved a problem with a subquery that mySQL didn't support, which was real nice. I was trying to grab the high x% of a set of values, and do operations on those (ie, min/max, average, etc).

That happens to me a lot with programming. I was a double E in school, so my formal training in things like database theory is pretty much nil. Everything I know I picked up from having to solve specific problems. Maybe I should go see if I can audit a class or something, I might learn a thing or two.