Slow select after update of records

Question

Post reply

Slow select after update of records

bwild-946238

Valued Member

Points: 57
More actions
August 30, 2009 at 5:48 am

#138517

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

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

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

Boolean_z SSCommitted Points: 1807 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

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)

cconnors-1038947 Grasshopper Points: 10 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

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)

RSage Mr or Mrs. 500 Points: 522 More actions · Answer 5

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.

😉

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 6

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."

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

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