Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Speed up updates by disabling indices


Speed up updates by disabling indices

Author
Message
terry999
terry999
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 677
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
Lowell
Lowell
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23873 Visits: 39707
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!

GilaMonster
GilaMonster
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71888 Visits: 44958
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
terry999
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 677
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71888 Visits: 44958
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search