SQL Server Performance

Update Statement using extra FK's

Discussion in 'SQL Server 2005 General Developer Questions' started by Arhamg, Jul 2, 2009.

  1. Arhamg New Member

    I have a simple update statement like
    Update tableName
    Set Col1 = val1, col2 = val2, col3 = val3
    where TablePK = Val
    The table in question has some 25 columns and some 15 foreign keys. When I see the execution plan of the above update statement I find that there is a Clustered index seek for each of the FK's.I'm concerned because each of the branch showed 3% cost which on accumulation is very high.
    What I dont understand is, when I'm not changing ALL the columns then why are ALL the FK being referred?
    Is there a way to stop this from happening?
    Is there a change on SQL Server 2008? I'm using 2005.
  2. Adriaan New Member

    That's why we have FKs - data integrity. Whatever you changes you try to make, the FK constraint is checked.
    You might try implementing/faking the FKs through triggers, so only the FKs are checked that are relevant for the columns that are actually updated.
  3. Arhamg New Member

    Thanks for your reply Adriaan.
    I know the logic behind FK but my doubt is only those FK's should be referred whose column I'm updating and not ALL of them. I mean in my example 3 FK's.
  4. arunyadav Member

    Hello Arhamg,
    I am sure you know about Deleted/Inserted tables in SQL Server. The deleted table stores copies of the affected rows(not column) during DELETE and UPDATE statements. During the execution of a DELETEor UPDATE statement, rows are deleted from the target table andtransferred to the deleted table then the whole row is again inserted and not just the particular column. So while inserting, all the FK's are again referred. Please look at the below link to understand more about these tables.
    Inserted/Deleted tables
  5. Arhamg New Member

    Thanks for yor reply Arun.
    I had thought of the same explanation but needed a confirmation from someone else.
    Just from the top of my head: Is it really difficult for Microsoft SQL Server to stop this happening? Cant they identify an insert statement from an update statement and fire only required FK's.
    I mean this would make a lot of queries faster.
  6. Adriaan New Member

    In a trigger, you can use the IF UPDATE(column_name) syntax to selectively do the verification for updated columns only.
    Note that the check constraints fire before any triggers.

Share This Page