Hello,
We maintain a 175 million record database table for our customer.
This is an extract of some data collected for them by a third party
vendor, who sends us regular updates to that data (monthly).
The original data for the table came in the form of a single, large
text file, which we imported.
This table contains name and address information on potential
customers.
It is a maintenance nightmare for us, as prior to this the largest
table we maintained was about 10 million records, with less
complicated updates required.
Here is the problem:
* In order to do the searching we need to do on the table it has 8 of
its 20 columns indexed.
* It takes hours and hours to do anything to the table.
* I'd like to cut down as much as possible the time required to update
the file.
We receive monthly one file containing 10 million records that are
new, and can just be appended to the table (no problem, simple import
into SQL Server).
We also receive monthly one file containing 10 million records that
are updates of information in the table. This is the tricky one. The
only way to uniquely pair up a record in the update file with a record
in the full database table is by a combination of individual_id, zip,
and zip_plus4.
There can be multiple records in the database for any given
individual, because that individual could have a history that includes
multiple addresses.
How would you recommend handling this update? So far I have mostly
tried a number of execution plans involving deleting out the records
in the table that match those in the text file, so I can then import
the text file, but the best of those plans takes well over 6 hours to
run.
My latest thought: Would it help in any way to partition the table
into a number of smaller tables, with a view used to reference them?
We have no performance issues querying the table, but I need some
thoughts on how to better maintain it.
One more thing, we do have 2 copies of the table on the server at all
times so that one can be actively used in production while we run
updates on the other one, so I can certainly try out some suggestions
over the next week.
Regards,
Warren Wright
Dallas