473,245 Members | 1,758 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,245 software developers and data experts.

insert into indexed table

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
1 6037
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Eric | last post by:
I'm trying to insert data for a real-time application that demands less than 3 second receipt-to-commit time, has a relatively large (500,000 records/hr) data rate, and requires access times on the...
4
by: MAB71 | last post by:
I'm running an ISP database in SQL 6.5 which has a table 'calls'. When the new month starts I create a new table with the same fields and move the data of previous month into that table and delete...
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
5
by: Paul Shaw | last post by:
Can anyone explain why an insert might cause multiple logical bufferpool data reads? Here's a situation that has me scratching my head. Table A's data resides in tablespace B Table A's...
8
by: Carl | last post by:
Hi, I hope someone can share some of their professional advice and help me out with my embarissing problem concerning an Access INSERT query. I have never attempted to create a table with...
8
by: nano2k | last post by:
Hi Shortly, I keep invoices in a table. Occasionally, someone will fire the execution of a stored procedure (SP) that performs several UPDATEs against (potentially) all invoices OLDER than a...
2
by: Danyork | last post by:
Hello, I'm relatively new to VBA, and was wondering if someone would help me debug the code below. I want to put it in a form, so that when I click "OK" it inserts a new record into the...
4
bugboy
by: bugboy | last post by:
I'm inserting a new word into table 'w' and a definition into table 'c' which are linked in table 's' which is the relation table for the many to many relationship between 'w' and 'c'. I've been...
0
by: Tawfiq | last post by:
Hi, I have got the following situation please give me some ideas how to solve/work around it. Current situation: Everyday day about 10 million records are processed and bulk inserted in...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.