Forum: Ruby on Rails Speeding up :dependent => :destroy

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Mikel (Guest)
on 2007-06-29 11:02
(Received via mailing list)
I have a tree of models that represent a book.  Looks like this:

book
   sections
      documents
          paragraphs
              index entries
              glossary entries
              footnotes
                  index entries
                  glossary entries

These are all models with has_many from parent and the child has
belongs_to.  They also all have dependent => :destroy pointing down
the tree.  Delete a section and it nukes everything beneath it.

There is only one problem.

If you select a document, and delete... the AR query is HUGE as it
searches through and finds each paragraph, which then finds each
index, glossary, footnote, which then finds each index and glossary.

This takes a LONG time... finding, opening, deleting each record...
thousands of database queries.

I need a way to speed this up as it is non functional.

Two ways I can see:

Remove the dependent => destroy and make a before_destroy method which
puts the child delete in a transaction, and then cascade that same
before_destroy method in each subclass (feels like a bad hack).

Create a stored procedure in Mysql to handle it (not portable).

Has anyone else fixed this problem before?  What is the best way to go
here?

Regards

Mikel L..
Michael L. (Guest)
on 2007-06-29 19:24
(Received via mailing list)
I suggest a different approach.

I have a tree like this and in the after save I update another table
for containments which i need for some related queries.  Then in the
before_destroy you can use one join with the containments table to
delete all enclosed records.

Michael

Here is my after_save filter for my "Place" class:


   #
   # Update all enclosures for records that enclose this record
   #
   def update_containment
     Containment.destroy_all :contained_place_id => self.id
     parents = []
     rec = self.parent
     while rec != nil do
       parents << rec.id
       rec = rec.parent
     end
     db = Containment.connection
     db.execute "insert into containments(place_id,
contained_place_id) values(#{self.id}, #{self.id})"
     for p in parents do
       db.execute "insert into containments(place_id,
contained_place_id) select #{p}, cont.contained_place_id "+
         " from containments as cont where cont.place_id = #{self.id}"
     end
   end
This topic is locked and can not be replied to.