sql server programming: Disable Index in mass insert, update & delete -- sql server programming

RE: Disable Index in mass insert, update & delete

Alejandro Mesa
6/14/2007 11:24:03 AM

sql server programming:

bzh_29,

Which version of SQL Server?

For 2000 you will have to drop the indexes and recreate them after the
synch. You can not drop indexes related to "unique" or "primary key"
constraints without dropping first the constraint.

For 2005, check "alter index" in BOL. Be careful disabling a clustered index
and / orindexes related to "unique" or "primary key" constraints, it also
disables the constraint. You will have to rebuilt those indexes after the
synch has finished and enable any constraint related to them.

If you have the book, Inside Microsoft SQL Server 2005: The Storage Engine,
by Kalen Delaney, I think it will help to read read in "Chapter 7 - Index
Internals and Management" about "Table-Level vs. Index-Level Data
Modification".

AMB

[quoted text, click to view]

Disable Index in mass insert, update & delete

bzh_29
6/14/2007 3:45:28 PM

Hi,

One of my apps synchronise two databases. In some case, the number of
elements could be important ( > 10 000) for my area ... Elements are
insert, update or delete depending on what happened on the first
database ...

In order to accelerate SELECT on some tables, indexes has been created
but as soon as they've been created, synchronisation times
increase ... ( 22 sec to 80 sec ...)

[quoted text, click to view]
updates or deletes ...

I'm wondering if there is a way to tell to SQL Server to stop updating
index until the synchronisation is finished ...

Thanks for any help!!!

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.