Index rebuild before table update

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Index rebuild before table update

Index rebuild before table update

Rate Topic

Display Mode

Topic Options

Author

Message

bugg

Posted Thursday, March 21, 2013 5:26 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 123, Visits: 402

There is some front end code that insert around 40000+ rows into a table then runs an update to all columns on all the rows.

Would it be okay after the insert to do an index rebuild before the update?

So i would create a sproc which first rebuilds indexes then runs the update command?

Post #1433730

GilaMonster

Posted Thursday, March 21, 2013 6:26 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 37,727, Visits: 29,988

bugg (3/21/2013)

Would it be okay after the insert to do an index rebuild before the update?

Is the index rebuild necessary?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1433750

bugg

Posted Thursday, March 21, 2013 6:34 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 123, Visits: 402

GilaMonster (3/21/2013)

bugg (3/21/2013)

Would it be okay after the insert to do an index rebuild before the update?

Is the index rebuild necessary?

Possibly not its, the index at 97% fragmented after the insert though. So i thought a rebuild would help speed up the update.

Post #1433753

GilaMonster

Posted Thursday, March 21, 2013 6:57 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 37,727, Visits: 29,988

It might. Test it, test a stats update and see which if either makes a noticable difference

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1433763

opc.three

Posted Thursday, March 21, 2013 2:00 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 6,720, Visits: 11,761

With limited insight into the overall process this may sound like I am asking a silly question so forgive me please, but why not push all the necessary data changes to the table in the INSERT? i.e. why INSERT 40K rows then turn around and immediately update them all?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1434014

« Prev Topic | Next Topic »

Permissions