Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Hopefully the title describes this sufficiently, but I have a process that runs and performs thousands of individual inserts and updates (nothing I can do to change that). My questions are:

1. Is it worthwhile to disable all the indexes on the tables before the import, and rebuild the indexes afterwards

2. Is it worthwhile (and is it even possible) to disable SQL auditing during the import and enable it when the import is complete.

The import is a binary operation, so either it works or it doesn't. It's also done on off-hours, so I'm not concerned with keeping either indexes up to date, or keeping auditing data.

share|improve this question
    
What is your PK? Can you give a sample of the updates/inserts? If they use filters then leaving some indexes on may help performance. –  JNK May 10 '12 at 13:47

1 Answer 1

up vote 3 down vote accepted

Disabling indexes is a popular method when inserting a bulk amount of data. So if you are looking to get this done in the shortest amount of time (your window isn't long enough for the with-index import??), simply do this:

alter index YourNonClusteredIndex
on YourDestinationTable
disable
go

Do this for your nonclustered indexes.

Granted, when you disable your indexes (provided you want to continue to use them) to enable then you'll need to rebuild or recreate them. So there is that time-consuming side effect.

Another consideration pointed out by @JNK:

Just disabling indexes on the table may be an issue if he uses any of those indexes to find the row to update or check for a previously inserted row.

In other words, whatever you choose you should be testing to ensure you're getting the gains you are looking for.

As for auditing, you need to be more specific on what type of auditing you are using. If you have triggers on your table, and that is your auditing design then you can also disable all of the triggers on your table by doing the following:

disable trigger all
on YourDestinationTable
go

Disabling the triggers on a table could potentially save you a lot of time on your insert (or whatever action fires the trigger[s]).

share|improve this answer
    
OP should bear in mind the tradeoff with UPDATES or conditional inserts, though. Just disabling indexes on the table may be an issue if he uses any of those indexes to find the row to update or check for a previously inserted row. –  JNK May 10 '12 at 13:51
    
@JNK Great point. If it's ok with you, I'm going to quote that comment in my answer's edit. –  Thomas Stringer May 10 '12 at 13:53
    
Don't mind at all. As long as OP and future visitors get the nuance :) –  JNK May 10 '12 at 13:54
    
this definitely makes sense. I guess I'll have to try both out in a test bed and see which holds the advantage. Thanks to you both! –  John Williams May 10 '12 at 14:01
    
@JohnWilliams You can also just disable specific indexes. I do this in some of my builds. I use a rowId to find which rows have been modified already, so I disable every OTHER nonclustered index but that one. –  JNK May 10 '12 at 14:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.