0

I'm trying to delete specific rows from various tables by id in my database, and I have too many tables to delete from, for example:

DELETE FROM [Customers] 
WHERE [id] = @Id

DELETE FROM [Agents]
WHERE [id] = @Id

DELETE FROM [Policy] 
WHERE [id] = @Id

DELETE FROM [Issues]
WHERE [id] = @id

DELETE FROM [Refunds] 
WHERE [id] = @Id

EXEC SP_DeleteCustomerRelations @Id
EXEC SP_DeleteAgentComplaints @Id

DELETE FROM [dbo].[Complaints]
WHERE id = @Id

I was wondering if there anyway to enhance the performance of this SQL query?

Or is there anyway to not repeat DELETE? As it takes up to 10 seconds deleting from all these rows from tables.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mostafa
  • 19
  • 4
  • 3
    Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! – marc_s Oct 01 '18 at 04:28
  • This one may help you https://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server – Santosh Jadi Oct 01 '18 at 04:36
  • `DELETE` in SQL Server only removes rows from one table, so you cannot reduce the number of deletes in the code. Your question hints that lots of rows are being deleted -- and that is the cause of the performance problems. – Gordon Linoff Oct 01 '18 at 11:15

1 Answers1

0

Are these Tables in relation of parent/child means is that ID is PK of one table and used as a FK in the other ones. If it is the scenerio you can use the cascading here means when ID is deleted from the parent table it will automatically get deleted from the child tables. See this link for How to use Cascade Delete.

Hope this helps please let me know if it doesnt helps.

Amit chauhan
  • 540
  • 9
  • 22