Speed up updates by disabling indices

Question

Speed up updates by disabling indices

terry999

SSCertifiable

Points: 5033
More actions
May 20, 2013 at 10:23 am

#294163

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

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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, MVP, M.Sc (Comp Sci)
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

terry999 SSCertifiable Points: 5033 More actions · Answer 3

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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, MVP, M.Sc (Comp Sci)
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