Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help!: Handling largeish result sets with ASP

Status
Not open for further replies.
pha2er

pha2er

Programmer
Dec 4, 2001
16
GB
Hi,

I have a small ASP script which queries a SQL Server 2000 database. The query is a simple select statement and returns ~28000 rows. The query runs in about 4 secs from query analyser. My code is:

<%@ Language=VBScript %>
<!-- #INCLUDE VIRTUAL &quot;/includes/DataSource.asp&quot; -->
<HTML>
<HEAD>
</HEAD>
<BODY>
<%
Server.ScriptTimeout = 999999999

count = 1
set conn = server.createobject(&quot;adodb.connection&quot;)
conn.open strConnect
SQLQuery = &quot;SELECT eid,min([timestamp]) as [timestamp] FROM open_track WHERE cid ='2' GROUP BY eid&quot;
set rs = server.createobject(&quot;adodb.recordset&quot;)
res = &quot;&quot;
rs.open SQLQuery, conn
If rs.EOF and rs.BOF then
result = result & &quot;No Data Avalable&quot;
Else
rs.MoveFirst
while Not rs.BOF and Not rs.EOF
res = res & rs(&quot;eid&quot;) & &quot; : &quot; & rs(&quot;timestamp&quot;) & &quot;<br />&quot;

rs.movenext
count = count +1
wend
End If
rs.close
conn.close()
Set conn = nothing
test = result
response.write res
%>
</BODY>
</HTML>

The script completely locks up the server.

I think that the problem could be to do with building such a large string (approx 900000 characters), but I really need to do this. Is there any way that anyone can think of to get whatI want?

Thanks in advance - phatoo-er
 
Sort by date Sort by votes
I think you may be onto the problem with the HUGE string.
Maybe you might want to think about outputting the string during the WHILE loop. That way you don't have that issue to deal with.
 
Upvote 0 Downvote
hi,
instead of retriving the records as
rs(&quot;eid&quot;) & &quot; : &quot; & rs(&quot;timestamp&quot;)

why can't u try with
arrVariable = rs.getRows then display from the array..
i feel this will even improve the performance...

hope this will work!!!!
 
Upvote 0 Downvote
Or if you simply MUST have it in one HUUGE string, how about:
(I haven't tested it)

str_HUGEOutputString = rs.GetString(2,, &quot;:&quot;, &quot;<br />&quot;, &quot;&quot;)

or if that doesn't quite work:

str_HUGEOutputString = rs.GetString(2, 1000000, &quot;:&quot;, &quot;<br />&quot;, &quot;&quot;) codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
<insert witticism here>
 
Upvote 0 Downvote
it is said that this type of concatenation results in run times that are proportional to the square of the number of concatenations. i.e., the more times you concat, the longer and longer it takes for each successive concatenation.

notice how the performance, as shown in this article, gets proportionally worse with the increased number of concats, and he only shows 2000 concats, not 28,000!


if your only purpose is to output the results to the screen, i would agree with the earlier suggestion to output the string during the WHILE loop and ditch the concat approach.

for best performance, i would recommend combining this suggestion along with the GETSTRING/GETROWS option. this will further reduce your demand on and connection time to the database server.

also, consider using RESPONSE.FLUSH on every thousand or so of your .WRITES, so that the perceived response time for the user is greater. (this also prevents IIS from buffering and waiting for the entire page to finish generating before sending to the client.)

good luck!
 
Upvote 0 Downvote
I agree with the getrows method into a variant, this will improve performance slighly if you have a very big recordset. This method would then allow you to just response.write in the loop rather than build up the string, VB is crap with string handling so the less concatenation you do the better

<%
arrData = rs.GetRows
...
for i = lbound(arrData,2) to ubound(arrData,2) %>
<%=arrData(0,i)%> : <%=arrData(1,i)%>
<%
next
%>
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
433
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
451
xwb
xwb
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
366
penguinspeaks
penguinspeaks
JScannell
  • Locked
  • Question
Border issue with Google Chrome on an Android phone
Replies
0
Views
335
JScannell
JScannell
how2trainyourdragon
  • Locked
  • Question
Request.Server variables blank for classic ASP
Replies
12
Views
800
GriffMG
GriffMG

Part and Inventory Search

Sponsor

Back
Top