Rails - Nov 2006 - Does delete_all do one DELETE statement or several?

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
-~----------~----~----~----~------~----~------~--~---

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,
> Wes
Wes,

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
-~----------~----~----~----~------~----~------~--~---