Home Forums SQL Server 2008 T-SQL (SS2K8) Is there any obvious performance difference between updating 1 column in a 4 column wide table vs 1 columns in a 1000 column wide table ? RE: Is there any obvious performance difference between updating 1 column in a 4 column wide table vs 1 columns in a 1000 column wide table ?

  • December 17, 2017 at 11:10 am

    #1972404

    Jonathan AC Roberts - Sunday, December 17, 2017 10:12 AM

    All other things being equal it will take longer to update the table with more columns. If only because there is going to be more disk IO in reading the data. Also if a table has 1000 rows it's quite likely that your updates will cause page splits, which would also slow down updates.

    It's not just the reads from disk, which will only occur if the data isn't already cached, and testing makes it look like both a narrow and wide table will perform the same when that occurs because people forget about the eventual writes that must occur.  It's the eventual writes to disk that are the real killer even after the table is cached and most people don't measure that or take it into account in their testing.

    I absolutely agree on the page splits becoming a potentially major performance and memory issue if the update causes expansion of data in variable width columns.

    That being said, one good test is worth a thousand of our expert opinions and I'm working on a demonstration where everything is identical in two tables except one table has a CHAR(1000) column to simulate 100 additional columns.  I'll post it all soon.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)