Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004
    Posts
    16

    Unanswered: SP Update overhead

    As part of dealing with a locking problem I am fine tuning a stored procedure
    that updates a table. The application updates a row by changing every single column except for the primary key, whether 1 or all of the columns have
    been modified. Although easier to code, this strikes me as taking a sledge hammer to a nut.

    Could anyone tell me if there is any benefit in attempting to break this down. That is, code the stored procedure so that only those columns being changed are modified. I am thinking this might reduce dramatically the overhead of writing to the transaction log and making the changes to the actual row.If the benefit is non existant (or insignificant) because of the way Sql Server updates a row it will obviously be a waste of time to generate
    dynamic sql.

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Generally a waste of time.

    SQL Server does not update records. It rewrites the entire record and then deletes the original.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Oct 2004
    Posts
    16
    Thats all I needed to know, thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •