Slow pages with MS Access DB

Pages load slowly when accessing the database

[Numpty]

Hi

I have a webpage which if a certain criteria is met, will lookup information from an access database. The site is on a Windows 2003 server.

The page loads quickly if the database isn't queried, however if thepage looks info up from the database, the page often (but not all of the time) takes ages to load - it's as though the server needs to timeout before the page loads.

Here is the code that acceses the database (it's been modified slightly with different variable names etc)....

<%
dim dbname, var1, var2, var3, var4, var5, var6, conString
dim formvar, name

dbname = abc.mdb
formvar = Request.Form(formvar)
name = Request.Form(name)

If formvar = "" Then
var1 = "a"
var2 = "b"
var3 = "c"
var4 = "d"
var5 = "e"
var6 = "f"
Else
%>
<%
'THIS SECTION IS NORMALLY IN AN INCLUDE
Set Conn = Server.CreateObject("ADODB.Connection")
conString = "DBQ=D:\w\o\womble\private\"
conString = conString & dbname
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)};" & conString
%>
<%
SQL = "SELECT * "
SQL = SQL & "FROM table1 "
SQL = SQL & "WHERE name = '"&name&"'"

set rs=Conn.execute(SQL)

var1 = rs(1)
var2 = rs(2)
var3 = rs(3)
var4 = rs(4)
var5 = rs(5)
var6 = rs(6)
%>
<%
'THIS SECTION IS NORMALLY IN AN INCLUDE
set rs=nothing
Conn.close
set Conn=nothing

End If
%>

At the moment, the database only contains two records.

Have I done something wrong here which will cause performance problems, or should I be asking questions to the web host?

Any comments greatly appreciated!

[wardbekker]

Well, code looks OK, and two records should be retrieved instantly, even if access is used AND the server is having a hard time. Maybe you can use a website stress tool to see if the slow load times can be reproduced.

[Numpty]

Thanks for your quick reply.

The website stress tool sounds interesting - can you point me in the right direction - I've never heard of such a beast!

[Staffa]

You might also want to check through the code if there is anywhere :

response.buffer = true

If this line is included then the whole processing of your page has to take place before any output is shown to the visitor, which with a large file may take some time.

[bmcgee]

Note - with IIS 5.0, Buffering is turned on by default. So even without that line, it could be on.

But I don't think buffering is likely an issue if the page is simple and is just taking a long time because of data access.

However, to prove that it is the data access, you could do Response.Writes before and after the database calls, and write out the current timestamp. That will confirm that the time is spent in the DB call or not.

[wardbekker]

The microsoft web stress tool is a free download. See [microsoft.com...] for more details

[wackal]

instead of doing "SELECT *", you might want to explicitly name the fields. This will speed things up slightly.

[aspdaddy]

good tip, it saves 2 trips to the db.

Also check that the name field is indexed, use getRows() instead of looping through the open recordset, and make sure the database is compacted.