access performance

[Guest]

Hello,
After I installing and moving my databases to a new hard drive, Access is no
longer using all of the CPU available to it. I am working with a very large
(11 million records) static dataset. It used to be that when I ran a query it
took 20-30 minutes at 99-100% of the CPU. Now they are taking hours and
Access is only using 12-60% (fluctuating) of the available CPU. Has anyone
ever experienced this? I cannot understand why the program would not use all
of the processing power available to it, especially considering it was doing
just that a week ago. Any insight would be extremely appreciated.
Rebecca

 

[Tony Toews]

After I installing and moving my databases to a new hard drive, Access is no
longer using all of the CPU available to it. I am working with a very large
(11 million records) static dataset. It used to be that when I ran a query it
took 20-30 minutes at 99-100% of the CPU. Now they are taking hours and
Access is only using 12-60% (fluctuating) of the available CPU. Has anyone
ever experienced this? I cannot understand why the program would not use all
of the processing power available to it, especially considering it was doing
just that a week ago. Any insight would be extremely appreciated.

Did you move to a new computer or just a new hard drive? If you have
faster computer but the hard drive isn't much faster then it may very
well be that Access is now waiting for the hard drive to respond
whereas before the computer could keep up to the hard drive.

However this doesn't explain why the queries are taking hours and
hours vs 20-30 minutes. I'd wonder if the hard drive has the proper
cables installed and such.

Also I'd wonder about any queries taking 20-30 minutes anyhow. Are
the tables properly indexed including your selection criteria such as
dates.

Have you installed all the Office patches?

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Guest]

Tony,
Thanks for responding. Here is some more information about my situation:

Did you move to a new computer or just a new hard drive? If you have
faster computer but the hard drive isn't much faster then it may very
well be that Access is now waiting for the hard drive to respond
whereas before the computer could keep up to the hard drive.

I installed a slave drive, and moved data to it over what my IT guy later
told me was the wrong cable. Could that have corrupted the database? The
right cable is in there now. In the past I had been building little databases
to answer select questions because my dataset is so big. There are 7 of
those. One of them still works properly, the others are all doing this
slow/not using CPU thing. I have built two new ones and they have the same
problem.

However this doesn't explain why the queries are taking hours and
hours vs 20-30 minutes. I'd wonder if the hard drive has the proper
cables installed and such.

I have moved the database to two other computers and the same issue is
happening. So it seems like the database is the problem?

Also I'd wonder about any queries taking 20-30 minutes anyhow. Are
the tables properly indexed including your selection criteria such as
dates.

My databases are already two gigs, so I don't think I can do anymore
indexing. Straight select queries are very fast, but I am doing a lot of
summarizing, and those are the ones that take a long time.

Have you installed all the Office patches?

I am checking that right now...

Thanks again,
Rebecca

 

[Tony Toews]

I installed a slave drive, and moved data to it over what my IT guy later
told me was the wrong cable.

Ah, yes. The newer IDE drives use an 80 pin cable instead of the
older 40 pin cable. The 80 pin cable has a ground wire between each
of the 40 wires to suck up any stray interference. If the drive or
controller doesn't see the 80 pin cable it defaults to an older much
slower mode.

Could that have corrupted the database? The
right cable is in there now. In the past I had been building little databases
to answer select questions because my dataset is so big. There are 7 of
those. One of them still works properly, the others are all doing this
slow/not using CPU thing. I have built two new ones and they have the same
problem.

Possible. A client had an interesting problem where a process went
from 20 minutes to 3 or 4 hours. Compacting did not solve that
problem. But importing the MDB into a new MDB solved that problem.

I have moved the database to two other computers and the same issue is
happening. So it seems like the database is the problem?

Agreed. Although without doing proper bench marking before you never
quite know for sure what the problem is.

Also given that one of your databases is now running quite fast.

My databases are already two gigs, so I don't think I can do anymore
indexing.

Then you do have some interesting data.

Straight select queries are very fast, but I am doing a lot of
summarizing, and those are the ones that take a long time.

Grouping queries? Are all the fields on the grouping part of the
query indexed?

I am checking that right now...

I'm not as concerned about that now simply because you stated in your
reply that you had only installed a new hard drive. I wasn't sure in
my previous reply if you meant to say you had installed a new PC.
Sometimes posters get a bit confused about these details. <smile>

Side note. There are times, and this is very likely one of them,
when the data the queries are processing gets so large Access starts
using temp files in the temp directory.

Now given that you have two hard drives and the databases are on the
second hard drive it is a good thing that your temp directory is on
the first hard drive. Thus the hard drive head on the second hard
drive can read data on the MDBs independently of the hard drive head
being used on the first hard drive in the temp folder.

But I'd also clean out the temp folder on a regular basis so the OS
doesn't waste time looking through 1500 files to ensure it isn't going
to create a duplicate temp file name or such.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

Thanks for responding. Here is some more information about my situation:

P.S. This promises to be an interesting discussion. I like these.
<smile>

Feel free to email me at tony at granite dot ab dot ca to notify me
when you've posted something new in this discussion thread. Note
that I want all discussion to be in public so all can read and
contribute.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Guest]

So when I tried to import a database into another (new)database, Access was
using 10-20%of the available CPU. This is abnormal, correct? It should use as
much of it as it can? Could there be a registry or a setting somewhere that
is wrong? Could this be viral?
Can you sense my frustration...
Thanks,
Rebecca

 

[Guest]

One other question. Is the temp file in question the C:/WINDOWS/temp file? Or
does Access have it's own somewhere?

 

[Tony Toews]

So when I tried to import a database into another (new)database, Access was
using 10-20%of the available CPU. This is abnormal, correct? It should use as
much of it as it can? Could there be a registry or a setting somewhere that
is wrong? Could this be viral?

No. This could simply be that the process is disk bound. That is
the CPU is waiting for the hard drives to process data.

Hmm, if importing I'd suggest creating the new MDB on the first hard
drive and importing from the second hard drive. Then moving it to
the second hard drive. Again because the hard drive heads wouldn't
need to move back and forth so much.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

One other question. Is the temp file in question the C:/WINDOWS/temp file? Or
does Access have it's own somewhere?

No, Access uses either the system or user temp file folder. Which
might be Windows\temp or C:\Documents and Settings\<user name>\Local
Settings\Temp

I mention this more as background material and why using a second hard
drive would be a useful concept in terms of disk head movement, etc.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

I am working with a very large
(11 million records) static dataset. It used to be that when I ran a query it
took 20-30 minutes at 99-100% of the CPU.

Hmm, another alternative, given that you're only doing data analysis,
might be to add three or so GB of RAM to your system and create a RAM
drive. This is a drive entirely in memory.

Now will this help? I don't know. I vaguely recall one posting
stating that someone tried it but it didn't help a lot. OTOH that was
a number of years ago and the CPUs would've been considerably slower.
Might've even been Win 95 or Win 98 for all I can recall.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

Hmm, another alternative, given that you're only doing data analysis,
might be to add three or so GB of RAM to your system and create a RAM
drive. This is a drive entirely in memory.

Or maybe a thumb drive on a USB port. That might be considerably
faster than a hard drive. Make sure the USB port is 2.0 though.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

Or maybe a thumb drive on a USB port. That might be considerably
faster than a hard drive. Make sure the USB port is 2.0 though.

I'm told that some USB drives are significantly faster than others.
So do some testing on these first.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Guest]

I really appreciate all of your help. I successfully imported the database,
but to have it run just as slowly. My current thought is to pull the second
hard drive out and do a system restore to prior to the install to see if it
makes any difference. I tried to start from scratch today and Access is
running slow for even a normal import of a text file from the master drive. I
think that the point at which this all began is when I transfered the
database to the second drive over the wrong cable. I will keep you posted as
to whether or not this works.
Thanks again,
Rebecca

 

[Tony Toews]

I really appreciate all of your help. I successfully imported the database,
but to have it run just as slowly. My current thought is to pull the second
hard drive out and do a system restore to prior to the install to see if it
makes any difference. I tried to start from scratch today and Access is
running slow for even a normal import of a text file from the master drive. I
think that the point at which this all began is when I transfered the
database to the second drive over the wrong cable. I will keep you posted as
to whether or not this works.

Whoa. Don't do the system restore yet. I really can't see that
making a difference.

Now it is quite possible that both IDE drives have degraded
performance if the wrong cable is still in there and they're both on
the same IDE port. You might be able to see this in the bios boot
screen and see if they both state something like PIO or UDMA 100 or
similar. PIO is really old and sucks big time. UDMA 100 or UDMA 133
is reasonable enough.

(I don't know if you have a SATA drive and I have no experience with
those.)

Ahh, here's an interesting article on the topic.
http://www.michna.com/kb/WxDMA.htm. While these are mostly
applicable to scratched CD/DVD this could definitely be a problem
with hard drives.

However what puzzles me is that you stated in your second posting
"There are 7 of those. One of them still works properly, the others
are all doing this slow/not using CPU thing" Why would one still be
fast? Or was it on the first hard drive.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

Ahh, here's an interesting article on the topic.
http://www.michna.com/kb/WxDMA.htm. While these are mostly
applicable to scratched CD/DVD this could definitely be a problem
with hard drives.

Interesting. My laptop primary IDE channel is set to UDMA 5. Quite
reasonable. But my secondary IDE channel is UDMA 2. And that's the
drive on which my CDR/DVD is attached to. UDMA 2 is one third the
speed of UDMA 5. See the table at
http://www.pcguide.com/ref/hdd/if/ide/modesUDMA-c.html

So depending on your hardware configuration your second hard drive
might be on the secondary IDE channel along with your CD/DVD drive and
may have been slowed down dramatically.

If that is the case move the second hard drive to your primary IDE
channel along with your current hard drive. Note that you have the
master and slave switches set appropriately.

Again if it's SATA then I have no idea.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[UpRider]

Try turning indexing off for the new drive. Click My Computer, right click
the new drive, properties, uncheck the indexing option.

Maybe,
UpRider

 

[Tony Toews]

Try turning indexing off for the new drive. Click My Computer, right click
the new drive, properties, uncheck the indexing option.

I would think this would only matter until the indexing has finished
doing the initial indexing. After that it only needs to reindex just
the changed files. And I doubt MDBs qualify.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[James A. Fortune]

Or maybe a thumb drive on a USB port. That might be considerably
faster than a hard drive. Make sure the USB port is 2.0 though.

Tony

Tony,

I love using a thumb drive to backup and to move Access data but I've
heard that there is a limit to the number of writes. I'd hate to have
an Access app crash to a halt because it used up all the writes. Do you
know anything about this limitation or where I can find out more
information?

Perhaps the RAM drive info was from Albert D. Kallal:

http://groups.google.com/group/comp.databases.ms-access/msg/ee830a006b9fb1d3

James A. Fortune
(e-mail address removed)

 

[Tony Toews]

I love using a thumb drive to backup and to move Access data but I've
heard that there is a limit to the number of writes. I'd hate to have
an Access app crash to a halt because it used up all the writes. Do you
know anything about this limitation or where I can find out more
information?

No clue but thanks for the warning.

Perhaps the RAM drive info was from Albert D. Kallal:
http://groups.google.com/group/comp.databases.ms-access/msg/ee830a006b9fb1d3

Not sure in what context you are mentioning RAM drives though and
Albert's comments.. Yes, it might not be that much faster. But
then Access may have some caching limits and if you give it 2 GB of
RAM won't use it all.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Guest]

Finally, there is resolution. I reistalled Access and disconnected the second
hard drive, to no avail. I got my database to work normally on another
computer, which meant to me that something was wrong on the hardware end.
I had forgotten that the new hard drive had not worked with the old
controller, so my hard drives were moved to a new yet identical machine. (The
IT department handles all of that stuff, I might have been more prone to
think about it had I actually done it.) So I finally convinced them to wipe
my computer and start over, and Access has returned to normal funtionality.
My databases on the second drive ar fine.I don't think I will ever know what
happened. My hard drive was extremely fragmented, and wouldn't defragment.
There was a ton of stuff in the temp file, as you suggested. Maybe all of
these things combined?
The truly happy ending is that I found out I can use a SQLserver that is on
site. My boss was under the impression that we didn't have a license for it,
and I felt my project did not warrant the giant expense.I tried to use
SQLserver express, but my data was to big for it. I think that the move to
SQLserver as a backend may be worth all of this headache.
I really appreciate all of your time. Your suggestions kept me moving forward.
Rebecca