[AccessD] Connections and Performance

Interesting, Drew.  Have you written an article on that?  It sounds like
useful knowledge.

Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
Sent: Friday, February 02, 2007 7:40 AM
To: Access Developers discussion and problem solving
Subject: Re: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com