CODEWITHSUNDEEP
sqlsql-server
electrotype
electrotype

Reputation: 8806

SQL Server - The optimal way of running two DELETE statements using the same SELECT results

I need to delete entries from two tables, using some ids found from a unique SELECT query.

What is the most performant way of doing this using SQL Server?

I currently use a Table Variable :

DECLARE @emailIdsToDelete TABLE(id int NOT NULL);

INSERT INTO @emailIdsToDelete
SELECT id
FROM ...;

DELETE organisations_emails
FROM @emailIdsToDelete etd
INNER JOIN ...;

DELETE emails
FROM @emailIdsToDelete etd
INNER JOIN ...;

But since I need that table only to store the result of the SELECT, and only for the scope of this specific query, I was wondering if there was a more performant solution.

I think I can't use CTE (using the "with()" clause) since it allows only one following statement, not two.

Is there a way to delete from those two tables, without having to create a temporary table first? Some kind of join? Or is my solution already optimal?

UPDATE : Let's say there is no "ON DELETE CASCADE" between any of those tables.

Upvotes: 1

Views: 94

Answers (2)

Random_User
Random_User

Reputation: 363

Cascade Delete is an option, however you would need to test to validate it is more performant, also sometimes even with FKs this can leave orphans records.

Soft Delete You could add a isDeleteFlag column set it to bit default(0) and update it to 1 when you run your query, again would have to test it. If you wanted to stop there you would need to change your usp_ to accommodate not to return these records. where isDeleteFlag = 0

To follow up with the most performant, you could use the soft delete and then at night do smaller batches of deletes, or you could store the delete ids in a separate table and remove them at night in batches. This might work for you depending on what you are trying to accomplish, if it is okay to steal performance at night rather than during normal business operations, sometimes it's not viable.

In the end the answer is it depends and mileage will vary, you'll need to test the solutions to compare performance.

Optionally you could use your approach and use a variable table / temp table or a user defined table type.

Edit: Also you could output the deleted ids and daisy chain deletes in a transaction.

Upvotes: 1

Paul
Paul

Reputation: 16853

The other way would be to have a foreign key between organisations_emails and emails with ON DELETE CASCADE. Then you would merely need to delete from emails, and matching rows in organisations_emails would be deleted automatically.

Assuming that the foreign key is appropriate in your schema, of course.

Upvotes: 1

Related Questions