Table too large/slow 600,000 records w/ 100 columns

[Peter]

I work for a State University and within our office we use Access all the
time. But whenever we deal with our extremely large access table our machine
gets bogged down. Is there a way to juice up access, so it can handle this
amount better? Whenever it's opened it takes a few minutes to open the table.
Just researching options other than using it as little as possible.

ideas that have come up:
-cut table records in half (columns can't be split)
-SQL Server
-pray for new super computers

 

[kabaka]

Hi
I also have very large tables (800,000+ records) and I've noticed if I do a
sort (which takes a couple of minutes) and close the table Access asks me if
I want to save the design. If I choose yes, then the next time I open the
table it automatically does the sort before displaying the info. I don't
think there's a way to remove the
design changes. If you recreate the table with the exact formatting you'd
like (e.g. maybe run a make-table query based on your original table) it'll
probably open up quicker.

That's just a suggestion - it may or may not work, but it could be worth a
shot.

 

[Arvin Meyer]

I have never seen a table that *required* using more than 30 to 40 fields.
The largest table I manage is 57 fields and that's partly because I didn't
design it. None of my tables has ever taken more than a few seconds to open
and I have a few hundred thousand records in some of them. Look carefully at
ways you can distribute data over multiple tables.

Also ... think indexes. Wisely built indexes can speed data access by 10
times. Don't ask for 600,000 records when you need only a few. Don't run
queries with functions on all the records. Instead, retrieve the records you
need and then run the functions.

Without indexes, SQL-Server may actually slow down some of your data
retrieval. With 600,000 records, you may need to start thinking about
SQL-Server. You could also archive records which no longer need to be stored
in your active database. Faster computers won't help much without indexes,
although a faster network may speed it up a bit.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Dirk Goldgar]

I work for a State University and within our office we use Access all
the time. But whenever we deal with our extremely large access table
our machine gets bogged down. Is there a way to juice up access, so
it can handle this amount better? Whenever it's opened it takes a few
minutes to open the table. Just researching options other than using
it as little as possible.

ideas that have come up:
-cut table records in half (columns can't be split)
-SQL Server
-pray for new super computers

It depends on how you're using this table. In general, you should avoid
just opening up the table to display all records. Instead, you should
use preliminary filtering mechanisms to limit the number of records that
have to be returned. For (simple) example, rather than have a user open
the whole table on a form and then find the one record he wants, have
the user first identify the record he wants and then set the form's
recordsource to bring back just that record. Or at least specify some
criteria that will return only a small subset of candidate records.

In doing this sort of thing, it's essential to index those fields that
will frequently by searched or filtered on. That way, Access doesn't
have loop through all the records internally looking for matches; it
can just search the indexes first, and then return only the matching
records.

 

[Arvin Meyer]

Hi
I also have very large tables (800,000+ records) and I've noticed if I do a
sort (which takes a couple of minutes) and close the table Access asks me if
I want to save the design. If I choose yes, then the next time I open the
table it automatically does the sort before displaying the info. I don't
think there's a way to remove the
design changes.

By opening the table in design view, you can right-click on the titlebar and
choose Properties. Remove the Order By field name and save the design and
the table will revert to it's original design specs.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[kabaka]

Learn something new every day - Thanks

By opening the table in design view, you can right-click on the titlebar and
choose Properties. Remove the Order By field name and save the design and
the table will revert to it's original design specs.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Albert D. Kallal]

I work for a State University and within our office we use Access all the
time. But whenever we deal with our extremely large access table our
machine
gets bogged down. Is there a way to juice up access, so it can handle
this
amount better?

You don't mention if a network is involved here or not. Leaving out some
critically details such as this is akin to telling me you took a rope, and
it
is hard to move. However, you left out the fact that a cow is attached to
the
rope!

Whenever it's opened it takes a few minutes to open the table.
Just researching options other than using it as little as possible.

Ms-access should open in the same amount of time if you have 10 records, or
have 10 million records in the database. Again, the above statement is not
clear here.

-SQL Server

Do note that sql server is a heavy duty database system, and is capable to
scaling to VERY VERY large databases. However, DO NOTE THAT SQL SERVER IS
40% TO 200% SLOWER then ms-access. (yes, you read this right....sql server
is SLOWER!!!). The comparison here is that of a speed boat going at 40 knots
vs a aircraft carrier (which also can do 40 knots...believe it or not!!!).
However, the speed boat only has a crew of 1 or 2 people, and the aircraft
carrier has a crew of 5000. However, the speed boat is JUST as fast to carry
one or 2 people...but large amounts of people it can not carry.

However, do no confuse that issue with speed and reading of records.

So, be aware that sql server, or Oracle is NOT faster then JET if you are
talking about a single user system, and no network. The other issue is
course
sql server can handle more users, and handle larger files..but on the same
machine sql server is NOT faster then ms-access/JET.

So, if your application is slow now, upgrading to sql server WILL NOT fix
your performance problems. The solution to performance problems is how you
grab
the data, and your designs of he applications (you MUST BLAME THE
DEVELOPERS OF THE SYSTEM here , not the hardware, or the data engine!).

-pray for new super computers

A super computer model CRAY C90 which many universities still run was rated
at
about 400-500 MFLOPS
The Cray Y-MP M94 was rated at about 170 MFLOPS

A new desktop Pentium CUP is rated about 3000 MFLOPS (as much as ten times
faster then that Cray C90).

So, you got MORE THEN a Cray supercomputer on your desktop. The speed of
your
desktop computer exceeds that of a 10 million dollar Cray supercomputer
that is not very old!!!

ms-access has not been processing bound sine the advent of 1 gig Mhz
processors. So, getting a new processor with 5, or even 200 times the
processing WILL DO NOTING TO SPEED UP YOUR database. I repeat, increasing
the processing speed WILL DO NOT NOTING IN TERMS OF FIXING the performance
of the database. What is the limit factor here is bandwidth, and the
speed at which data is being brought off the disk drive. And, if your
designs/queries and use of the data is incorrect, processing WILL NOT FIX
this
problem. Upgrading from a 1 gig MHz processor to brand new 3 gig processor
will DO NOTING!!.

And, note that a average Pentium processor on a desktop today is FAR MORE
powerful then the Cray supercomputers of just a short while ago.

So, you are going to have to look at what is being done, and the fixes in
terms of performance are going to be that of changing your approaches, and
looking at thinks like reducing the bandwidth from the software to the data
files.

You don't mention what kind of processing you are doing here? Are you always
working with 600,000 records, are your problems generally that of working on
a small sub-set of the data? Are forms attached blindely to tables of
600,000
records ? (bad idea). There is MANY issues here...