SQL Server Performance

delete performance question??

Discussion in 'T-SQL Performance Tuning for Developers' started by mariechristine, Aug 6, 2004.

  1. mariechristine New Member

    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.
  2. satya Moderator

    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.
  3. Twan New Member

    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

Share This Page