SQL Server Performance

Delete from a table ignoring a foreign key

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by jonesri, Dec 6, 2007.

  1. jonesri New Member

    I am looking to delete a single row from a relatively small table. Unfortunately, there is a foreign key relationship between this table and a much much larger table. The checking of this foreign key when I am deleting this row seems to significantly impact the performance of the operation. Previously there was an index on this larger table that helped this query run. This index has been dropped to improve the performance of a more frequently executed operation.
    Is there a way I can use a hint or something to stop SQL checking this foreign key when deleting the row? I am certain that there are no associated rows in the larger table.
    I have read elsewhere that I could disable the foreign key, perform the delete, then enable the foreign key. This delete statement is not a one off process and could happen in the normal operation of the application so I don't really know what the implications of doing this are.
    Any suggestions would be appreciated.
  2. FrankKalis Moderator

    I'm really suprised that a single index on a single column should have a that significant impact on the overall performance. No way to establish this index again?
  3. jonesri New Member

    The table is very large (around 56 million rows) compared with the table I am deleting from (around 7500 rows).
    The index could be put back, but it was removed to aid performance of another part of the application that is inserting into the larger table. This routine to delete a row is run very infrequently compared with the number of inserts that are done.
  4. ranjitjain New Member

    You can drop the constraint, delete rows and then re-create the same constraint. But if you remove any primary entry, you need to ofcourse delete all the corresponding child entries as well right? else it will not allow you to re-create such constraints
  5. jonesri New Member

    I am deleting the rows from the child table immediately before deleting from the parent table.
    I have considered disabling the foreign key but I am unsure as to the implications of doing this. It is highly likely that data will be being inserted into the child table while this operation is going on.
  6. jasonL New Member

    Something is not quite right:
    Application A perform bad WITH the FkeyX. Delete perform bad WITHOUT the FkeyX and you needed the foreign key constraint on FkeyX
    These are the options I can think of (in the order of preference)
    1. Put back the index FkeyX, troubleshoot the Application A to find out why it is bad (Could it be because there is another index with the same leading column of FKeyX on the BIG table causing the OPTIMIZER to pick the wrong index?)
    2. Remove foreign Key constraint and do the checking on the CONTRAINTS from the applicaiton standpoint.
    2. Change foreign key constraint to "dummy column X"+ FkeyX
  7. jasonL New Member

    ignore line
    2. Change foreign key constraint to "dummy column X"+ FkeyX

Share This Page