Move old records to other table

[Ray]

I have a table that grows bigger and bigger year by year. Is there any way
to move the old records to a new table automatically to make the overall
operation more efficient? Your advice is appreciated.

Thanks,

Ray

 

[John W. Vinson/MVP]

I have a table that grows bigger and bigger year by year. Is there any way
to move the old records to a new table automatically to make the overall
operation more efficient? Your advice is appreciated.

How big is "very big"? Tens of millions of records? With proper indexing and
query design, even a table of that size can be quite efficient. Also, just
moving data to another table in the same .mdb will often not provide much
benefit: the overall size of the database doesn't change. it's better to
move it out of the .mdb file altogether, into another linked database.

That sais... you will need to run an Append query (with an appropriate date
criterion) to migrate the data, folloeed by a Delete query to remove the
records from the production table. Be sure to Compact the database after
this operation in order to recover the space formerly occupied by the
deleted records. These two queries can be run from VBA code, and should be
run within a Transaction to ensure that they either both run, or neither
does. See "Transaction" in the help file for an example.

John W. Vinson/MVP

 

[Ray]

John,

Thanks for your useful advice.

Very big means about 20-year data and most of them never or seldom used
again. We have front end and back end database that the front end is
located in the pc of each user and the back end is located in a Windows 2003
server. Do I need to move the split table to other database under this
design?

The users take about 10 to 30 seconds to complete a query and feel too long.
You mentioned proper indexing and query design will help the efficiency. Do
you have any idea where I can find out the best practice of doing proper
indexing and query design? So I can examine the existing design of the
database to see where can be improved. The database was re-designed since
from Lotus 123, dBase.

Thanks,

Ray

 

[John W. Vinson/MVP]

Thanks for your useful advice.

Very big means about 20-year data and most of them never or seldom used
again.

ok... but how big in megabytes, or records?

We have front end and back end database that the front end is located in
the pc of each user and the back end is located in a Windows 2003 server.
Do I need to move the split table to other database under this design?

In order to actually save space in the backend database and gain back the
overhead, I'd suggest creating a second backend database on the server.
Create a copy of your current table in this backend, design mode only. If
you wish, you may want to add a field DateArchived so that you can keep
track of what was archived when.

The users take about 10 to 30 seconds to complete a query and feel too
long. You mentioned proper indexing and query design will help the
efficiency. Do you have any idea where I can find out the best practice
of doing proper indexing and query design? So I can examine the existing
design of the database to see where can be improved. The database was
re-designed since from Lotus 123, dBase.

Optimization is equal parts science and black magic, with a liberal dose of
"try it and see what works". In general, all fields which are used for table
linking, for searching, and for sorting should be indexed appropriately.
Watch out for Access' automatic indexes - e.g. any key used as a foreign key
in a relationship is automatically indexed, so there's no need to create
another index. But too many indexes can damage *update* performance at the
same time that they're improving search performance. Occasionally I've seen
marked improvement by opening the table in design view; removing all indexes
other than the Primary Key; compacting the database; and recreating the
indexes and compacting again.

If you indeed need to archive (as sounds plausible), BACK UP your database
first of course. I'd use File... Get External Data... Link to link to the
archive backend, and run the Append and Delete queries suggested; then
compact the backend.

John W. Vinson/MVP

 

[Ray]

John,

Thanks a million!

Ray

ok... but how big in megabytes, or records?


In order to actually save space in the backend database and gain back the
overhead, I'd suggest creating a second backend database on the server.
Create a copy of your current table in this backend, design mode only. If
you wish, you may want to add a field DateArchived so that you can keep
track of what was archived when.


Optimization is equal parts science and black magic, with a liberal dose
of "try it and see what works". In general, all fields which are used for
table linking, for searching, and for sorting should be indexed
appropriately. Watch out for Access' automatic indexes - e.g. any key used
as a foreign key in a relationship is automatically indexed, so there's no
need to create another index. But too many indexes can damage *update*
performance at the same time that they're improving search performance.
Occasionally I've seen marked improvement by opening the table in design
view; removing all indexes other than the Primary Key; compacting the
database; and recreating the indexes and compacting again.

If you indeed need to archive (as sounds plausible), BACK UP your database
first of course. I'd use File... Get External Data... Link to link to the
archive backend, and run the Append and Delete queries suggested; then
compact the backend.

John W. Vinson/MVP