By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,195 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

insert into indexed table

P: n/a
Jay
Hi I have a huge table with over 100million records and on regular
basis ineed to delete nearly a million records and insert a million
records.
Currently I delete indexes before going through the process and
recreate the indexes which takes a very very long time.
IS there a way to disable indexes and re enable them after doing
insert and delete by reindexing or anything of that sort?
OR
Is there an approach to append two tables with similar indexes?

Please air you opinior, will mean a lot to me.
Thnaks
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
we**********@yahoo.com (Jay) wrote in message news:<95**************************@posting.google. com>...
Hi I have a huge table with over 100million records and on regular
basis ineed to delete nearly a million records and insert a million
records.
Currently I delete indexes before going through the process and
recreate the indexes which takes a very very long time.
IS there a way to disable indexes and re enable them after doing
insert and delete by reindexing or anything of that sort?
OR
Is there an approach to append two tables with similar indexes?

Please air you opinior, will mean a lot to me.
Thnaks

Jay,

You have a few problems to bear in mind when dealing with tables this
large and with such a turnover of data.

Firstly, as you've discovered, index rebuilds can take an age. I'd
consider partitioning this table if possible, either horizontally or
vertically. Books Online has a good discussion on partitioning and
ways to retrieve data from partitioned tables. The major benefit for
you would be that the index rebuilds will each be on smaller tables
(the member tables within the partitioned table), and may not be
required on all of these tables either.

You could also try rebuilding your indexes using the SORT_IN_TEMPDB
option - this can be particularly good if tempdb is on its own
separate device (which it should be, ideally). It allows you to
reduce disk I/O contention on the table's database, and can improve
index rebuild speed significantly.

Secondly, by deleting/re-inserting 1 million rows out of 100 million,
you may be severely skewing your table data statistics that the
optimizer relies on. I say "may be" because I don't know what rules
your deletes and inserts follow; if you're deleting and re-inserting
randomly throughout the table, then maybe it won't be so bad, since
you're only changing 1% of the data. If you're deleting 1 million
rows from one end of the table and re-inserting at the other, however,
this could make a substantial difference to the "spread" of the
statistical information.

By rebuilding your indexes, you will be partially correcting for all
this, since statistics will be updated at the same time (unless you're
using the STATISTICS_NORECOMPUTE option). However, these will only be
sampled statistics with a table of this size (i.e. they won't take all
rows into account, only a certain percentage of them). Furthermore,
the statistics are only computed (and maintained automatically
thereafter) for the leading column in any composite indexes. You can
improve on this by running the UPDATE_STATISTICS command and
specifying the INDEX option, (or COLUMNS if you want to also include
non-indexed columns), together with the FULL keyword, which will
recreate your statistics based on all the table data, not just a
sample.

For a table this size, you may want to consider disabling autostats
updates, too, since these can kick in at inconvenient moments, and be
quite an overhead on a table of this size. You may find it better to
disable the automated updates by using the STATISTICS_NORECOMPUTE
option when rebuilding the index, and to then schedule manual UPDATE
STATISTICS commands at a predictable time so as to give you more
control. If there is no suitable out-of-hours time to rebuild the
statistics manually, then it would be best to leave the auto update
option ON.
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.