Performance of MSAccess queries

W

WaltB

I purchased a multiprocessor workstation a few years ago in the hope of
restoring performance to other applicatons when executing long running
MSAccess queries. The thought was the NT (then) would schedule an OS
execution thread on one processor and would switch/allocate other
applications to a thread on the other processor.

The presence of dual processors did absolutely nothing to improve the
performance of other applications during long running MSAccess queries.

Does anyone know why NT/2000/XP cannot improve the performance of MSAccess
queries running on a dual processor machine?

WaltB
 
P

Paul Overway

Because Access isn't designed for it. And I don't think it is likely to be.
Microsoft would rather sell you SQL Server. RAM will make a difference
though....so, make sure you have plenty.
 
M

Marvin P. Winterbottom

How much RAM is in the machine? That could be the limiting factor, not
processor threads.
 
W

WaltB

Thanks. However, I would expect that on a multi-processor machine that the
NT/2000/XP OS would allocate MSAccess to a thread on one processor and that
as other applications are opened that the OS would then allocate them to
another less busy processor.

In this scenario MSAccess would have no knowledge of the processor on which
it executes but I would expect the OS to know and to choose another processor
for subsequent applications as they are opened.

WaltB
 
A

Albert D. Kallal

The problem you are facing is that queries are NOT processor bound. They are
i/o bound. (i/o means Input/Output in computer jargon).

You have to wait for ONE record to be read off of the disk drive. You can
put in 10, 20, or even 2000 processors, but the time to read one record off
of the disk drive (or transfer across your network) is going to be EXACTLY
the same. More processors will NOT make memory faster, nor will more
processor make the disk drive all of a sudden speed up (the disk drive will
NOT start to go round and round faster when you put in more processors).

Your queries are not slow due to lack of processing, they are slow due to
how fast your machine can read data from the file.

In addition, the other reason why queries would run slow is:

you have bad design
your quires cannot be optimized (no optimizing means the disk drive i/o
is going to be great)
Your amounts of data are so great (in the millions of records) to make
the queries slow.

You don't mention your data set sizes, but if you are working with tiny
tables, say only 75,000 to 150,000 records, then most operations should
occur in about 1, or 2 seconds max..and we are talking about a older slower
junk type PC here. A newer box would most certainly be faster since the
disk drives and memory is better.
 
A

Albert D. Kallal

WaltB said:
Thanks. However, I would expect that on a multi-processor machine that
the
NT/2000/XP OS would allocate MSAccess to a thread on one processor and
that
as other applications are opened that the OS would then allocate them to
another less busy processor.

In this scenario MSAccess would have no knowledge of the processor on
which
it executes but I would expect the OS to know and to choose another
processor
for subsequent applications as they are opened.
Click to expand...

Well, in fact the above is likely happening...but if you read my other
response..the problem here is not the processing speed, but in fact the
speed at which the data is pulled off of the disk drive..and that don't
change with 2, 4 or 100 processors. The disk drive does not all of a sudden
go "faster" with the additional of more processing..
 
N

nellie almodovar

WaltB said:
Thanks. However, I would expect that on a multi-processor machine that the
NT/2000/XP OS would allocate MSAccess to a thread on one processor and that
as other applications are opened that the OS would then allocate them to
another less busy processor.

In this scenario MSAccess would have no knowledge of the processor on which
it executes but I would expect the OS to know and to choose another processor
for subsequent applications as they are opened.

WaltB
Click to expand...
 
Top