Is it better to do a select count then if before doing a delete or just blindly call delete?

Question

I'm looking for a best practice / thoughts on if it is better to do a select count and checking if the result is > 0 before calling a delete or if it would be better to just blindly fire a delete statement at the database even if the data doesn't exist. In our case most of the time data will NOT exist.

So what is better:

Option 1: call Select Count(X) where foo, if result > 0, delete where foo

or

Option 2: delete where foo

I lean towards the blind delete for speed reasons and since you are doing a table hit anyways.

EDIT: This is actually happening in kettle(an ETL tool) so the three operations will be done completely separate if there is a delete. So completely in SQL is not an option.

Answer 1

Just go for the delete. There is no advantage in issuing a count(*) beforehand.

Answer 2

I would just delete, if you try to delete something that is not there, it will just delete 0 (zero) rows. This reduces the amount of trips to the DB as well.

Answer 3

I would say I would do the delete blindly, and then get back the number of rows affected. In either cases, the comparison is done, it should be almost the same execution time.

Answer 4

The universal IT answer is prefaced with "It Depends"

Here it depends on the query plan of the Delete.

If you do an EXISTS query (per OMG-Ponies) that uses an index or a better query plan, that would be better-faster.

An unneeded delete that uses a table scan could then perhaps be avoided.