May 13, 2008 at 4:50 am
I have a script that appends (INSERT INTO) records from a staging table to a production table that has many indexes on it. At the end of the script I refresh all the indexes automatically for that table (using DBCC DBREINDEX(tblName,'').
Is there a way to have the indexes update automatically during the INSERT INTO statement, so that I don't have to run the DBREINDEX statement?
I realize this might not be optimal from a performance standpoint, but would like to test it anyway, do some comparisons, etc.
Is there a setting that lets me do this?
May 13, 2008 at 6:05 am
I have a script that appends (INSERT INTO) records from a staging table to a production table that has many indexes on it. At the end of the script I refresh all the indexes automatically for that table (using DBCC DBREINDEX(tblName,'').
Is there a way to have the indexes update automatically during the INSERT INTO statement, so that I don't have to run the DBREINDEX statement?
I realize this might not be optimal from a performance standpoint, but would like to test it anyway, do some comparisons, etc.
Is there a setting that lets me do this?
RD - in answer to your question the indexes will automtically 'be updated' when the insert happens, that is the new data will be included in all the indexes on that table.
Without going into detail on how indexes work many new inserts on a given table can cause the indexes to become fragmented and then not used in future query plans as a table scan is deemed more efficient. rebuiling the indexes at the end of insert script ensures that fragmentation is minimised and the index remains useful...Hope this helps
Gethyn Elliswww.gethynellis.com
May 13, 2008 at 6:59 am
Very helpful, thanks Ellis.
May 19, 2008 at 2:20 pm
DBCC INDEXDEFRAG is command to defrag an index.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply