Rails migrations, SQLite3, and largish data

For the Bus Project, I'm building a walklist app and I'm using rails. I decided to build things using rails migrations, and I'm finding them painfully slow with my SQLite3 database that has ~400k voters in it and ~1200 block captains. Overall though, it's good to be writing ruby again.

Some things to keep in mind when dealing with large databases and SQLite3:

• Wrap things into transactions. SQLite is very slow at doing individual inserts, because it locks and unlocks the file too much.
• Prepared SQL statements are much faster, of course.
• I found ActiveRecord hopelessly slow, so I had to drop into the sqlite3-ruby gem. The FAQ is very helpful.

tech | link | 2006/Mar