1

I migrated my classic asp site from IIS6 to a much powerful server with Windows Server 2008 R2 and IIS7.5, but it actually runs even slower.

Every simple call to the MSAccess database is taking forever. Many times the request is dropped because of Session timeout (120 seconds).

Any idea what can cause the problem and how to solve it?

Thank You.

Yevgeni Grinberg
  • 359
  • 5
  • 19
  • What makes you think that the request from MS Access took all the time? – Mark Aug 12 '13 at 05:42
  • Because asp pages with no db access load fast. Why, what did you have in mind? – Yevgeni Grinberg Aug 12 '13 at 05:47
  • 1
    Of course it's like a normal html, but have you tried to query your request to the access db itself? There are many factors, 1st: Is the speed of your query; 2nd: The connection to your db (your Base DB Classes); 3rd: would be relative to the IIS version you are using. Nevertheless, I suggest that you upgrade your db to MS SQL Server. It's free(Express Edition) and faster than access. – Mark Aug 12 '13 at 05:54

1 Answers1

3

Before blaming Access and moving to SQL Server Express or another database, you need to make sure you know where the slowdown occurs.

From what you are motioning, it looks like at least some of the queries don't even work (IIS times out after 120s).

Access databases, especially if they are accessed locally by a handful of concurrent users, are fast.
Moving to another database may or may not solve the problem, but it will be probably be a lot more work than solving your issue with your current Access database.
That said, if your website needs to server lots of concurrent users (say more than 50 at a time) you may need to look into moving to a full database server like MySQL, SQL Server Express or PostgreSQL for instance.

A few things to make sure you double check:

  • Corrupted database. Make sure you use Compact and Repair regularly as a regular maintenance measure (make a backup first).

  • Incorrect filesystem rights.
    Make sure the your IIS process has read/write rights to the folder where the database is located so that it is able to create the lock file (.ldb or .laccdb depending on whether you are using .mdb or the new .accdb database format).

  • A related issue is that the IIS process must be able to create temporary files in the temporary folder, for instance %SystemDrive%\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp.

  • Bad queries. Open the database with Access and run the queries to check how long they really take and if they return any errors.
    If there are data integrity issues, it could be that the query returns unexpected results that could have strange side-effect to the code in your asp page.

  • Check your IIS logs for errors. Also check the OS Event Log.
    Make sure there are no other errors that could incorrectly cause the behaviour.

  • Make sure you profile your asp code to find out exactly which queries and parts of your code are slow and which are fine.

  • Once you have solved your issues. Improve performance by keeping the database open to avoid the lock file being create/deleted all the time (this can have a huge impact on performance).

A good reference with more detailed information on some of the topics above: Using Classic ASP with Microsoft Access Databases on IIS

Community
  • 1
  • 1
Renaud Bompuis
  • 16,596
  • 4
  • 56
  • 86
  • What do you mean by keep the database open? I open the database with the following code: `Set rs = Server.CreateObject("ADODB.Recordset") rs.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\mydb.accdb" rs.CursorType = adOpenStatic rs.LockType = adLockPessimistic rs.Source = sqlQuery rs.Open rs.Close set rs = Nothing` – Yevgeni Grinberg Aug 12 '13 at 08:36
  • @YevgeniGrinberg Keep one global connection open, same code as you mention but don't close it until you shutdown the site. This should will speed-up queries if you perform a lot of them in a short period of time. See [this answer][http://stackoverflow.com/questions/6361947/why-one-public-oledbconnection-is-deprecated-alternative-to-solve-the-bug-too/6367107#6367107] for some more information: – Renaud Bompuis Aug 12 '13 at 08:57
  • @YevgeniGrinberg another thing: do not put your database at the root of a drive. Put it inside a folder and make sure that folder is added to the _Trusted Locations_ in Access so you can open it in Access without problem. Also, in your connection string, specify that you are opening the database in shared mode: add "Mode=12;". See http://stackoverflow.com/questions/13484298/intermittent-system-resource-exceeded-exception-for-oledb-connection-to-micros/13485162#13485162 – Renaud Bompuis Aug 12 '13 at 09:04
  • As you can see in my code, I open the recordset only after setting the `rs.Source`. How can I change it without closing and opening the recordset? – Yevgeni Grinberg Aug 12 '13 at 09:07