2012년 4월 20일 금요일 오후 11:17
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.
2012년 4월 24일 화요일 오후 2:52
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:
- Do initial computations.
- Drop indexes.
- Insert data.
- Recreate indexes useful for computation.
- Redo computation.
- Recreate remaining indexes.
Some index suggestions:
- 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.)
- If you have non-clustered indexes, drop as many of them as do not help the load and recompute process.
- 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,
- 답변으로 제안됨 koles 2012년 4월 25일 수요일 오전 7:41