Code taking long to run:

[Brent]

Hello all.. I have a recordset that has 12 records in it, and about 25 columns. Unfortunately, I have to write the records out as columns in an ASP page, and the columns as rows. So, what I have done is a Do Until myRS.EOF and write all the records in <td></td> tags. I then do a myRS.MoveFirst before going to the next line. So basically, I am writing until EOF and moving first about 25 times. I have 3 webpages that do this, and 2 of them work great. This code runs in a couple seconds on the other pages. However I have one page that is identical in code, just using a different recordset, that is taking about 7 seconds to write per line. The other 2 pages are taking less than a second per line. Has anyone seen anything like this before? Anyone have any suggestions? Below is an example of a loop that is taking so long to run. Thanks for your help

do until myRS.EO
Response.Write("<td bgcolor=#eeeeee>" & myRS("OutstandingInCompliance") & "</td>" & vbcrlf
iTotal = iTotal + cdbl(myRS("OutstandingInCompliance")
myRS.MoveNex
loo

 

[Chris Hohmann]

Hello all.. I have a recordset that has 12 records in it, and about 25

columns. Unfortunately, I have to write the records out as columns in an
ASP page, and the columns as rows. So, what I have done is a Do Until
myRS.EOF and write all the records in <td></td> tags. I then do a
myRS.MoveFirst before going to the next line. So basically, I am writing
until EOF and moving first about 25 times. I have 3 webpages that do
this, and 2 of them work great. This code runs in a couple seconds on
the other pages. However I have one page that is identical in code, just
using a different recordset, that is taking about 7 seconds to write per
line. The other 2 pages are taking less than a second per line. Has
anyone seen anything like this before? Anyone have any suggestions?
Below is an example of a loop that is taking so long to run. Thanks for
your help.

do until myRS.EOF
Response.Write("<td bgcolor=#eeeeee>" &

iTotal = iTotal + cdbl(myRS("OutstandingInCompliance"))
myRS.MoveNext
loop

Using the Recordset.GetRows method. You should see some substantial
improvements in the performance of your page.

<%
Dim cn,rs,arr
Set cn = CreateObject("ADODB.Connection")
cn.Open "<DSNLess OLEDB Connection String>"
Set rs = cn.Execute("<SELECT statement>")
If Not rs.EOF Then arr = rs.GetRows()
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing

If IsArray(arr) Then
Dim i,iMax,j,jMax
iMax = UBound(arr,1)
jMax = UBound(arr,2)
Response.Write "<table>"
For i = 0 To iMax
Response.Write "<tr>"
For j = 0 To jMax
Response.Write "<td>"
Response.Write Server.HTMLEncode(arr(i,j))
Response.Write "</td>"
Next
Response.Write "</tr>"
Next
Response.Write "</table>"
Else
Response.Write "No Records"
End If
%>

Here's an article on the pros and cons of various methods for display
recordset data:
http://aspfaq.com/2467

HTH
-Chris Hohmann

 

[Brent]

Ok, so I've tried running this page one of two ways. The first is using a stored procedure (that takes an sql string and just runs it). The second is just having ASP open the recordset without a stored procedure. Using a stored procedure takes about 7 seconds to write each line (and there are about 25 lines to write). Not using a stored procedure takes < 1 second to write every line. Just an FYI.