Forum Moderators: buckworks
I have an ecommerce site written in classic asp/ado, which is linked to a MSAccess 2000 database. As traffic to the site increases, I worry about the limitations of Access in particular.
Somebody once told me that Access could only handle a handful of users at once, and I'm worried that if the site gets more popular it will start to lag or just won't work as the database gets overworked. Many of the pages are created on-the-fly using a single database with several tables.
1) Is MSAccess 2000 and asp/ado a wise decision to use on a site that (I hope) will at some point have 100 to 1000 concurrent users all accessing the same database?
2) What are the general performance and concurrent user capabilities of PHP/mySQL vs my current setup of ASP/ADO & MSAccess 2000? I'm very busy with the actual 'meat' of the site, and these technical issues are worrying me.
Access is not designed for that kind of punishment. MySQL may well work, although I would consider MS-SQL or Postgres.
I've never used mySQL, but I think I may have to look at switching over to it soon due to Access' limitations. I'm assuming a switch to MS SQL would be more seamless, but I don't have that kind of scratch to throw around. I'd take a look at PostgreSQL but my webhost doesn't support it.
Thanks for the help.
Usually 50 - 100 users hit the database concurrently. We use a simple connection pool class.
Also, we closly watch database performance. (If a query takes longer than 1 second, we log it). So far Access has performed wonderfully. Our product table has 20,000 rows and 16 columns.
Does anybody know the real limits of Access?
Everybody keeps saying Access is no real database, but we are yet to see the downside of it.
But it has alot to do with how well your database is normalized.
-Corey
I can tell you definitely that Access is a lot more robust than most people seem to think. It seems like most people heard Access is no good on the web and they pass around this info without ever considering if it is valid. I've seen many webmasters waste money month per month for a SQL Server solution when an Access database would have worked fine.
I've also used Access for 3 years and we just recently had to upgrade to SQL Server. Before the upgrade I was handling about 1000 unique visitors per week. I've also done consulting on another site where they had 10000 uniques per week and used Access.
Access is great if you're doing mainly SELECT queries. I've heard of other people running into performance issues when you start running a lot of INSERTS and UPDATES.
Don't worry about the 5 concurrent users, most users will only connect to the db for a few milliseconds at a time. You need to have a lot of people visiting a page in order to have 5 concurrent users.
Definitely use OLEDB connection to Recordset.
Compact your db regularly.
Try to make you asp code as efficient as possible.
If you follow the above tips, access can handle a fair amount of users before needing to upgrade.
In my case, more and more users were inserting into my db, so things started to slow down.
Everybody keeps saying Access is no real database, but we are yet to see the downside of it.
The primary issue with Access is that the data is not managed by a database service. The data is stored in a file that is accessed by a client type application. When more than one client tries to get at it, you can have problems.
You can use it fairly reliably from a website that does connection pooling because the website is the client. You can have 15 people hitting your site, but they probably all go through one connection. So only one program actually has the data file open.
When I do programming for desktop applications that need a database, I will usually recommend against using Access if more than one person (one client program) will need to hit the database simultaneously. When such programs do use Access, you will routinely (once every week or two) get a corrupted database when the simultaneous connections go past 5 or 6.
How many people can hit your site before you have problems depends upon a lot of factors. The remote possibility will always be there. As your traffic increases, the possibility gets more real.
Since you are concerned about it at all, you should definitely go with the more robust RDBMS as soon as possible. mySql is probably a good choice. There are also some fairly low-cost hosting solutions available with SQL Server availability. I would go with one of those.
that's definitely not true, I used Access for three years and NEVER had the database get corrupted.
But I wasn't talking about on a website. I was talking about as a desktop application in which Access is on a network drive and accessed by multiple clients. If you have multiple people doing data-entry on a system like this all day long, you will eventually have a collision and your Access file will become corrupted.
When utilized from a website, in which the web-server is the lone client, you don't get the collisions. That only happens when traffic causes multiple connections to be opened. That is why I said that it could be used fairly reliably from a website.
The (poor) reputation of Access was gained from people trying to utilize it like a bullet-proof RDBMS. I was simply pointing out what the weak-point of Access is and why it is a weak point.
I have also used Access on websites with no problems. It works great. But if you use it without knowing which end is up, you do so at your own risk.
without starting a whole other thread, this was a thread about access performance on a website, not a desktop.
When you get multiple connections on Access that frequently write data, you will occassionally get a corrupt data file. Your website traffic may not be high enough to ever create multiple OLEDB connections to the same datafile. And even if it does, it might never cause the type of collision that causes data corruption.
It is possible that it might.
If you are concerened with the possibility of a corrupt datafile, it doesn't make sense to use Access when mySql is free and SQL Server plans can be had for only a few dollars more a month.
I mentioned the desktop experience because it is an environment that amplifies the liklihood of data corruption. A very high volume website will have the same experience. 1000 and 10000 unique visitors a week does not fall into this category.
I run 9 eComm sites with each having it's own Access DB to hold orders. Never a problem. I have a central MySQL database that has the Access orders dumped into it at various times throughout the day.
You can actually trap for any concurrent user errors Access *may* produce and just put the user into a loop until their order is able to be inserted. Although, you are likely to see a need for it.
That's the category that I'm in and thats why I gave my traffic stats as an example. You're right, for very high volume sites, Access can't handle the load. But I would say that most webmasters who are looking into Access, they probably don't have a high volume site or they wouldn't be asking such questions.
I am saying that if you are expecting 1000 concurrent users AND you are concerned with the impact of a corrupt database, then you should not use Access for your website.
Here's a virtual beer:
¦~~¦
¦ ¦)
¦ ¦
----
Cheers!
So I agree with you both. Access can handle a fair amount of users, but if it is always a better option to go with SQLserver or MySQL if you have the resource available.
