Database updates are very slow

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?

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

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

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.

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

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

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