Time Consuming Script Problem

Hello All,

I have a script which runs for at least a minute before anything is given back in my call to it in AJAX httprequest.

Can you help me find where I am missing the trick..

here is the code ..

response.Clear()
response.Buffer=true
dim rs
set rs=Server.CreateObject("ADODB.RECORDSET")
call record (rs,"SELECT t.rscompany, CONCAT(t.PKCODE,' - ' , t.TYPE) FROM TBNAME t order by t.TYPE,t.PKCODE")
if not (rs.eof or rs.bof) then
%>
<select id='taskrsid' name='taskrsid' class='input_ radioselect'><option value=''>None</option><%
rs.movefirst
do while not (rs.eof)
%><option value='<%=rs(0)%>'><%=rs(1)%></option>
<%
response.Flush()
rs.movenext
loop
%></select><%
response.Flush()
end if
if rs.State = 1 then rs.Close
set rs = nothing

The database is MySql 4.1.22-community-nt and my connection string has value like this
"DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=dbname; UID=dbuser;PASSWORD=dbpwd; OPTION=16387"

I have checked with AJAX call it is working fine. The problem lies within the code.

The Database table has PK column as varchar(255), with TYPE also varchar(255).

Any help would be great!

Mark

Also I have 13678 Rows in the table.

Without seeing what your "record" function is doing, it's a bit hard to say, but I'd do three things to it: take the buffering off, don't loop through a recordset directly, and stop jumping in and out of ASP:

dim rs, ary, optionValue, optionText

set rs=Server.CreateObject("ADODB.RECORDSET")
call record (rs,"SELECT t.rscompany, CONCAT(t.PKCODE,' - ' , t.TYPE) FROM TBNAME t order by t.TYPE,t.PKCODE")

if not (rs.eof or rs.bof) then

ary = rs.getRows()
rs.Close
set rs = nothing

response.write("<select id='taskrsid' name='taskrsid' class='input_ radioselect'><option value=''>None</option>" & vbCrLf)

for i = lBound(ary,2) to uBound(ary,2)

optionValue = ary(0,i)
optionText = ary(1,i)

response.write(" <option value='")
response.write(optionValue)
response.write("'>")
response.write(optionText)
response.write("</option>" & vbCrLf)

next

response.write("</select>" & vbCrLf)

end if

Without seeing what your "record" function is doing, it's a bit hard to say, but I'd do three things to it: take the buffering off, don't loop through a recordset directly, and stop jumping in and out of ASP:

dim rs, ary, optionValue, optionText

set rs=Server.CreateObject("ADODB.RECORDSET")
call record (rs,"SELECT t.rscompany, CONCAT(t.PKCODE,' - ' , t.TYPE) FROM TBNAME t order by t.TYPE,t.PKCODE")

if not (rs.eof or rs.bof) then

ary = rs.getRows()
rs.Close
set rs = nothing

response.write("<select id='taskrsid' name='taskrsid' class='input_ radioselect'><option value=''>None</option>" & vbCrLf)

for i = lBound(ary,2) to uBound(ary,2)

optionValue = ary(0,i)
optionText = ary(1,i)

response.write(" <option value='")
response.write(optionValue)
response.write("'>")
response.write(optionText)
response.write("</option>" & vbCrLf)

next

response.write("</select>" & vbCrLf)

end if
This is good enough