SQL Server Performance

Delete Records from Parent/Child Table

Discussion in 'Performance Tuning for DBAs' started by Monika, Mar 23, 2006.

  1. Monika New Member

    Hi
    I have 2 tables(parent & Child) having millions of records.
    I want to delete some records from the tables.I have tried 2 approaches.

    1. First I have deleted the records from child table & id of child table that is linked to parent table has been stored in temporary table.Now I am trying to delete the rows from parent table by joining temporary table,but this query is taking more than 15 mins because this parent table is referenced by 2 foreign tables.

    2. I have dropped the constraint and then delete the records from parent & child tables
    and again adding the constraint,but adding constraing is taking 7 mins.

    Could you please suggest me better method..

    Thanks
    Monika Sharma

  2. Twan New Member

    Hi ya,

    Make sure that you have non-clustered indexes on any foreign keys in large tables

    Cheers
    Twan
  3. Monika New Member

    Thanks Twan
    You suggested a best solution. But creating nonclustered index on a foreign keys is also taking very long execution time.Is there any way to make fast this?

    Thanks
    Monika Sharma


  4. FrankKalis Moderator

  5. satya Moderator

    Well spotted Frank, that thread has been locked.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page