SQL Server Performance

Update Performance

Discussion in 'SQL Server 2005 General Developer Questions' started by fkmbkk, May 31, 2011.

  1. fkmbkk New Member

    Question: Does updating a table with 200 fields is slower than updating 10 fields table ? (all fields is same size). And the update here is just affecting a single field.

    Thank you.
  2. fkmbkk New Member

    OOps sorry this is sql 2005 ..but i think this is very general and applies to all.
  3. FrankKalis Moderator

    Welcome to the forum!

    It depends... :)
    If the UPDATE statement has no WHERE clause and thus affects all rows, then yes, most likely it will be slower because the wider table is spread over more data pages that have to be read and changed. If the UPDATE affects only a couple of rows which can easily be located by an index, then the difference shouldn't be that huge, if noteable at all.

    You can easily test this in your own environment or is this an interview question?
  4. FrankKalis Moderator

    Move the thread to the 2005 section...
  5. fkmbkk New Member

    It involves a lot of rows like 10% out of 6million rows.

    I have tested this..and now in the process of narrowing the columns. I just want to confirm what i tested.
  6. fkmbkk New Member

    So the update process is like this ?
    Read the page, update a single field and write the whole page ?
  7. FrankKalis Moderator

    Yes, basically that's it. The read can happen in memory or from disk, then the update is performed, log entries are recorded, the data page is written to stable media. At least, i f I recall it correctly. :)

    You might want to check these out:
    SQL Server 2000 I/O Basics
    SQL Server I/O Basics, Chapter 2

    If the schema is still evolving, you might want to narrow the table(s) as much as possible, provided that this is an OLTP database.
  8. fkmbkk New Member

    Great thank you very much...cause this is the first time i deal with > 1 million rows...
    Downloaded my client's customers base to my Notebook :)
  9. FrankKalis Moderator

    Hehe, sometimes I wish I would deal with 1 million (only). Currently we process between 20 - 25 million rows a day.
    "Just" one million rows is not considered a big table any more these days. :)
  10. fkmbkk New Member

    > 25 million new rows per day ??? whats the nature of the application ?
    My new job will require this ....
  11. FrankKalis Moderator

    It's a risk management system for Investment Banking. Most data is throw away after x amount of time, but still we increase by a few million rows a day. Nothing that the server could not handle though.
  12. fkmbkk New Member

    Thats cool.
    Add to my question above. If a single row is 3 page big. Does it mean SQL needs to read 3 page before it can update a single field ?
  13. FrankKalis Moderator

    A row can't span multiple pages. Under certain circumstances some data can be pushed to a "row overflow area", but I guess that's a different story.

Share This Page