Does delete_all do one DELETE statement or several?

Rails
1

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

2

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

3

Wes G. 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

4

Wes G. 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

5

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) :wink:

Wes