AR 1.14.4 SQL Server 2000 (via ODBC) I have two objects TargetList and Target where TargetList has_many Targets. Somewhere in the TargetList object, I issue the command targets.delete_all For 213 targets, this takes quite a while. When I look in the log, I see a bunch of these statements (interestingly, I can''t find a DELETE statement written anywhere in the log). SQL (0.000000) SELECT @@ROWCOUNT AS AffectedRows Target Destroy (0.010000) WHERE UniqueID = 1634 SQL (0.000000) SELECT @@ROWCOUNT AS AffectedRows Target Destroy (0.020000) WHERE UniqueID = 1635 This looks to me to indicate that one DELETE is occurring for each target in the collection instead of one big DELETE statement. Do I have to add the foreign key condition back to target_lists explicitly in order for one SQL statement to be issued? I want my mass deletes to be efficient, one DELETE statement vs. many. Thanks, Wes -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
Frederick Cheung
2006-Nov-01 16:35 UTC
Re: Does delete_all do one DELETE statement or several?
Target.delete_all definitely does one single delete
targets.delete_all is different. it winds up calling delete_records,
which for has_many_association looks like this
def delete_records(records)
if @reflection.options[:dependent]
records.each { |r| r.destroy }
else
ids = quoted_record_ids(records)
@reflection.klass.update_all(
"#{@reflection.primary_key_name} = NULL",
"#{@reflection.primary_key_name} = #{@owner.quoted_id} AND
#{@reflection.klass.primary_key} IN (#{ids})"
)
end
end
So if you''ve got :dependent set, then you''ll be destroying
them one by
one.
Fred
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---
Wes Gamble wrote:> Do I have to add the foreign key condition back to target_lists > explicitly in order for one SQL statement to be issued? > > I want my mass deletes to be efficient, one DELETE statement vs. many. > > Thanks, > WesWes, Well, I couldn''t believe your post until I sat down and tried it myself and got the same behaviour as you did which I find very surprising indeed (except I see the individual deletes in my log file). I have the same 1-n relationship as you do and called a destroy_all() on the list only to have the log file call delete on each individual list entry!!??.. I don''t know how this will help you except to say that you are not crazy and that I verified this too on rails 1.1.6 with delete_all. The foreign key constraints solution will only help if you are deleting the container itself. Very interested in seeing what I am doing wrong here.. My back end is postgres 8.1 using the postgres-pr drivers ilan -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
Ilan, Per Fred''s post above, I am calling delete_all on a dependent child collection which is why it''s doing individual deletes. This is yet another case where I expect ActiveRecord to take care of doing something that seems intuitive but doesn''t work as I expect. I expect that since I said "delete_all" on the specific child collection (targets), that the implicit foreign_key condition would be supplied for me when the SQL is generated. Obviously, for now, I can do the Target.delete_all and add the appropriate condition. However, I have to ask the question: Why isn''t there a delete_all implemented in this way on has_many associations? Is there some concern around lifecycle callbacks being called or something - I''m guessing that doesn''t happen in cases where delete_all is called. I already have to ability to choose whether or not I want "destroy" behavior or a full on DELETE. So it seems like this might be a gap in association management. YATTR (Yet Another Thing To Remember) ;) Wes -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
Wes Gamble wrote:> However, I have to ask the question: Why isn''t there a delete_all > implemented in this way on has_many associations? Is there some concern > around lifecycle callbacks being called or something - I''m guessing that > doesn''t happen in cases where delete_all is called. >Wes, That was my understanding as well, that delete/delete_all() circumvents the callbacks (while destroy/destroy_all() doesn''t) so I am confused as well as to why delete_all() is working as you discovered. The :dependant option makes no sense to me as as well since that only matters when the container is removed and that is approximately equivalent to having the foreign key constraints as was discussed earlier. Anyways, this was an eye opener and I thank you for bringing it up. Thankfully, it will be easy to refactor my code to use the class version of delete_all. ilan -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---