Slow select after update of records

  • Hey all,

    I have a table with about 35 million records. Using a date and a unique identifier I have been experiencing great performance on selects.

    I had to do an update of a large number of those records and sicne then my select performance has gone down dramatically.

    Im thinking it has something to do with the indexes? While I did not change them is there some kind of refresh I need to do after UPDATEing a table this size?

    Just wanted to mention I did have the need to add a few columns to this table. I didnt see the performance change until after I did the updates.

    Thanks

  • If possible rebuild the indexes and then update statistics. Or just update statistics will help.

  • Adding columns won't normally cause a performance change. Adding data to the new columns will. Chances are, you have page splits all over the place. I'd recommend doing a rebuild on the clustered index and then rebuilding the other indexes.

    --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)

  • was the updates done on columns that are index? you did not mention if you ran update stats after the update.

  • cconnors (9/1/2009)


    was the updates done on columns that are index? you did not mention if you ran update stats after the update.

    That won't matter if it's the clustered index.

    --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)

  • I didn't get it where the columns were indexed. I got that the columns were a date and a unique identifier. But perhaps the indexs for table could should be re-built after that update.

    I'm wondering if he wouldn't see improved performance if he did index one of these columns he's using for his SELECT.

    I'm also wondering if a non-clustered index wouldn't provide him better perfomance for the size of a table he has.

    😉

  • RSage (1/13/2010)


    I didn't get it where the columns were indexed. I got that the columns were a date and a unique identifier. But perhaps the indexs for table could should be re-built after that update.

    I'm wondering if he wouldn't see improved performance if he did index one of these columns he's using for his SELECT.

    I'm also wondering if a non-clustered index wouldn't provide him better perfomance for the size of a table he has.

    😉

    Either way, page splits can cause slowness when you do a SELECT on a table.

    A index reorganize after an large update should help..not to mention updating the statistics too...

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • Key point that many miss:

    If you do a REORG of index, you MUST update the statistics manually.

    If you do a REBUILD of index, you must NOT update the statistics manually - you will get WORSE statistics if you do so and do not specify FULLSCAN. If you do specify FULLSCAN then you have simply wasted effort and time for no gain.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply