Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Index rebuild before table update Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 5:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 8:04 AM
Points: 179, Visits: 635
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
Posted Thursday, March 21, 2013 6:26 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:54 PM
Points: 42,434, Visits: 35,488
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
Posted Thursday, March 21, 2013 6:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 8:04 AM
Points: 179, Visits: 635
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
Posted Thursday, March 21, 2013 6:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:54 PM
Points: 42,434, Visits: 35,488
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
Posted Thursday, March 21, 2013 2:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1434014
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse