Website slow when accessing DB - HELP!!

[Sprout]

Hi

I have a webpage which if a certain criteria is met, will lookup
information from an access database.

The page loads quickly if the database isn't queried, however if the
page 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?

Any comments greatly appreciated!!

[Ray at]

I don't see anything inherently wrong anywhere, like any endless loops or
anything. I can offer a different version of the code to make things a
little more efficient, but nothing that ~should~ make it go from timing out
to being an instantaneous page or anything. Here is an alternate version.
Look at what is different, and if you'd like to know why anything was
changed, post back.


<%
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

SQL = "SELECT [col1],[col2],[col3],[col4],[col5],[col6] "
SQL = SQL & "FROM table1 "
SQL = SQL & "WHERE name = '" & name & "'"

%>
<%
' THIS SECTION IS NORMALLY IN AN INCLUDE
Set Conn = Server.CreateObject("ADODB.Connection")
conString = " Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\w\o\womble\private\"
conString = conString & dbname
%>
<%

set rs=Conn.execute(SQL)

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

End If
%>



Ray at home



"Sprout" <rinkydinky@btconnect.com> wrote in message
news:1n5nqvkv4jbd2iprvq9lfbrkgku7tj4c48@4ax.com...

> Hi
>
> I have a webpage which if a certain criteria is met, will lookup
> information from an access database.
>
> The page loads quickly if the database isn't queried, however if the
> page 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?
>
> Any comments greatly appreciated!!

[Bob Barrows]

Sprout wrote:

> Hi
>
> I have a webpage which if a certain criteria is met, will lookup
> information from an access database.
>
> The page loads quickly if the database isn't queried, however if the
> page 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.

Does the page have the data from the database when it loads after timing
out?

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

<snip>

> At the moment, the database only contains two records.
>
> Have I done something wrong here which will cause performance
> problems?
>
> Any comments greatly appreciated!!

Beyond Ray's suggestion to use the native Jet OLEDB provider, the only
suggestion I can make is to try and narrow down further where the bottleneck
is occurring. Is it happening when the connection is opened? Is it happening
when you run the query?

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"