[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