Database updates are very slow

rubyonrails-talk
1

I'm doing a migration that needs to go through all rows in a DB table and update then.

It takes forever and I've narrowed it down to Rails' own .save() call. If I do all the update without saving, it completes in a few seconds. With the Active Record save, 60000 rows take 15 minutes to update.

Is there a way for me to bypass that and write the values directly to the database?

2

I’m doing a migration that needs to go through all rows in a DB table

and update then.

It takes forever and I’ve narrowed it down to Rails’ own .save() call.

If I do all the update without saving, it completes in a few seconds.

With the Active Record save, 60000 rows take 15 minutes to update.

Is there a way for me to bypass that and write the values directly to

the database?

Where’s the code that you’re using to do the migration?

-Conrad

3

Table.find(:all).each { |e| e.save }

I have some processing per entry, but even if I remove it all and just do the save, it takes forever.

If I return everything else and just comment out e.save it completes quickly.

What puzzles me is that the CPU is running 100%, 95% Ruby and only 5% MySQL, so the actual table updates are not the problem. Active Record is just loading it.

Amir

4

Do you have any on_save callbacks on the table's model? Those are executing on each run. Also, all your validations are running as well with the save. You can ignore validations by altering your code to:

Table.find(:all).each {|e| e.save(false)}

However, your callbacks will still execute and if you have any observers, those get called as well.

If any of those can't be skipped with a conditional, then you can always run your updates with raw sql.

5

helzer a écrit, le 11/18/2009 07:43 PM :

I'm doing a migration that needs to go through all rows in a DB table and update then.

It takes forever and I've narrowed it down to Rails' own .save() call. If I do all the update without saving, it completes in a few seconds. With the Active Record save, 60000 rows take 15 minutes to update.

Is there a way for me to bypass that and write the values directly to the database?    YourModel.connection.execute("your UPDATE in SQL here")

Lionel

6

Table.find(:all).each { |e| e.save }

After thinking about what you’re trying to do, it might be much faster to simply

perform the update using SQL. Then you’ll avoid AR all together. Otherwise,

you might be interested in using something ar-extensions which is great for

bulk updates.

-Conrad

7

Thanks!

I don't have any complicated logic hanging on the save code and only trivial validations. I'll just use the connection.execute and that's it.

Amir