tinman spent a few years mucking around industry before going back to school for a Masters. Currently not enjoying the weather in North England..
He wrote Perl that looked suspiciously like C code in 1998, while working as an intern, and has been trying to cure that bad habit ever since.
As is usual for me, slow on the uptake, I completely missed the discussion and developments on this site about SQLite.. Having made the discovery yesterday, I downloaded, took home and tried to see how it stacked up against MySQL..
The data volume is moderately large, 13 million lines worth of inserts (thats roughly 4-5 million records) distributed among 3 tables. I have it in a 1GB text file.
I was originally going to use MySQL to store it (Oracle installations on my home machine, I dont think I need).. Looked around for a PostgreSQL Win32 install that I could use without Cygwin pain (didnt find it, sadly), so its a straight toss between SQLite (curiosity) and MySQL. (dont get me started on Access). The downers first: Its not a particularly high spec machine. In particular, I only have 256mb memory. The second is that the hard disk, although a large beast, is also slow.. (why o why did I turn down an offer for a 7.2k RPM disk ?)
I needed to massage the data a bit (pulled it off an Oracle db, so it had a to_date conversion which SQLite barfs on), so did that, created the tables, and left the SQL to run..
An hour later, I became impatient, broke off the import and queried, only to find that one table had a measly 300k records. The other tables were empty. Turns out (reading the speed test) that things are much much faster if you enclose all the inserts in a BEGIN transaction.
Now, what I need to do is figure out how to edit a 1GB file and embed BEGIN blocks before commits (there are commits every few thousand records).
What am I hoping to do with this ? err. just see how SQLite handles large volumes.. and then I can get away with not installing MySQL