LARGE DB HANDLING QUESTION

[uriah]

In handling multiple large DB is it better/more efficient to query the final union query or to 'make tables' and query a (humongous) consolidated DB? I think Access tops out at 1 gig.? Thx...

 

[Norie]

I think querying a table would be quicker.

But creating the table would obviously take some time.

 

[uriah]

Thanks N.

Yes, especially at end of year when their are 12 files to be consolidated
or 11 separate union queries. This is all a big hmm. But, yes, as you
said, I might go the query route to save on server space, if the union
queries don't hit a memory ceiling on the 9month of the year or so.

Uriah

 

[Norie]

Why do you have 12 different files?

Is it one file per month?

Surely you would be better with one file covering the whole year?

By the way do you actually mean a table when you say file?

 

[uriah]

N. Thanks for your interest.

Technically the file is recreated nightly and a new one at the end of each
month from a flat file on as400 box created by VB from an outside
vendor. So, am stuck with the 1 creation a month.
Since the data comes from plc's from a plant floor,
there is not much control there.

Roughly there are 40 datapoints per minute, or 57,600 records
per day. A month final file would be about 1,700,000.

Do you think it would be in the best interest to create 1 single table
from the various months or like we/you said earlier query it now
that you know the size.

thx again.............Uriah

 

[Norie]

Is this file a text file that you are importing into Access?

 

[uriah]

There is a (compiled) program that uploads the new
data table nightly to the Access Db. Cannot touch that
routine, purchased services, etc.

tks

T

 

[uriah]

reply; Noria; Large Db Handling; More

Noria

What did you think about a consolidated db at 24million records per year
versus the union qry of 12 separate. I did not see a reply. If you do not have one, you can say, unknown. That is ok. The Msacces limit is a 1-gig I believe,
and it would be way under that.

Thx again

Uriah

 

[Andrew Fergus]

Hi Uriah

The table size limit is apparently 1 Gigabyte for Access 2K & prior, or 2Gb for XP & 2003 version - see here.

If you had all 12 months records in one table would you expect to run detailed queries on this table? By detailed I mean right down to individual records? Or would you be looking for summarised data, perhaps daily or weekly? If this is the case then you could create a new "master" table with data summarised from the 12 detail files. Maintenance of the main table aside, this would speed up any queries where you were looking to analyse summarised data.

HTH, Andrew.

 

[firemark1817]

Appending data to one table may be most efficient

If you design the table with Indexes and run monthly append queries you'll get faster performance than trying to Union the separate tables.

You can put this table in it's own database to minimize the impact of the size limit 1GB or 2GB depending on your version. When you write the Append query make sure you select to Append to a different database.

Other than that, the best way to boost performance is more memory. I recently upgraded my PC from 512MB to 1GB can cut process time by better than 50%.

Last option if this doesn't work is to put the table in SQL Server where you can still maintain and query via MS Access.