SQL Server Performance Forum – Threads Archive

delete performance question??

i have table Controller (ControllerID, ControllerNAme, isActive)
and tables SendControllerForCitizenClaim (SendID, ControllerID, Region),
table SendControllerForCompanyClaim (SendID, ControllerID, REgion)..
and so on .. i have 14 tables of SendController each for a certain purpose. I have a procedure for deleting controllers. I want that if a controller is sent (i.e its id is in at least one of the tables) then isActive=0 otherwise if the controller id isn’t present in neither of the 14 tables then delete controler record. How do i do that wihout having to write 14 If Not exists statements??
which method is better and faster in performance? these 14 tables will contain a lot of rows.

http://www.sql-server-performance.com/stored_procedures.asp
IN order to gain the performance of the delete in the tables with massive rows, if possible delete the indexes until the delete operation is finished and then re-create the indexes on those tables. Executing the statement in blocks will have better performance too.. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

There is no way without restructuring your data in a different way. What you’ve describer is a classic supertype-subtype relationship which you’ve chosen to implement as separate subtype tables. The alternative is to have a single subtype table with the columns SendID, ControllerID, REgion, typeID where typeid is what ever you want it to be to distinguish the various tables. Then you’d have only one if exists statement, and with the right indexes you wouldn’t notice any drop in performance Cheers
Twan

]]>