How scalable is MS Access?

[cydewaze]

I'm wondering if I should be worried.

My office is in the process of doing a huge website redesign that will use ColdFusion/Access to generate pages. There are thousands of pages, and every page will be doing at least one db query to get information. Some of the pages will be doing multiple queries to fetch a lot of info. We have a few databases that are over 30MB, and some as large as 60MB. I know this isn't huge, but... well, it's Access.

Should I be worried about using Access rather than something more robust, or will it be fine for our use?

[Vali]

use MySQL, it's cheaper and can handle allot more than Access.

if your websites get allot of traffic, your Access database won't hold up as well as MySQL.
(Access is more for 1 user at a time accessing the DB, where MySQL works with a few thousand queries a second easilly)

[cydewaze]

I just did a dir *.htm* /s on the development server. 52,000+ pages.

It's a federal gov't website, so it'll get a fair number of hits.

[felgall]

Microsoft SQL Server is Microsofts database product that should be used where a Microsoft database is required. At the bottom end of its range it overlaps with what can be done with advanced Excel, covers everything that can be done with Access, and at the top end of the range can handle anything that any database needs to be able to handle (provided you have enough servers to run it on).

Access doesn't really have a place in any significant database project. It is basically aimed at the home market for people who want to be able to store info for home use without having to learn advanced Excel to do it.

[cydewaze]

Thanks felgall. I'm wondering how adamantly I should push to get us to upgrade. Not that anyone will listen to me, but it might be worth some "I told you so" value.

[verschha]

use MySQL, it's cheaper and can handle allot more than Access.

Access (or better said a Jet Database Engine, because that's what you're actually using in an web environment) is just as cheap as MySQL, both are free

if your websites get allot of traffic, your Access database won't hold up as well as MySQL.
(Access is more for 1 user at a time accessing the DB, where MySQL works with a few thousand queries a second easilly)

Unless you've a lot of traffic, Access will hold up pretty good. It's perfomance is very much underestimated by people that didn't use it in a real live scenario. I've seen examples of websites with over 500.000 page visits a day, with a mdb as the backend database that was over 1 GB with no performance isssues at all.

Yes, it's true. MySQL is better. And when you're used to Microsoft products you might want to consider to upgrade to MS SQL Server (The express edition is also free). Upgrading is very simple using the build in Upgrade wizard when you've Access.

[TheOriginalH]

Access (or better said a Jet Database Engine, because that's what you're actually using in an web environment) is just as cheap as MySQL, both are free

Actually, it's $229, but that is pretty academic, because as has already been stated, it really isn't the right tool for the job described - concurrent connections will be an issue, it ain't fast and I am informed (though not experienced in the problems) that it is pretty insecure.

MySql or MSSql are both far better fits for the job - which you use is basically down to which you are more comfortable with.

[Czaries]

I can't believe no one has stated this, but the primary reason you can't use MS Access on any kind of website is due to one critical design specification:

Access only allows one connection at a time, so if there are two simultaneous requests on your website (two different people visiting at the same time), the 2nd person has to wait until the 1st person's connection has closed. This makes websites incredibly slow and even completely unreachable as they continue to get more traffic.

So thus, the answer to your question is: MS Access is not scalable at all.

Real databases like MySQL and MS-SQL allow multiple concurrent connections, so a new connection thread is opened to serve the 2nd person at the same time as the 1st instead of making them wait. They scale very well this way.

[molona]

Access (or better said a Jet Database Engine, because that's what you're actually using in an web environment) is just as cheap as MySQL, both are free

First news I had about this

Access normally comes with Office, and you pay for it

And, unless things have changed much, you also have to pay for MSSQL. It is for this reason that people turned to MySQL. MySQL has a free version.

The second reason is that Access is just a database, while MSSQL, MySQL and many others are Database Managers. It is a slight difference, but with huge impact.

Access can open connections to various tables but only one database, while a database manager allow you to connect to various databases and this helps with scalability.

Access will not be good for an online store, as an example. When it reaches 20000-30000 records, it simply does not work well (of course, this is not valid for all databases, it depends on the amount tables and number of fields in each table, and how the tables are connected, etc)

Access is slow, specially if you use referencial integrity (same happens to MySQL when you use InnoDB motor)

For this reason, it is normal to use Access not to store the data, but only for the front end (that is, simply to create the forms for entering/retrieving data, and the screens that users will work with) and connect it to a database manager, typically MSSQL, where all the information will be stored. In access, that's called an Access Project. Creating a new project will create a file with extension adp.

The project can be connected to other databases such as Oracle, but I've never tried with MySQL, although I guess it is possible.

Conclusion: If you really, really have to, use Access to do the screens, the reports, the forms and all the other objects... but NO TO STORE THE INFORMATION.

[molona]

Access doesn't really have a place in any significant database project. It is basically aimed at the home market for people who want to be able to store info for home use without having to learn advanced Excel to do it.

Off Topic:

Can't agree with this. The purpose of Excel is to analyse information while the purpose of a database is to store it safely. Try to store more than 5000 reconds is an excel file... (even if you have only one sheet). It is a nightmare.

Using a database will not save anyone to learn advanced Excel. The opposite is also true. Someone that knows advanced Excel will not know about databases.

So thus, the answer to your question is: MS Access is not scalable at all.

90% agree with this. The only exception is when you use Access as a project and no as a database

[cydewaze]

Access only allows one connection at a time, so if there are two simultaneous requests on your website (two different people visiting at the same time), the 2nd person has to wait until the 1st person's connection has closed. This makes websites incredibly slow and even completely unreachable as they continue to get more traffic.

We'll basically be using the database to store the contact info of the content owners for each page. So when a user loads the page, it basically fetches a name and email address out of the database to display on the page. I would think this would happen pretty quickly, so even if a few people happened to hit the page at exactly the same time, there would be a small delay for two of them.

We have other pages that are more intensive (like the page that builds our office directory) but that shouldn't take horribly long to load either. What worried me most is that all these pages are using the same database (the page contacts, office directory, project list, etc are all different tables in a single db) so I'm worried that if a few people are using one of the more intensive pages, the people loading all the other ones will experience lag.

I'm just trying to get a sense of the impact of this before I go lobbying for any changes.

[TheOriginalH]

I'm just trying to get a sense of the impact of this before I go lobbying for any changes.

Federal gov't website? Well the answer to the above depends on whether or not it's a long forgotten backwater of gvt people never visit or not. If you have even a relatively small number of visitors, concurrent access is going to be a huge problem, and will probably cause crashes as well as delays.

Impact of using Access as anything but a front end in an environment with more than one (or a couple, generously) of users == BAD

[cydewaze]

Well the answer to the above depends on whether or not it's a long forgotten backwater of gvt people never visit or not.

Haha, no, it's a fairly non-controversial agency with pretty vanilla info. It'll get a fair number of visitors.

[bluebenz]

I was use Ms. Access for desktop application.
On one of my clients, they have a huge data so it's almost 4GB (I think..), and my program won't work.

Trying to find answer, and found that 4GB is the maximum file size of Ms. Access.

I forgot about the 4GB and the version of Ms. Access, please some one correct me..

[molona]

We'll basically be using the database to store the contact info of the content owners for each page. So when a user loads the page, it basically fetches a name and email address out of the database to display on the page.

That's personal information and I would think that there's a law controlling how it should be stored. You should take that into account as well because Access is known for not being very secure. After all, its purpose is to be used at home or at a small office, but not for big projects.

[cydewaze]

That's personal information and I would think that there's a law controlling how it should be stored.

It's not really personal info. The information in the database is just the the contact information (name, email address) of the person responsible for the page - the same exact information that gets displayed on the website. There isn't any sensitive data in the databases, it's just the information that gets displayed on the website.

[Czaries]

We have other pages that are more intensive (like the page that builds our office directory) but that shouldn't take horribly long to load either. What worried me most is that all these pages are using the same database (the page contacts, office directory, project list, etc are all different tables in a single db) so I'm worried that if a few people are using one of the more intensive pages, the people loading all the other ones will experience lag.

Yes, that's exactly what is going to happen. It won't matter which page the user is on. The limit is one connection - one user - at a time, not one user per page at a time. So even if you have 10 users all on different pages, they all still have to connect to the database. They will get queued up and wait for their opportunity to connect to the access database since there are no concurrent connections. The net effect is that your entire website will become very slow for all users as traffic increases, and there's no way to scale.

Access was made as a desktop database. One user at a time isn't a problem on the desktop. But it's a huge problem for websites.

[verschha]

I really don't know where to start, because I can't believe the xxxxx things I hear

First news I had about this

Access normally comes with Office, and you pay for it

And, unless things have changed much, you also have to pay for MSSQL.

In a web environment, you cannot use Access. Yes, you can use a mdb file, but what you're using is actually the underlying Jet Database Engine, meaning you can access the tables, relations and queries in the mdb file. You don't need to have access installed, so you don't have to buy anything to use a mdb file in an web environment to store data!

And about MS SQL, the express edition is completly free:

http://www.microsoft.com/express/sql/default.aspx

I can't believe no one has stated this, but the primary reason you can't use MS Access on any kind of website is due to one critical design specification:

Access only allows one connection at a time, so if there are two simultaneous requests on your website (two different people visiting at the same time), the 2nd person has to wait until the 1st person's connection has closed.

This is total non-sense, although it is something that is heard a lot. Access, and the underlying Jet Database Engine, in fact allow up to 255 simultanious users, as you can see in the specifications

Access will not be good for an online store, as an example. When it reaches 20000-30000 records, it simply does not work well (of course, this is not valid for all databases, it depends on the amount tables and number of fields in each table, and how the tables are connected, etc)

Really? Did you test this, because when you do you'll experience that simply ain't true.

Access can open connections to various tables but only one database, while a database manager allow you to connect to various databases and this helps with scalability

Also non-sense. In an Access database application, you can connect to mulriple database, including to other databases like MS SQL server.

I was use Ms. Access for desktop application.
On one of my clients, they have a huge data so it's almost 4GB (I think..), and my program won't work.

Trying to find answer, and found that 4GB is the maximum file size of Ms. Access.

I forgot about the 4GB and the version of Ms. Access, please some one correct me..

The max size limit of an MDB file is 2 GB, see the specifications...

I can only conclude that some members here don't know what they're talking about regarding Access, especially in a web environment. In my previous post, I already stated that MySQL and MS SQL are better (off course), but for a lot of websites (even larger website with a lot of traffic) it works perfectly, and as I already stated, it is free.

One issue, that isn't discussed here, can be a major problem, and that is security. You can password protect a mdb file, but that can be hacked easily. FOr a federal gov website, I really can't believe that they even consider to use Access because of the security issue. I would recommend to upsize all tables to MS SQL (Express), which is very easy when you have access using the upsize wizard....

[cydewaze]

Oye! Conflicting info.

In a web environment, you cannot use Access. Yes, you can use a mdb file, but what you're using is actually the underlying Jet Database Engine, meaning you can access the tables, relations and queries in the mdb file. You don't need to have access installed, so you don't have to buy anything to use a mdb file in an web environment to store data!

Well we're creating the database in the desktop version of Access, then copying the mdb file to the web server. Then we point to that mdb file in the ColdFusion administrator (which, by the way, lists Access as one of the choices, making me think that some people somewhere are using Access mdb files for websites).

One issue, that isn't discussed here, can be a major problem, and that is security. You can password protect a mdb file, but that can be hacked easily. FOr a federal gov website, I really can't believe that they even consider to use Access because of the security issue. I would recommend to upsize all tables to MS SQL (Express), which is very easy when you have access using the upsize wizard....

This merits further discussion. When you're talking about security, do you mean someone will be able to get into the database to view the data, or do you mean they'll be able to get into the database and add/alter the contents?

The former isn't really a problem, but the latter certainly would be.

[gregrobson]

Looking at the previous comments I feel some important considerations are missing.

The JET database engine is not particularly strong compared to OS/commercial RDBMS. I find the databases need compacting far more than you would expect as they grow (double in size!) very quickly! However it does stand up well to multiple connections requiring to read tables (they can all read at the same time). What will limit the database more is how often it is written to as this will lock the tables and hold up other write processes. Judging from previous comments, updates will be infrequent by comparison to reads, so it should perform okay with the correct hardware. Scribble down a piece of paper how many inserts and updates you imagine will be done, how many will it be per minute? Does that seem realistic?

By far the most limiting factor in any database is the human who builds it. Many people criticise MS Access and I find this somewhat unfair as I have read many blog posts and articles by people who have inherited databases that people complain about only to find that:
1) Tables are not normalised
2) Indexes are not properly applied
3) The database has not been split into interface/back-end.

A few days later 800MB becomes 40MB and the 20min query becomes .2 seconds!

If the design is well normalised, indexed and implemented properly then you may find its fine with the right hardware, if not, then its SQL Server or MySQL or another RDBMS I'm afraid.

[Czaries]

This article is a great read on the topic:
http://databases.aspfaq.com/database...ms-access.html

And while it is true that Jet now supports up to 255 concurrent connections (I apologize for perpetuating that old 1-connection limit "knowledge"), Microsoft has frequently said it's highly recommended that "less that 10" concurrent connections are established using Jet.

From the article:

Jet can support up to 255 concurrent users, but performance of the file-based architecture can prevent its use for many concurrent users. In general, it is best to use Jet for 10 or fewer concurrent users.

Also:

While Microsoft Jet is consciously (and continually) updated with many quality, functional, and performance improvements, it was not intended (or architected) for the high-stress performance required by 24x7 scenarios, ACID transactions, or unlimited users, that is, scenarios where there has to be absolute data integrity or very high concurrency.

So again the same conclusion - not fit for websites. Don't use it.

[felgall]

Those "huge" specifications that are said to be way too big for Access - 30,000 records, 4Gb of data, 255 concurrent users - are actually quite tiny compared to what a real database is designed to handle - billions of records per table, petaBytes of data, millions of concurrent users.

[cydewaze]

Scribble down a piece of paper how many inserts and updates you imagine will be done, how many will it be per minute? Does that seem realistic?

That's an easy one. Zero. The only time we'd be doing any inserts or updates would be when we have a form up for certain events, and that happens every two years. In the past, we've had problems with these forms though (random ColdFusion errors) so maybe that was an Access problem? Many people would be hitting that db in a short period of time.

As far as your other comments go, it sounds like I need to read up to make sure the databases I'm responsible for are normalized, indexed, and all that other stuff. But fear not, as I've purchased rudy's book!

And while it is true that Jet now supports up to 255 concurrent connections (I apologize for perpetuating that old 1-connection limit "knowledge"), Microsoft has frequently said it's highly recommended that "less that 10" concurrent connections are established using Jet.

That was a useful link, thanks. Especially the link to the Adobe page. We use tons of Adobe products (ColdFusion, Dreamweaver) so that link will carry a lot of weight when I make my case to move to something more robust.

It sounds like the MS SQL Express product would be ideal for our situation. I'll probably try to steer them toward that.

Lots of good info here, thanks!

[verschha]

Oye! Conflicting info.


Well we're creating the database in the desktop version of Access, then copying the mdb file to the web server. Then we point to that mdb file in the ColdFusion administrator (which, by the way, lists Access as one of the choices, making me think that some people somewhere are using Access mdb files for websites).

You don't need to have access for creating an mdb file and creating tables, relations and query's. It all can be done in code, for example in ASP. An example of this is Database Administrator for MS Access

This merits further discussion. When you're talking about security, do you mean someone will be able to get into the database to view the data, or do you mean they'll be able to get into the database and add/alter the contents?

The former isn't really a problem, but the latter certainly would be.

Off course you'll need to store the database somewhere on the filesystem, where web user don't have access. But what I mean is that everybody who has access to the mdb file (in your company), will be able to view (and alter) the content, because the password protection can be hacked easily.

What will limit the database more is how often it is written to as this will lock the tables and hold up other write processes. Judging from previous comments, updates will be infrequent by comparison to reads, so it should perform okay with the correct hardware. Scribble down a piece of paper how many inserts and updates you imagine will be done, how many will it be per minute? Does that seem realistic?

By far the most limiting factor in any database is the human who builds it. Many people criticise MS Access and I find this somewhat unfair as I have read many blog posts and articles by people who have inherited databases that people complain about only to find that:
1) Tables are not normalised
2) Indexes are not properly applied
3) The database has not been split into interface/back-end.

A few days later 800MB becomes 40MB and the 20min query becomes .2 seconds!

What will limit the database more is how often it is written to as this will lock the tables and hold up other write processes. Judging from previous comments, updates will be infrequent by comparison to reads, so it should perform okay with the correct hardware. Scribble down a piece of paper how many inserts and updates you imagine will be done, how many will it be per minute? Does that seem realistic?

By far the most limiting factor in any database is the human who builds it. Many people criticise MS Access and I find this somewhat unfair as I have read many blog posts and articles by people who have inherited databases that people complain about only to find that:
1) Tables are not normalised
2) Indexes are not properly applied
3) The database has not been split into interface/back-end.

A few days later 800MB becomes 40MB and the 20min query becomes .2 seconds!

Exactly!

If the design is well normalised, indexed and implemented properly then you may find its fine with the right hardware, if not, then its SQL Server or MySQL or another RDBMS I'm afraid.

Even if you use SQL Server or any other RDBMS, if the design is not wel normalised, indexed and implemented or running of the right hardware you'll be in trouble also. But if you know the do's and don't from Access (Jet), it can be a good and also cheap alternative...

And while it is true that Jet now supports up to 255 concurrent connections (I apologize for perpetuating that old 1-connection limit "knowledge"), Microsoft has frequently said it's highly recommended that "less that 10" concurrent connections are established using Jet.

In a web environment, how many concurrent connection do you expect? When somebody visits a database driven website, the connection is only opened for a few miliseconds, the data is retrieved and the connection is closed (if well written). So even when 1000 users are visiting your website at any given time, they're not retrieving data all at the same time.

So again the same conclusion - not fit for websites. Don't use it.

No, it ain't intented for high performance websites. But there are many, many websites that aren't high performance (although we may want to ). For example websites running in a shared hosting environment. If they would have a lot of visitiors, they won't be running in a shared environment anyway. I really don't see any reason why not to use a Jet Database Engine in a shared hosting environment, because the performance issue will not be the Database Engine, but the hosting environment....

[felgall]

No, it ain't intented for high performance websites. But there are many, many websites that aren't high performance (although we may want to ). For example websites running in a shared hosting environment. If they would have a lot of visitiors, they won't be running in a shared environment anyway. I really don't see any reason why not to use a Jet Database Engine in a shared hosting environment, because the performance issue will not be the Database Engine, but the hosting environment....

But as the OP is talking about a Government web site it is certainly not going to be running in a shared hosting environment and it almost certainly is going to get a huge number of visitors (if not all the time then at least occassionally).