CODEWITHSUNDEEP
sql-serverperformancesql-server-2008stored-procedures
A.Goutam
A.Goutam

Reputation: 3494

how to improve query performance to delete child table record faster

i have a table Order where i want to delete the data by clientId. This table have more than 2071458 records per client . And this table have foreign key reference with 16 table . Wo when i try to delete the record by clientid it takes 2-4 hours to delete the reocord . So how can i improve the performance to do it faster. May i use subquery if yes how can i use in this query . Below is the query that i am using .

DECLARE @ORDERID int   
DECLARE DEL_RelDataOFOrdeTab CURSOR FOR  
SELECT ORDERID FROM orde_ WHERE CLIENTID = @ClientID  
 OPEN DEL_RelDataOFOrdeTab  

  FETCH NEXT FROM DEL_RelDataOFOrdeTab  INTO @ORDERID  
   WHILE @@FETCH_STATUS = 0  

     BEGIN   
       DELETE FROM AUTHORIZED WHERE ORDERID = @ORDERID  
       DELETE FROM AUTODISPALERTS WHERE ORDERID = @ORDERID  
       DELETE FROM DRIVER_REIMBURSEMENT WHERE ORDERID = @ORDERID  
       DELETE FROM FAXPOD WHERE ORDERID = @ORDERID  
       DELETE FROM GENERICFIELDS WHERE ORDERID = @ORDERID  
       DELETE FROM [Messages] WHERE ORDERID = @ORDERID  
       DELETE FROM ORDAUDIT WHERE ORDERID = @ORDERID  
       DELETE FROM OrderNotification WHERE ORDERID = @ORDERID  
       DELETE FROM OrderNotificationActions WHERE ORDERID = @ORDERID  
       DELETE FROM ORDERSPAID WHERE ORDERID = @ORDERID  
       DELETE FROM ROUTESERVERORDERS WHERE ORDERID = @ORDERID  
       DELETE FROM UnfinalizedOrders WHERE ORDERID = @ORDERID  
       print 'DELETING FROM ORDE_'  
       DELETE FROM orde_ where ORDERID = @ORDERID  
       PRINT @ORDERID  
     FETCH NEXT FROM DEL_RelDataOFOrdeTab INTO @ORDERID  
     END  

CLOSE DEL_RelDataOFOrdeTab   
DEALLOCATE DEL_RelDataOFOrdeTab  

DELETE FROM orde_ WHERE CLIENTID = @ClientID

Upvotes: 0

Views: 85

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can remove the CURSOR and just use a SUBQUERY.

DELETE FROM AUTHORIZED WHERE ORDERID IN (SELECT ORDERID FROM orde_ WHERE CLIENTID = @ClientID)
DELETE FROM AUTODISPALERTS WHERE ORDERID IN (SELECT ORDERID FROM orde_ WHERE CLIENTID = @ClientID)
DELETE FROM DRIVER_REIMBURSEMENT WHERE ORDERID IN (SELECT ORDERID FROM orde_ WHERE CLIENTID = @ClientID)
DELETE FROM FAXPOD WHERE ORDERID IN (SELECT ORDERID FROM orde_ WHERE CLIENTID = @ClientID)
DELETE FROM GENERICFIELDS WHERE ORDERID IN (SELECT ORDERID FROM orde_ WHERE CLIENTID = @ClientID)
DELETE FROM [Messages] WHERE ORDERID IN (SELECT ORDERID FROM orde_ WHERE CLIENTID = @ClientID)
DELETE FROM ORDAUDIT WHERE ORDERID IN (SELECT ORDERID FROM orde_ WHERE CLIENTID = @ClientID)
DELETE FROM OrderNotification WHERE ORDERID IN (SELECT ORDERID FROM orde_ WHERE CLIENTID = @ClientID)
DELETE FROM OrderNotificationActions WHERE ORDERID IN (SELECT ORDERID FROM orde_ WHERE CLIENTID = @ClientID)
DELETE FROM ORDERSPAID WHERE ORDERID IN (SELECT ORDERID FROM orde_ WHERE CLIENTID = @ClientID)
DELETE FROM ROUTESERVERORDERS WHERE ORDERID IN (SELECT ORDERID FROM orde_ WHERE CLIENTID = @ClientID)
DELETE FROM UnfinalizedOrders WHERE ORDERID IN (SELECT ORDERID FROM orde_ WHERE CLIENTID = @ClientID)
DELETE FROM orde_ WHERE ORDERID IN (SELECT ORDERID FROM orde_ WHERE CLIENTID = @ClientID)

Basically, it replaces the WHERE clause from

WHERE ORDERID = @ORDERID

to

WHERE ORDERID IN (SELECT ORDERID FROM orde_ WHERE CLIENTID = @ClientID)

Upvotes: 4

Related Questions