SQL Server Performance

why update one row is so slow?

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by gavin, Aug 9, 2007.

  1. gavin New Member

    A table has 200000 rows.When I update one row with primary key, it cost 0.2s. I checked the excute plan, and found something can not be understood.
    In the excute plan. the clusted index update cost 1%, but before clusted index update there is a nested loops(inner join).seems constant scan->compute scalar then clusted index seek.The clusted index seek cost 99%.
    Because I use the primary key to update, I think the clusted index update is enough. What is the use of constant scan and compute scalar?
    Hope get some help from you. Thanks.
  2. Adriaan New Member

    Updating an existing PK value is usually a bad idea. Once the data is recorded, the PK should be 'set for life'. If you're using a real-life value for the PK, like a client code, you should only need to change the PK if there's a big overhaul of the system how those key values are formatted, so this type of update should not be an everyday occurrence.
    What the execution plan cannot and will not tell you, is that there are probably FK relationships depending on this PK, with cascading updates. These updates also take time, and they not are just not on the map of the execution plan. For one-row updates, I wouldn't bother too much with checking execution plans in any detail.
    Furthermore, if your PK is the parent in one-to-many relationships, and the child tables have a clustered index on the FK and its own key, then that clustered index will be updated and has to be re-arranged (being a clustered index).
    Add this all up, and you find that you should use a unique constraint on the natural key, and use an identity column as the PK, also for FK relationships.
  3. satya Moderator

    This post is locked as further discussion were held in thispost

Share This Page