Making destroy_all work faster.

rubyonrails-talk
1

When I call destoy_all on a model with a where clause AR iterates though each record in the recordset and calls destroy on each item. This seems highly inefficient to me. Wouldn't it be better to call delete from child records where foreign_id in (parent ids)?

In other words shouldn't destroy all destroy all the children for all the items in one shot?

I am having to manually do destroy all on my children when I have large number of records to delete so I am wondering if there is a more elegant way of handling this.

Thanks.

2

When I call destoy_all on a model with a where clause AR iterates though each record in the recordset and calls destroy on each item. This seems highly inefficient to me. Wouldn't it be better to call delete from child records where foreign_id in (parent ids)?

In other words shouldn't destroy all destroy all the children for all the items in one shot?

No. Destroy has to instantiate each object prior to actually removing it from the database in order to run any before/after destroy call backs.

You can speed things up if you don't need this by tweaking the :dependent option to has_many so that it will simply use SQL's DELETE on the child objects... but that of course won't run any callbacks (your own, or Rail's counter cache, etc.)

3

No. Destroy has to instantiate each object prior to actually removing it from the database in order to run any before/after destroy call backs.

You can speed things up if you don't need this by tweaking the :dependent option to has_many so that it will simply use SQL's DELETE on the child objects... but that of course won't run any callbacks (your own, or Rail's counter cache, etc.)

Actually it doesn't HAVE to instantiate them if it can query the relationships in a meaningful matter. Of course we know that it CAN query those relationships and calculate what has to be deleted.

I did this....

id_list = Model.select(:id).where(whereclause)..map { |r| r.id }.join(',')

This gives you a list of comma delimited ids

ActiveRecord::Base.transaction do

    Children.delete_all('parent_id in (#{id_list})")   Children2.delete_all('parent_id in (#{id_list})")   Children3.delete_all('parent_id in (#{id_list})") end

In my case I had about a half a dozen children.

The delete time for my records went from four minutes to under 30 seconds. That's a pretty big gain. Of course I had to hand code my relationships and if I add something new I will have to modify my method. It would be really nice if somebody way smarter than me implemented this into AR.

4

Tim Uckun wrote in post #976165:

No. Destroy has to instantiate each object prior to actually removing it from

the database in order to run any before/after destroy call backs.

You can speed things up if you don't need this by tweaking the :dependent

option

to has_many so that it will simply use SQL's DELETE on the child objects... but that of course won't run any callbacks (your own, or Rail's counter cache, etc.)

Actually it doesn't HAVE to instantiate them if it can query the relationships in a meaningful matter. Of course we know that it CAN query those relationships and calculate what has to be deleted.

Unfortunately, that's not always all that a before_destroy callback does. Since that's the case, there's no general method for executing the callbacks without instantiating all those objects.

Of course, if you know you don't need the callbacks, it's perfectly fine to use delete_all instead.

Best,