Whats the fastest way to import data from a remote database to a local database

Question

My current project I am pulling data from a remote database and saving it locally. During some stress test we are seeing horrible results with writing to a database. To give you some perspective:

• Our stress test made ~9000 actions, which saves off about 54,000 records. (1 action = 6 records)
• By 6 records, I mean 6 rows in 4 different tables in a database.
• Heroku took 15-20 minutes to write all of these records.
• Locally, I'm going on 45 minutes still writing.

I'm not sure what's fast/slow, but obviously we can't let the web-page hang around like that. My next thought would be a CRON job, but heroku only allows 1 cron job an hour. That would be a start but we could eventually need more.

The way I'm pulling the data from the remote is:

• Querying the remote
• Matching the remote fields with the local fields
• Save the record
• Loop through to the next record.

Maybe theres a faster way with ruby?

Answer 1

• For the select statements, are you using good indexes?
• Have you checked them with the db tool (mysql describe or veiw query plan in mssql etc?)
• Speaking of indexes if you have too many they will slow inserts.
• You can also try to run in parallel this might be faster.
• You should profile your code to tell what exactly what is going on.

There are also other ways of doing this. All databases have a bulk export utility: mysqldump/load data infile comes to mind for mysql. If you are just copying data from one database to another, this is definitely the preferred way. Going through the app is bound to be slow.