Access as a Service

[Bud Jay]

Near as I can tell, Access runs as a service under XP and maybe the same
under Server 2003.

I have two questions and requests for information...

We purchased a dual processor machine a while ago, and to our dismay, Access
only used 1 of the CPU's, even when we ran Multiple Access programs
symultaneously. We even tried to run Access 2003 and Access 2007 against
each other to try to get the second CPU to be used, to no avail.

Does anyone know how start Access as a second service, and further to direct
that service to use the second CPU?

We are considering a major update to a single processor Server 2003 and will
be buying a new machine, likely a dual-head or maybe even a quad-head. We
use ODBC links to Access databases for VB Script written ASP pages, and will
be very dismayed if only one processor is used to service database queries.

Does anyone know if Access will be treated the same by Server 2003 as it is
by XP Pro? (Just 1 CPU used to service the Access ODBC Queries?)

Many thanks in advance for you help and answers.

Bud Jay
(e-mail address removed)

 

[Albert D. Kallal]

Near as I can tell, Access runs as a service under XP and maybe the same
under Server 2003.

No, the above information is incorrect. MS access is simply one of the
desktop programs included in Microsoft office. Access no more runs as a
service than does PowerPoint or excel. I'm not sure where you got the idea,
suggestion, concept or even hint that one of the desktop programs in office
is to be considered or even attempted to be run as a service.

We purchased a dual processor machine a while ago, and to our dismay,
Access only used 1 of the CPU's, even when we ran Multiple Access programs
symultaneously. We even tried to run Access 2003 and Access 2007 against
each other to try to get the second CPU to be used, to no avail.

If you start thinking of how the architecture of a computer works, you'll
quickly realize that throwing more processing at access applications is
going to do absolute bunks for improving performance.

The reason for this is for about ten years now MS access applications have
not been processing bound at all. You can add ten processors, but you're not
changing the speed or rotation or I'll ability of the disk drive to read
data. The speed of most access applications is the speed at which the disk
drive runs and that's about it.

Does anyone know how start Access as a second service, and further to
direct that service to use the second CPU?

You can't run access as a service at all. However if you launch a second
copy access, then the operating system will load balance the applications
running, and likely utilize the second processor.

Keep in mind that in about 99% of the cases using a dual processor computer
does absolutely NOTHING for improving the performance of 99% of the programs
you use. So, keep in mind that for 99% the programs you run on a computer,
if you have one processor or twenty processors, you will not see any
difference whatsoever in terms of performance. I repeat NONE whatsoever in
terms of performance.

However to be fair while one program will not run faster, if you're running
two programs at once you will be able to run each program at full speed on
each processor. So, while you can't increase the performance of one program
you can simply run more programs at once with greater speed.

So if you want to run two copies of MS access at once that's perfectly
legal. I often have one or two copies of MS access running on a machine all
the time.

However having said above, once again keep in mind you doing zero to
increase bandwidth and disc drive speed. Therefore you're not going to
increase the actual performance of your applications by any noticeable
amount by adding more processing into the mix.

We are considering a major update to a single processor Server 2003 and
will be buying a new machine, likely a dual-head or maybe even a
quad-head. We use ODBC links to Access databases for VB Script written
ASP pages, and will be very dismayed if only one processor is used to
service database queries.

opps.....
That the sounds like you're not using MS access at all, but in fact using
the jet database engine.

You can use ODBC connections on any windows computer to an MDB jet based
file, but you actually don't even have to install MS access. While the
distinction between MS access the application development system, and jet
data engine is often used interchangeably here, in your case it's a
significant issue.

However now that I realize you're talking about the jet database engine, and
not MS access the development and reporting tool, much of what I said above
still applies to the jet database engine. It is not a threaded data engine,
nor does a run as a service. what this means is that your client (your web
server) has to wait for jet to complete its operations.
This is certainly going to be a bottleneck for your website

Does anyone know if Access will be treated the same by Server 2003 as it
is by XP Pro? (Just 1 CPU used to service the Access ODBC Queries?)

As I said your chasing kind of a wild goose here. The jet database engine is
not go to speed up one bunk from what you're proposing all. You should
probably consider using one of the many free sql data engines. there's
several additions of SQL server that are free from Microsoft, and they are
much better for use with a web site. Remember the jet database engine only
reads files directly from the disk drive ***and*** it is the client side
that does all the processing (reading of data) here. In this case it'll be
your website that does the processing, this is also true even in the case if
the website is NOT even on the same machine as where this JET mdb file
resides!!!

So keep in mind that the jet database engine is a file based system, not a
socket based or service based system like SQL server. This is a very
important point to view to keep in mind in terms of the architecture you're
using here.

So keep in mind that a jet ODBC connection is not a service at all, in fact
100% of the processing will occur on the client that does the request of the
data. I repeat 100% of the processing occurs from a client side software
that makes the request. In the case of SQL server you make a request of the
server and THEN it goes and reads the data from the disk drive (this is a
separate process from that of the requesting program, with jet this is not
the case).

If you're looking for more concurrency, and something that can utilize a bit
better of the processors then you'll have to move up the SQL server. Using
JET here gains you absolutely zero benefit in terms of cost and ease. (since
you're not actually using MS access on the system at all anyway).

Keep in mind my comments above about adding more processing generally does
not solve the disk read problems which is useally the real bottle neck when
you use a JET database. However in instances where you need a better
concurrency architecture, and in fact much your data might be cached in RAM,
then you're gonna be better off to use an SQL server based system. This is
especially so with a web server.

Using a file based system like jet is really barking up the wrong tree here
to try and utilize more connections and more processers. It is simply the
wrong tool and the wrong approach.

 

[Norman Yuan]

Besides what Albert has said.

If you use the database (*.mdb) file as data source for ASP application on a
web server, it HAS NOTHING to do with MS Access (whther it uses multiple
CPUs or not). You do not need MS Access installed. It is up to IIS to use
multiple CPU to serve the ASP application, which in turn get data from *.mdb
file via Jet engine.

Just repeat what Albert has said: no, MS Access is not a service; no, Jet
database is not a good choice of web application (but certainly can be used
in somce cases).

 

[Bud Jay]

Albert and All...

My apologies. I mis-read the entry in the Performance Monitor of the XP Pro
Windows Task Manager. Access is a PROCESS (not a service) in the list of
Processes when it is running. It seems to be only one process regardless of
the number of Access Programs (actually activities in fully different Access
Databases and actually different Access versions) I activate.

On dual core processors which I have tested, I can have different access
databases running as different versions of Access, and each of the different
databases each on it's own disk drive, and when I look at the Performance
monitor of the XP Pro Task Manager (it has two CPU lines on Dual Core
'chines, but only one on single core), I see one CPU pegged at 100% and the
other one sits at or near zero, as if Access running multiple tasks, using
different versions, and having multiple disks just ignores the second
processor.

I simply cannot explain this behavior. I have always assumed that Access
has some restriction, accidental or intended, that restricts it to one
processor.

The application I run has two independent components. The "off-line"
(batch) component uses enough "in memory" tables and activity that it is
quite memory bound, and not at all dependent on Access' capability to do
disk activity. I can see different completion times when I run this on
different speed processors even without looking at the Performance monitor.
It is almost always "pegged" at 100 %.

For the above reasons, we are limited to single processor 'chines, or to
"waste" the second processor. When we "waste" the second processor, we
cannot find a dual processor running only one core that will perform as fast
as a single core 'chine. I would for sure appreciate some way that I could
see the second processor used by the Access applications so that we could
use them effectively. Single core 'chines soon will be rare indeed.

You are correct, that Server 2003 uses the Jet engine, not Access. I do not
have Access installed on that 'chine. It is a single processor running
Server 2003, and again with the performance monitor, I see my code forcing
the CPU line to or near the 100% line when it is processing inputs. The new
requirements by my customer have increased response time from 1-2 seconds to
15-30 seconds on the main ASP program. Volumes are projected to jump about
6 times when we implement the new process in production. So we can expect
about a 60 times (6000%) increase in processing requirements.

The CPU on this 'chine seems to average about 25-30% peak utilization in the
current configuration. If we can expect each transaction to take ten times
as long and we should expect a 6 times increase in volume (6000%), then
obviously, I need to add greatly to capacity. The problem is to figure out
how best to configure.

The current 'chine is an Athlon 64 2800+ with 1 Gb Ram, and a reasonably
fast IDE Disk. We anticipate the new 'chine (or 'chines) to be an Athlon 64
dual core 6000+ and we will use SATA2 disks and have at least 4 Gb Ram. We
have as an option to use solid state SATA disks for the Access Files which
may provide better performance.

Again, though, I am concerned that the second processor may not "be alive"
in this configuration. I use "connectionless" links to the Jet to the
databases, and am very careful to properly end the connections as soon as
possible, which might free the Jet for the other processor, I hope.

In parallel, I have looked at the comparative SQL Server specs. The
"express" version is stated to support only one processor, the "workgroup"
version will support 2 processors, the "standard" 4 processors, and the
"enterprise" supports unlimited processors.

We feel that to support SQL Server instead of Access on this 'chine would
not be a big deal, and we are actively considering it. (this application
only displays the data taken from Access, and we do no on-line updating at
least at this time. Otherwise, there are some major differences in how
Access allows updating code) Obviously, with planning for SQL Server, we
need to select at least the "Workgroup" version to reach the second
processor.

We have a unique situation where we are a master/slave application (we are
the slave) and the master can use a "round robin" method to call multiple
servers (ours) to do the processing. So our problem at least will not
include load balancing on our end, as we can simply add processors here and
have them added to the calling table.

But how many is the question. At least initially, we plan to buy only one
'chine as above, to switch to "solid state" drive for the database, have the
SATA2 drive contain the operating system and SQL Server, and to use SQL
Server rather than Access for the databases. I suspect that we still will
need multiple 'chines, and have told my user that actual volume testing with
the revised configuration will probably indicate the expected number of
systems needed to support the expected transaction volume.

So Albert, I much appreciate your input. It would be extremely helpful to
know how to get Access to use the second processor of a dual core system. I
have been totally unsuccessful so far.

I hope SQL Server may be the answer for our on-line segment, and it may be
the only answer for the "batch" stuff to reach the second processor. To
change the "batch" segment to SQL Server, however, may be a much more
difficult task as to "attach" an SQL Database table in Access rather than an
Access table is reasonably trivial, but I have used ADO statements in my
Access code that are permitted by Access by are not SQL standard. These
changes might be quite difficult and time consuming. Access serves us so
very well, and seems totally reliable both for the on-lines and batch
processing. If it used the processors beyond the first one, we would never
consider switching.

Help if you can to tell me why I can't get the second processor to look at
Access programs. It would be greatly appreciated.

Bud Jay
(e-mail address removed)

 

[Bud Jay]

Thanks, Norman.

Please see my previous post to Albert and the Group. I think I clarified my
post.

Bud Jay
(e-mail address removed)

 

[Albert D. Kallal]

My apologies. I mis-read the entry in the Performance Monitor of the XP
Pro Windows Task Manager. Access is a PROCESS (not a service) in the list
of Processes when it is running. It seems to be only one process
regardless of the number of Access Programs (actually activities in fully
different Access Databases and actually different Access versions) I
activate.

It is absolutely critical for this discussion to make a distinction between
access the development system, and the database engine you use with MS
access. Remember you can build an access application and use oracle for your
database or SQL server, or in this case the jet database engine. Once again
I will restate this issue:

It is absolutely critical that in your discussions you make a distinction
between the two products. We don't call c++ a database. MS access is NOT the
data engine here. Access is a tool that lets you develop reports and write
and create software just like VB. This is not to be confused with the jet
database engine.

On dual core processors which I have tested, I can have different access
databases running as different versions of Access

You can also freely have several copies of the same edition of MS access
running. And, you can open the same application several times if you wish.
There is no particular restrictions in this regards. Just launch ms-access
3 times. Then open the same mdb application ...you have 3 copies of it
running. You don't need to use different versions of ms-access to make this
happen. I suppose that this point I could point out that like word, or
excel, MS access also has what we call re-entrant code. This simply means
if you open in internet explore 5 times, or launch word ten times, you
actually only have one copy of the code base in RAM, but separate memory is
used for the variables in code. only one copy of that code ever gets brought
into memory. I suspect this has some significance in how the windows
scheduler decides which CPU to run a given task on.

, and each of the different databases each on it's own disk drive

I don't think the issue of separate disk drives will make a difference from
a CPU processing point of view (But it might get the operating system can
request data from those drives as assynchronously.)

However, using one processor or ten processors is not going to be affected
by the number of disk drives you have. As I said before this whole issue is
moot on the issue of processors anyway. I said MS access applications have
not been processing bound for about ten years now. (they are i/o bound).

, and when I look at the Performance

monitor of the XP Pro Task Manager (it has two CPU lines on Dual Core
'chines, but only one on single core), I see one CPU pegged at 100% and
the other one sits at or near zero, as if Access running multiple tasks,
using different versions, and having multiple disks just ignores the
second processor.

As I said, I don't think the application is processing bound. If you want to
see MS access use both processor at 100%, then try the following simple
experiment:

Build a simple unbound form with a button behind it that runs a blank loop
to about 1 billion. Put in some timer code to give you the amount of time it
takes to do this task.

eg:

Private Sub Command0_Click()

Dim t As Double ' timer
Dim i As Long

t = Timer
For i = 1 To 1000000000
Next i

t = Timer - t

MsgBox "loop done, time = " & t

End Sub


Now, close the application (and access). Now launch one copy of MS access
(note I said MS access, not open your application). Now launch a second copy
of MS access. now in each copy of ms-access open the above application, and
open up the form with the above code. Now resize and place the two
applications side by side on the screen in which also you can see the
performance CPU monitor.

Click on the button and note how one CPU goes to 100%. (note the time). Now,
click on the other application and you'll see it go to hundred percent also.
Now, run both at the same time...you see BOTH cup's pegged at 100%. As you
can clearly see who we now have two access applications running on both
processors at 100%, And it's the same application, and were running them
both on separate processors.

Of course the above demonstration is a little bit a waste of your time, as
I've mentioned about a zillion times so far it's not a MS access we're
talking about it's your application and JET.

It's going be your website system that controls how well and how many
processes it launches as separate threads to read data from that jet
database. If your website's not intelligent enough to launch a separate
threads and separate copies of code, then you'll not see improvements
concurrency at all with additional processing. It is ****your****
application that decides this issue, since jet runs as part of that
application.

In parallel, I have looked at the comparative SQL Server specs. The
"express" version is stated to support only one processor, the "workgroup"
version will support 2 processors, the "standard" 4 processors, and the
"enterprise" supports unlimited processors.

The "express" edition only supports one processor, but it can run requests
asynchronously. That ability is worth a few processors alone. However, since
your application can't use the processor separately, then you have to split
out the database engine to something that can.

As I clearly shown, ms-access can run two copies on two processors. If you
can't get your web based system to do that, then you have to choose a
database engine that can do it for you.

Obviously, with planning for SQL Server, we need to select at least the
"Workgroup" version to reach the second processor.

As I said I my bets that you're not processing bound at all on the database
engine side anyway. Further if you used SQL server you can place it on a
separate machine and effectively double your processing at that point (Since
then the processing would not be shared with the website, but on a different
machine). It is an issue of the architecture and what kind of throughput you
get as opposed to the actual processing issue. You're dealing here with much
as a i/o and bandwidth issue as you are processing.

So Albert, I much appreciate your input. It would be extremely helpful to
know how to get Access to use the second processor of a dual core system.
I have been totally unsuccessful so far.

As I showed above that simple demonstration it's a piece of cake to get
access to use both processors. It is your web a software that you'll have to
get to use both processors, not JET. ms-access had no problem using both
processors when you run two copies.

You'll not see the jet database in the task manager. The reason is because
when you're using jet it's like it's become part of the actual application
you're using. It becomes like a simple subroutine in part of the application
code. Were talking about a library reference to code here, and running some
code. WE ARE NOT TALKING about a SEPARATE process that runs and communicates
with that program. I think this make things more clear.

The jet engine runs *as* part of the processing thread of the application
that is **using** jet. This is not an inter process communication issue like
when you use sql server. It is simply a program running and using the JET
code as part of that process.

If it used the processors beyond the first one, we would never consider
switching.

As I mentioned I disagree 100% that the issue of processing will fix any of
these problems.

Help if you can to tell me why I can't get the second processor to look at
Access programs. It would be greatly appreciated.

As I said it's a piece of cake to have two copies of ms-access running. The
problem is you're not running MS access in this case. It is your web site
code that has to know to launch separate copies of its code, or separate
threads to utilize the processor(s). If your web code can't run as multiple
threads, then you going have change your code to a threaded model. You can
also consider using a database engine that can run as threaded and thus
utilize multiple processors (like sql server). However, using a database
engine that can utilize multiple processors will NOT solve the lack of your
web code's ability to run as multiple threads. Your web code will simply que
up requests, and run on a single processor no matter what you do *unless*
your web system can utilize multiple threads. I suppose using a threaded
database engine like sql server will at least speed up the database part,
but your web requests part will still be stuck on that one processor.

 

[Bud Jay]

Albert and All...

All of this is helpful and I hope that you realize that I do know that the
on-line under Server 2003 is completely separate from the batch stuff which
is Access under XP.

I need to go to my other office where I have some dual-core 'chines where I
will set up the test you suggest. One thing you said may explain what I
have been experiencing.....

I suppose that this point I could point out that like word, or excel, MS
access also has what we call re-entrant code. This simply means if you
open in internet explore 5 times, or launch word ten times, you actually
only have one copy of the code base in RAM, but separate memory is used
for the variables in code. only one copy of that code ever gets brought
into memory. I suspect this has some significance in how the windows
scheduler decides which CPU to run a given task on.

This could fully explain what I have seen. If Access is loaded only once,
but that one load manages the "variables in code" which may be loaded
multiple times, then that explains why only one CPU is used, and why on the
"performance monitor", I only see one instance of MSACCESS.exe. I don't
understand why I only see one MSACCESS.exe, when I have loaded an ACCESS
2003 and an ACCESS 2007 database, but that I also can test.

I need to set up the test as you suggest, and that may be affected by the
above. I will post my results, probably tomorrow.

Again, many, many thanks for your kind attention to these problems. I will
be thrilled to resolve what has been a very thotny problem for me for months
if I can.

If it should be that we truly determine that MSACCESS.exe is only loaded
once, and that is why it only uses 1 CPU, could it be that I could copy
MSACCESS.exe to MSACCESS2.exe and have it load a second copy, and maybe get
away with it? I could see that I have a very small chance of success and a
great opportunity to make a huge mess, but I believe I will probably try it
anyway, on a very limited basis to see if then we can tickle the second
processor.

Bud Jay
(e-mail address removed)

 

[Albert D. Kallal]

This could fully explain what I have seen. If Access is loaded only once,
but that one load manages the "variables in code" which may be loaded
multiple times, then that explains why only one CPU is used, and why on
the "performance monitor", I only see one instance of MSACCESS.exe. I
don't understand why I only see one MSACCESS.exe, when I have loaded an
ACCESS 2003 and an ACCESS 2007 database, but that I also can test.

Actually you will see more than one copy of MS access running in the task
manger.
however what is important when code is re-entrant, there is only actually
ONE executable copy in RAM, *dispite* the fact that you load of the
application several times.

When we use the term reentrant code, we are speaking of code that is written
in such a way that it does not do stupid things (like self modifying parts
of the code, or using non relative memory references for the code). This is
why it might be difficult for a computer to run twenty different
applications, but you can easy run twenty seperate copies of the Internet
explorer (and if you open the task manager, you will see that twenty
copies). In actual fact you have twenty tasks and actually one copy of the
actual executable code in ram. If the code was not written in such a way as
to be re-entrant, then you would in fact HAVE to run twenty separate copies
of that code (and use considerably more system resources). In a simple sense
is probably good to point out that reentry code is simply multi user safe.

In the case of MS access, it is never really big deal. However if you're
aware of how the architecture of windows terminal services works, if you
have twenty users logon and running an access application you developed, you
actually have one actual copy of the executable code in RAM. Thus in
multiuser software, entrant code is a significant advantage, and a
significant consideration.

I need to set up the test as you suggest, and that may be affected by the
above. I will post my results, probably tomorrow.

It will not. You will see multiple copies of MS access running in the task
manager. (but, as mentioned in actuality you only have one copy the
executable in RAM).

If it should be that we truly determine that MSACCESS.exe is only loaded
once, and that is why it only uses 1 CPU, could it be that I could copy
MSACCESS.exe to MSACCESS2.exe and have it load a second copy, and maybe
get away with it?

No, you don't wanna do that. And, since the code is RE entrant, then you
don't have to worry about this issue at all. Furthermore as I mentioned if
you do my test, you'll see multiple copies of MS access and the task
manager.

My only point in bringing up the issue of reentering code is from an
architectural point of view, and also the fact that the system may use the
processor scheduler to take into account some issues. However, for the most
part if you have a reentry code, then it is thread safe, and thus dual
processor safe (and that is significant). If you have some code that is self
modifying or modifies local values, then it's not RE entrant, and therefore
it's also not able to be run on separate processors with great ease. In
fact, it means the software is not even multi user safe.

Remember, there's little difference between running several copies of word
on you computer, or running windows terminal services and having 4 seperate
users running that copy of word. If code is not reentry, then you'll never
get that same piece of software to run on different processors, and you'll
always have to run an actual separate copy of the code in memory. However,
since word is RE entrant, then in terminal services only the first logged on
user loads a copy of word into memory...all additional users get a free
ride, and don't actually have to load the application into memory (and this
issue is not to be confused with disk caching).

If you think of a web server and having hundreds of users hit the web site
and using parts of applications, if the code is not reentrant, then for
every single user that is browsing and running application code, they'll
have to have a seperate copy that software running.

However do not confuse the issue of re-entrant code with what the task
manager shows. (the task manager does not show the fact that you're running
the same copy of the executable in RAM). To be fair for most applications
running today it is the memory they use, not the actual executable code that
uses up memory resources anyway.

 

[Bud Jay]

Albert and Group...

Albert is absolutely correct. I prepared the test he suggested. I actually
counted to 2 billion and did a screen update and a "DoEvents" every 10,000.
I tested it under both Access 2003 and Access 2007.

I have not seriously worked with this for about 18-20 months and at that
time, we had two 'chines that acted the same. When we ran tests like this,
one cpu was dead, and the other pegged at 100%, and the CPU meter was at
50%. I now must suspect hardware trouble was occuring, but I cannot prove
it as both 'chines have died and been parted out and the leftovers scrapped.

This time, both CPU's showed activity, but when only one Access was running,
each CPU meter showed activity, and the total CPU meter showed 50%. When I
started the second, both meters went to max, and the total meter went to
100%. This was true whether I ran in Access 2003 or in 2007.

Of significance, the speed of the changes in the display as each 10,000 was
displayed did not vary as I ran 1 or 2 instances of the program. It was a
visual confirmation that both CPU's were active and that they were quite
independent of each other.

So, Albert, you are 100% correct and I stand corrected.

Access DOES run on both cores of a dual core CPU.