Drew Wutka
DWUTKA at marlow.com
Fri Feb 2 11:24:17 CST 2007
Never bothered to test it, to have empirical data. It is just obvious.
For example, our new website (which isn't up yet), has a navigation process
that puts a nav bar at the top of every page. On our current site, that bar
is built strictly from a hack into FrontPages navigation structure files.
(We use Frontpage to edit the site...well, some people do, I do most stuff
in just plain ol HTML.) It's essentially a csv file. The new site has a
mix, it gets most stuff from the FrontPage source, but then it also adds in
all the products. There are three product pages, a page that shows all the
product types, a page that shows all the products of that type, and a page
that shows info on just a single product. So, to FrontPage, the Navigation
is three pages, but to the user, there are lots of pages based on the
content that is stored within the database. So the new site now builds the
navigation bars from a mix of the FP navigation, and the product database
(which handles everything from the products on the web, the glossary,
shopping cart, etc.). It is doing this for every page you load, and it
loads as fast as your browser can handle it. The product database is local
to the webserver (though not visible on the web itself), so everything that
occurs to the db is being done locally by the webserver itself.
If you had a SQL Server running on Machine X, and then had an identical
machine 'Y' (same processor, hard drives, memory, etc). Which would be
faster? Running a query on Machine Y, that is getting data from a SQL
Server on Machine X, or running a query (against identical data tables) on
Machine Y that is getting data from an Access MDB on Machine Y?
There is nothing special about a server side DB that makes it more powerful
then Jet at doing the same thing. The only true difference is that with a
server side system you have a dedicated system retrieving data. But the way
that machine is retrieving data is essentially the same way Jet does. It
has to read it's indexes, find the data, put it together, and send it out.
Jet does the same thing (though maybe not exactly the same way). Over a
network, Jet is pulling chunks of a file across the network, which does take
longer then letting a server side system just send the resulting data back.
Since most mid to large apps in Access are used in a network environment,
we, as Access developers, get hammered by this shortcoming of a end user db
system. Putting the .mdb on a webserver, with a web interface, however, is
functionally identical to putting the data into a server side db, with the
exception of roleback/transaction capabilities.
Drew
-----Original Message-----
From: Charlotte Foust [mailto:cfoust at infostatsystems.com]
Sent: Friday, February 02, 2007 10:27 AM
To: Access Developers discussion and problem solving
Subject: Re: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com