Access Database Limitations

Stuck to using backend .mdbs, how will it perform online?

[brickwall]

Iam developing a content site that will be positioned in a crowded affiliate marketing sector once it is finished.

I have no choice at the moment but use Access as the database backend (no choice, really).

Realistically,

(1) can my site handle say 1000 pageviews daily if much of the content is pulled from Access tables? How about 5000 page views? Let's assume that the db is well designed.

(2) how many concurrent users can this site support at any given time?

(3) I have broken down the entire database into multiple independent mdb files to lighten up the load of each mdb. Will this help?

By the way, Iam using ADO 2.8, dsn-less.

When SQL Server is finally available, I'll migrate. But for now Iam really stucked with Access.

Hope you guys can help out.

Thanks.

[paladin]

From what I have seen access can handle either 10 or 100 read request concurently so that should be OK for the most part. The problem you may have is that access can only handle one write request at a time, and while it is writing no one else can read.

If possible try to seperate the reading and writing databases and set up somekind of error handeling incaes you get a file locked/in use error.

Hope this helps a bit.

[txbakers]

As long as your site doesn't write, insert, update, or delete you're OK.

It's the I/O that causes access to crap out.

And it will crash.

[brickwall]

Thanks guys.

The writing part, that I didn't know about. There are only two situations where I need to write data:

1) During member registration, newsletter subscription.

So I need to a separate mdb to hold data for this procedure. Is that right?

2) During updating of the "article viewed XX times" counter.

So it would be best if I take out this procedure while Iam using Access because it can become a potential pitfall. Is this correct?

I really appreciate your answers. Helps me to make appropriate changes before problems occur.

[mattur]

1) During member registration, newsletter subscription.
So I need to a separate mdb to hold data for this procedure. Is that right?

If you use a separate "data input" db from your "content delivery" db, it will mean that if/when the "data input" db locks up, your content pages, using the other db, will probably still work.

2) During updating of the "article viewed XX times" counter.
So it would be best if I take out this procedure while Iam using Access because it can become a potential pitfall. Is this correct?

Yes take it out altogether. Most sites function effectively without having this "feature"... ;)

[Easy_Coder]

I believe that access has a limitation of 255 concurrent connections...

[randallxski]

I just recently converted a production Access database over to MySQL with a simple change to the connection string. Keep your SQL simple and standard to simplify your switch to a production-ready database in the future. In our case, it took a few years before we needed to convert to something more robust. It sounds like your time may be shorter.

[brickwall]

Yes, Iam prepared for the eventual migration to an SQL Server backend. Just needs to change my conn strings.

One last question:

When you say the mdb "will" crash during multiple i/o, what do you actually mean?

a) an error will occur to those sessions attempting db operations while the mdb is in use (writing, updating, deleting etc.)? but will be ok once again after the prior operation has completed?

b) or, the db will "die" and will become unusable? (Oh please, don't let this one be the answer...)

[randallxski]

When you say the mdb "will" crash during multiple i/o, what do you actually mean?

a) an error will occur to those sessions attempting db operations while the mdb is in use (writing, updating, deleting etc.)? but will be ok once again after the prior operation has completed?

b) or, the db will "die" and will become unusable? (Oh please, don't let this one be the answer...)

I've found that once a session gets an error, all sessions will get an error until:

1) there are NO transactions for roughly 30 seconds or
2) IIS is restarted

[txbakers]

Sorry, but the answer is "b" it will totally die and you'll need to restart IIS.

Access is not a database server. It's a wonderful desktop database, but will not handle the constant I/O of web traffic.

And migrating to a real database isn't just a matter of changing connection strings. Access handles the SQL language differently than SQL Server or other real databases.

Dates and Outer Joins are two major differences. Anything coded with date comparisons in Access will have to be recoded when you go to a real databse.

[brickwall]

Uh-oh, then this is really serious.

Any way around it? What if I setup an Application("dbinuse") = True before starting a db write/update and reset it to Application("dbinuse") = False after completion and check the flag status before committing any db operations? Will this solve the problem?

If yes, what do you think is the best approach to hold off further db ops when the db is busy? Timers, custom error message pages, redirects?

Again, thanks guys. You all have been great.

[wackal]

I would take everything that was just posted with a grain of salt, because most people have not attempted to use Access for their site, they just heard from other people that it's not a good db for the web.

I'm on my second site that uses Access as the backend and so far not one problem. I have an e-commerce site that used Access 2000 for 3 years and not once did the db crash. this site gets about 500 unique visitors per day.

I built a blog site for someone else again using Access 2000 as the backend. This site received about 10000 unique visitors per week and again, Access did not crash in the 6 months I was involved in the site.

That being said, eventually you will need to upgrade, but there are a lot of specific things you can do to minimize/avoid problems. Here they are:

optimize ASP code

use OLEDB for the connection

optimize SQL queries

use separate db's for reading and writing data, if possible

use separate db's for different tables if table will be getting heavy use

try to compact database once per month

that's about it off the top of my head. if I think of any other tricks I used, I'll post back.

[txbakers]

because most people have not attempted to use Access for their site, they just heard from other people that it's not a good db for the web.

I guess I'm the exception then. I started with access and watched my site slow and slow and slow until it was unusable and I had to restart IIS every time.

I lost a very good customer due to this. I was on their site and she told me that it was slow, could I take a look. I looked and was appalled about how bad it was.

The very next weekend I migrated to mySQL and NEVER had a problem since. That was over 3 years ago.

Access is NOT a server database and never will be, no matter how well you optimize. It's not designed for that.

[wackal]

yes, but did you also follow all the other suggestions? I guess if you just use Access without any optimization, then you may have a problem.

[brickwall]

I actually have no choice but to use Access as stated earlier, so the optimization route is all I have. I have been using mdbs in all my desktop projects without any problems for years, but it will be the first time that I will be using Access on a web project. I have will be uploading the finished site in a few weeks so I need to optimize the best I can. There is no way I can simulate such things as web activity because Iam currently using a single development PC running PWS (poor me). Iam a better marketer than developer so after I start promoting the site, I expect to get very decent traffic. Problem is, Iam not sure if Access can handle it.

The inputs you have given is already tremendous help. If there are still things I need to know, please do post it here. I will be forever gratefull.

[dataguy]

I'm also someone who used Access for years without one crash. Because of all the horror stories, I've always made sure to use the most efficient coding as I've known how. These sites had some pretty substantial traffic, too.

About 2 years ago I was 'given' a database server with SQL Server 2000 on it, and I now would/could never go back. Having Enterprise Manager running on my desktop makes things so much easier.

[paladin]

Up until now some have said that it will crash until you restart IIS, some have said that it will work and some said that it will crash temporarily while the database is being written to.

Why don't you set up a simple experiment to see if and when it will fail:

Create a pages called 1.asp that writes a lot of information to the database (maybe in a numbered loop) so that it takes about a minute to complete.

Now create 2.asp that quickly reads a small line from the same database.

Now open 2 brownser windows. In the first window, open 1.asp and in the second window open 2.asp a few seconds after opening the 1.asp page.

From my experience, what will happen is that 2.asp will fail with a "file in use" error until 1.asp is completed, at which time 2.asp will work fine.

[txbakers]

Then have 5 friends access those same pages for about a week.

Then track how long it takes before you have to restart IIS.

[Easy_Coder]

because most people have not attempted to use Access for their site

That's just silly...

I started my first commerce with with access back in 97. It worked ok for the first year but started to have problems when I got to 50K page views per month or when a crawler came chuggin along. So I upgraded...

brickwall, once you have your site ready for testing go out and find yourself a multi threaded stress tool. I know MS has one called the WCAT. Point it at your site and unleash some heavy duty test scripts to see just how it holds up under the scenarios that you're describing. Then pound it with 10 times what you think you'll get for traffic. The idea is to find a point where your application will break so that you'll know when it's time to either reboot or upgrade to a differnt database.

[brickwall]

I'll do that Easy_Coder.
I'll be uploading this weekend and do some test runs. I believe that the db is now optimized. Followed a lot of suggestions made here. I'll report back after I have some decent and conclusive data in my hands, for the benefit of the community.

Thanks guys.

[wackal]

perhaps I should have said "because most people have not attempted to use Access CORRECTLY for their site"

you used Access 97? no wonder you had problems. Access 97 was a piece of crap! My db was corrupting once a week with Access 97.

My suggestion was to use Access 2000 or above and to do all the optimization I suggested. Only then can you avoid problems with Access.

[Zaphod Beeblebrox]

It's only an issue if you get a lot of traffic.

Access becomes more unstable when there are about 5 or 6 concurrent connections. Given that the connections are only there for as long as the ASP page is being run, the average connection time will be in the range of several milliseconds.

Suppose you get 10 pageviews a second, the chance of 5 of them accessing the database at the same time is still pretty low.

10 visitors per second equals 864,000 pageviews a day, or 25.92 million pageviews a month. Once you reach that number, you should be able to rent a dedicated server off your advertising income...

[brickwall]

Ok guys, Iam finally online and testing. I don't have any conclusive data to share yet (need a break because of the holidays). But when I do have the stress results in I'll post it here.

[Easy_Coder]

-->online and testing.
-->I don't have any conclusive data to share yet
-->the stress results in I'll post it here.

kudos to you for taking a 'best practices' approach! I'm curious to see where you're breaking point will be.

[rogerd]

Hmmm, missed this thread last week. I've got multiple sites running ecommerce shops that use an Access DB. They handle plenty of traffic - as many as 40K dynamic pageviews per day (often heavily concentrated in a few hour period). When I first installed it, I figured we'd have to upgrade to SQL, but performance and reliability haven't been an issue.

[dataguy]

I've got multiple sites running ecommerce shops that use an Access DB.

Is this something that is home-brewed or off the shelf? I have a client looking for cart software that uses Access...

[s1dev]

Access is fine for prototyping (ie one person testing), but I wouldn't use it in production environment.
As the previous posts stated, Access cannot handle multiple users when writing to the db. Unfortunately, reading and writing go together. I'd suggest making the switch to a real DB server first.
Your sites performance will appear very slow and users will not return.

[raptorix]

A good intermediate is to use microsoft data engine, which is free and can be found at: [microsoft.com...]

Its the light version of sqlserver and works fine. If you have a lot of access specific functions or views you can also work with "linked tables", i tested it on a complex access database and it works pretty good.

[sinixstar]

Do yourself another big favor if you're doing a site in access, and plan on migrating to sql-server in the future. Be extremely mindful of how you type your data. SqlServer is a lot more specific about datatypes then access - and if you're not careful, you can run into some HUGE problems when you're trying to migrate. I'm actually having that problem now. I'm working with a site that someone else designed a long time ago, and they didn't pay attention to how data was typed, and now that i'm trying to move it into a real database - i'm having all sorts of problems.

[Zaphod Beeblebrox]

Its the light version of sqlserver and works fine.

Actually, it's the full swing version of SQL Server, only without the user interface.