Why is our MS Access Asp.net so slow?

[Dan V.]

We use dot net, asp.net and iis 5 on a pentium 1.2 GHZ (dual capable - one
in now) and 1 MB RAM and Access 2002.
Our ASP.NET Database queries to MS Access (running locally) are very slow,
(the programmer can't change this)
and they take between 2 - 15 seconds to load a data page -this is even
running locally on the web server. There are multiple web sites on this
server, with not too much traffic.

Our goal is to have 100-200 simultaneous users of this Access database. We
can't change the database right now or the code.
What should we do? Upgrade or buy new?

thanks

 

[Dan V.]

More details:
It is a Dell Poweredge 1500SC running Windows 2000 server on a Pentium 1266
MHZ (dual capable I think- one processor in now) and 1 GB RAM and Access
2002. There is 460 MB free on the C: partition and plenty on the D: drive.

 

[Bernie Yaeger]

Hi Dan,

We would have to see some code to offer some help. It should not be that
difficult to load a page of data from Access unless the tables are
extraordinarily large - eg, more than 300,000 rows.

Bernie Yaeger

 

[Carl Mercier]

Access -is- slow and not suited for 200 simultaneous connections.

If it is slow in design mode (1 connection), it's probably because your
database contains too much information. Access is not suited for a lot of
data.

You should definately take a look at MSDE, SQL Server, MySql and so on.

MSDE might be enough for you if you use ASP.NET caching wisely.

 

[Richard L Rosenheim]

That's interesting. My brother has encountered a very similar problem with
his web application.

Access database (simple queries, and very little data in the tables), plain
ASP code (not ASP.NET), Windows 2003 Server (w/IIS 6). It's taking a long
time for the ASP page to render, even locally. I was thinking maybe the
issue was with the ASP engine, but maybe the problem is with Access.

Unfortunately, I don't have any other useful information to provide, nor
have we been able to track down or resolve the issue.

If I get time, I think I might try converting a page to use a SQL Server
database. See what results I get...

Richard Rosenheim

 

[Dan V.]

I found this article:
http://www.nethost.co.il/heb_v/iis5_performance.htm

And will try it. Trying to find out if it makes a difference if you index
the select and / or where fields in the query.

I didn't test this myself, but if the complex query is run in MS access on
that machine not using ASP.NET at all, it is the same speed I was told.

 

[Dan V.]

I found these as well to try:

MS Access optimization articles:
http://support.microsoft.com/?kbid=286222 -How to optimize Microsoft Access
when using ODBC data sources

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q209126 -Excellent
Information about query performance in an Access database.

 

[Paul Clement]

¤ We use dot net, asp.net and iis 5 on a pentium 1.2 GHZ (dual capable - one
¤ in now) and 1 MB RAM and Access 2002.
¤ Our ASP.NET Database queries to MS Access (running locally) are very slow,
¤ (the programmer can't change this)
¤ and they take between 2 - 15 seconds to load a data page -this is even
¤ running locally on the web server. There are multiple web sites on this
¤ server, with not too much traffic.
¤
¤ Our goal is to have 100-200 simultaneous users of this Access database. We
¤ can't change the database right now or the code.
¤ What should we do? Upgrade or buy new?

Not sure which driver you are using but I would definitely recommend using the Jet OLEDB Provider
instead of the Access ODBC driver.


Paul
~~~~
Microsoft MVP (Visual Basic)