如何提高查询性能以更快地删除子表记录

Question

我有一个表Order，在这里我想通过clientId删除数据。此表每个客户端有超过2071458条记录。这个表有16表的外键引用。Wo当我尝试按clientid删除记录时，删除再皮质需要2-4小时。那么，我如何提高性能，以更快地做到这一点。如果是，我可以使用子查询，如何在此查询中使用。下面是我正在使用的查询。

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   

Answer 1

您可以删除CURSOR，只需使用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)

基本上，它替换了WHERE子句

WHERE ORDERID = @ORDERID

至

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