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

Speed up updates by disabling indices Expand / Collapse
Author
Message
Posted Monday, May 20, 2013 10:23 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 116, Visits: 445
Hi

I need to update several tables which have the same field its a shopid.

e.g.
Update orders set Shopid =300 where shopid =3

Shopid is part of indices.

Will it make a big difference to disable the indices first.

I'm concerned about log file getting too big for the disk.

Thanks





Post #1454620
Posted Monday, May 20, 2013 10:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:24 PM
Points: 12,905, Visits: 32,180
terry999 (5/20/2013)
Hi

I need to update several tables which have the same field its a shopid.

e.g.
Update orders set Shopid =300 where shopid =3

Shopid is part of indices.

Will it make a big difference to disable the indices first.

I'm concerned about log file getting too big for the disk.

Thanks






if you disable the indexes, everything will slow down, because it has to do a table scan instead of an index seek to update the table, right?

your example is probably an over simplification of a real problem, maybe you can add some detail as to what the underlying problem is?


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1454629
Posted Monday, May 20, 2013 11:08 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: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
Bear in mind that if you disable the indexes you need to rebuild them afterwards. Unless you're doing a huge amount of inserts/updates, the cost of the rebuild (and logging impact) will likely be more than any gain (if there is one) by disabling them


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 #1454644
Posted Monday, May 20, 2013 5:11 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 116, Visits: 445
Thanks for replying.

Yeah that's what I'm getting at I'm essentially moving all one shop's data to another shop.

The PK will still be there but I thought about using the disable index for all other indices that have shopid in it.
(Odd table but clustered index is not the PK but the clustered index is on the datetime of sale its a warehouse type/reporting table so insert time not a concern)

I've not used disable before but read somewhere that it doesn't use the disk space up that a drop and recreate does.



Post #1454772
Posted Monday, May 20, 2013 5:50 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
terry999 (5/20/2013)
I've not used disable before but read somewhere that it doesn't use the disk space up that a drop and recreate does.


It will use the same disk space, disable's almost the same as drop, it just leaves the metadata of the index behind. To re-enable the index you have to rebuild it which is pretty much the same as recreating it, you just don't have to specify the index definition.



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 #1454779
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse