Re: Performance of MSAccess queries
Paul Overway
Microsoft would rather sell you SQL Server. RAM will make a difference
though....so, make sure you have plenty.
--
Paul Overway
Logico Solutions
http://www.logico-solutions.com
"WaltB" <Wa...@discussions.microsoft.com> wrote in message
news:5EAE195E-39EE-472E...@microsoft.com...
>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
WaltB
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
Albert D. Kallal
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.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOO...@msn.com
http://www.members.shaw.ca/AlbertKallal
Albert D. Kallal
> 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.
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..