Breaking down a large table into smaller parts for better performa

[Michael]

I have a linked table for sales details it is about 500,000 records currently
and grows by about 30,000 records per month. I want to use this data to
create and publish reports and I have somewhat successfully done that but I
am always looking for a faster more automated way of producing these reports.
My main concern for this question is performance. My linked table contains
sales details from 2002 and I need to keep all that data but my primary
reports are generated from sales that occurred within the last year and those
that occurred within the last 3 months.

As my table has grown my queries take longer to run.

I am considering creating two new tables that have the exact same fields but
only contain either 1 years worth or 3 months worth of data. I would like to
update the data in these tables each time I open this database or by command
I am not sure which just yet. I would need the relationships related to
these tables to stay in force.

My linked table points to a text file that is updated daily.

I have several thoughts but I am not well enough trained in Access to know
which is the best.

I could do something outside of Access to filter the text file and just
write a new text file for each date range and then link tables to those, or
use a query to create a new table each time I execute the command or open the
database (there are several itterations of that one I have played with)

So basically I have this large linked table that I want to be able to query
in an efficient manner.

Any advice on this topic is appreciated.

Thanks

 

[Rick Brandt]

I have a linked table for sales details it is about 500,000 records
currently and grows by about 30,000 records per month. I want to use
this data to create and publish reports and I have somewhat
successfully done that but I am always looking for a faster more
automated way of producing these reports. My main concern for this
question is performance. My linked table contains sales details from
2002 and I need to keep all that data but my primary reports are
generated from sales that occurred within the last year and those
that occurred within the last 3 months.

As my table has grown my queries take longer to run.

I am considering creating two new tables that have the exact same
fields but only contain either 1 years worth or 3 months worth of
data. I would like to update the data in these tables each time I
open this database or by command I am not sure which just yet. I
would need the relationships related to these tables to stay in force.

My linked table points to a text file that is updated daily.

I have several thoughts but I am not well enough trained in Access to
know which is the best.

I could do something outside of Access to filter the text file and
just write a new text file for each date range and then link tables
to those, or use a query to create a new table each time I execute
the command or open the database (there are several itterations of
that one I have played with)

So basically I have this large linked table that I want to be able to
query in an efficient manner.

Any advice on this topic is appreciated.

Are the date fields in the table indexed? The size of a table should make
little difference performance-wise as long as your query/form/report is only
asking for a subset of the rows and the criteria used to select those rows
is applied against indexed fields.

 

[Michael]

No my table is not indexed. I am currently redoing my entire database from
ground up.

I do not know what is required to index a linked table? There is on
primary key for the table. The text file I link to is simply sales orders.

If I should index it would I first have to change my link specifactions to
add something like an auto number>?

 

[John Vinson]

I do not know what is required to index a linked table? There is on
primary key for the table. The text file I link to is simply sales orders.

Text files do not have indexes.

I'd strongly suggest importing the data into an Access Table with
appropriate indexes defined. You'll NEVER get decent performance on
this large of an external text file; it simply isn't designed as a
database file. In any search, Access must step through every single
record one by one; if you import it into a Table, the powerful JET
engine search tools can make it MUCH easier to handle.

John W. Vinson[MVP]

 

[Michael]

I had thought about this my concerns were having to do an import quite often
not every time I opened the database but at least the first time each day
that I open it. I want to make sure that I retain any relationships that are
on the table when I do an import. How would I ensure the relationships would
remain and could I write the date of import to a table and check that table

 

[John Vinson]

I had thought about this my concerns were having to do an import quite often
not every time I opened the database but at least the first time each day
that I open it. I want to make sure that I retain any relationships that are
on the table when I do an import. How would I ensure the relationships would
remain and could I write the date of import to a table and check that table
on startup against the current date to see if I needed to update again?

Is there ANY chance that you could simply maintain this data in Access
rather than in this humungous text file?

You would *not* typically wipe out the table and reimport everything;
it would be difficult to do so if you have other tables dependent on
this table. If you have "lookup" tables joined to this one, it would
be possible to run a Delete query to empty your master table and an
Append query to load it from the text file, daily (you should
certainly Compact the database daily as well if you use this method).
It would be very easy to add today's date to the import Append query,
by just putting Date() in a vacant Field cell and appending that field
to a date/time field in your table. Or, you could maintain a small
table of Imports and record the date in it using the same VBA code
which launches the append query.

John W. Vinson[MVP]

 

[Michael]

The text file at this point in time is needed. It is generated by our unix
system. I can't touch the data tables in there directly. I have tried just
updating with previous days sales but that never worked out. If the unix box
didn't export correctly there was a lot of manual work to be done to correct
errors. Once I was out of town and the power went out so the schedule didn't
run. I decided to have it all there all the time. There may be a better way
but I am not experienced enough to figure it out just yet.

 

[John Vinson]

Are the date fields in the table indexed?

It's an external Text file. No, in other words!

John W. Vinson[MVP]

 

[John Vinson]

It is generated by our unix
system. I can't touch the data tables in there directly. I have tried just
updating with previous days sales but that never worked out. If the unix box
didn't export correctly there was a lot of manual work to be done to correct
errors.

I'd imagine this would be a problem with the text file just as much as
with a table! If the text file isn't exported correctly, you're in
trouble no matter what, no?

I don't know what to suggest, since apparently the only reasonable
options have been rejected. You cannot index a text file, and
therefore cannot search it efficiently. You can certainly work with a
subset of the file, but apparently you've rejected that too. Sorry!

John W. Vinson[MVP]

 

[John Nurick]

Hi Michael,

Sounds like the textfile is generated at regular intervals (nightly?) by
the Unix system and you need to use the latest version for your reports,
but never need to update it yourself.

In that situation, if your Unix db admin is friendly, you could ask
him/her to generate you two files each night: the big one, and another
that just contains records for the last three or 12 months. Then point
your queries at one or other of the text files as appropriate.

If you can't get cooperation, you could do it yourself, with a script
that runs every night after the Unix system has done its stuff, reads
through the textfile, and writes the recent records to a separate file.