Index : Reading and Writing from the same table

Question

Hi Everyone

I have a table that I read data in and then I have batch inserts in the same table. I compute data from the existing data thats in the table and insert it in the same table, and then when the insertion done, compute the data again based on the new data and insert.

The problem is, I delete the clustered index before all this computations and insertions are done on the table.

Now all the insertion and computations are done from a table taht has no index on it.

My question is, how can I have an index to read the data, but when I am inserting, if we can avoid the index to improve query performance.

Help pls..

Thanks

-Sarah

Answer 1

Sarah, you either have indexes and get the good and bad side-effects, or you have no indexes and get the good and bad side-effects.   You have to choose.

But it is not all or nothing.  You mentioned dropping the clustered index.  Do you have other indexes on the table?  Do also drop those indexes?  Is your clustered index on a simple number column or a more complex group of columns?  Do the values of the clustered index column ascend (relatively) sequentially?

Of course, if you drop all the indexes, get them re-established before trying to do the computations if possible.  If that is possible then your pattern would be:

1. Do initial computations.
2. Drop indexes.
3. Insert data.
4. Recreate indexes useful for computation.
5. Redo computation.
6. Recreate remaining indexes.

Some index suggestions:

1. If possible, have the clustered index on a relatively narrow column (INT or BIGINT) with ascending values and do not drop that index.   (If you cluster on a GUID column, it will cause more performance problems due to the size and the pattern of insertions.)
2. If you have non-clustered indexes, drop as many of them as do not help the load and recompute process. 
3. Do some tests to determine if it is better to drop and recreate all indexes, or to keep essential indexes active during the whole process. (Check the timing, I/O, and CPU load.)

All the best,
RLF