[AccessD] Connections and Performance

As soon as I see an article saying Access is bad behind a webserver, I just
roll my eyes.  In essence, an .mdb placed physically on a webserver is going
to crush a SQL server running on another machine, because the data is being
read locally, in essentially the same way a SQL server is going to read it's
own local data, so you completely drop the time it takes to send and receive
the data across a NIC. (On top of no time loss for transaction logs, etc.)

Drew

-----Original Message-----
From: JWColby [mailto:jwcolby at colbyconsulting.com] 
Sent: Friday, February 02, 2007 9:29 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Connections and Performance

>This means that although Access remains suitable for small Web
applications, those that experience growth where they are experiencing more
than 25 concurrent connections should consider upgrading to a more robust
database."

And what lab does he use for his perspective?  I tried to go read the
article but just got the main page and no further.  It would be useful to
see real numbers with understandable (to use programmers) test methods.  I
have never seen any such thing, nor do I have the time right now to build
such a test system.  I might have to though since my applications are
extremely complex and this might be something to watch.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Friday, February 02, 2007 10:17 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Connections and Performance

On 2 Feb 2007 at 10:07, Michael R Mattys wrote:

> Heh :) It took me a few minutes, but I found this 'explanation'
> http://www.crystaltech.com/Newsletters/news2006-08tech.aspx
...
> However, Access is not optimized for use with a large number of 
> concurrent connections and does not scale for large databases. As more 
> users connect to an Access database, the performance starts to degrade 
> rapidly. Even moderately-trafficked websites can easily have 5 or more 
> users at one time which can cause Access to lock the number of
connections.

Alternatively:

http://www.2020datashed.com/help/Topics/DataProviders.html

The database cannot handle more than 255 concurrent connections.
    This topic is very complex and many programmers will argue that this
number is misleading. Under the right conditions, if lock-type is managed
carefully and each of the concurrent users are connected to different
resources/tables in the database, and the users' connectivity is properly
managed via OLE, then 255 distinct users can be connected simultaneously to
the MS Access database. (As discussed in MS Knowledge Base article: 
176670.)
    However the number of available concurrent connections drops
significantly when multiple tables are in use by a single connection and if
the data is not sufficiently locked while communicating with the data.
    This number of course does not represent "people"...it represents
"users" which is often a different concept altogether. In a web-based
application each connection to the database can be opened, queried, and
closed within a few milliseconds, the number of "people" which can browse
the web site simultaneously is not limited to 255.
    In conclusion: this particular limitation is a concern but shouldn't
worry you unless your web site consistently receives a significant amount of
traffic. Perhaps more than a few thousand visitors per day.
    Sean Nicholson of www.informit.com (Article) has this to say:

        "Microsoft lists 255 as the maximum number of concurrent users to an
Access database. This means that only 255 users can actively interact with
the database at the same time. This might be theoretically true in
Microsoft's labs under the ideal circumstances, but the reality of working
with an Access database is that performance falls off sharply when more than
25 or 30 concurrent requests are made. This means that although Access
remains suitable for small Web applications, those that experience growth
where they are experiencing more than 25 concurrent connections should
consider upgrading to a more robust database."


--
Stuart


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com