CODEWITHSUNDEEP
sql-serverasp-classic
J Harley
J Harley

Reputation: 285

Classic ASP retrieving large amounts of data

I have a table that has over 148,000 rows, I have managed to display this by doing: 

<%response.Buffer=false%>

However, this is far from ideal as it needs to be done on the server and it takes so long. Is there a better way of doing this?

Many Thanks, Joel

Upvotes: 1

Views: 3268

Answers (2)

webmaster_sean
webmaster_sean

Reputation: 962

I know this is an older post but I just thought I'd let anyone who comes across this that I wrote a server side script just for this purpose. Check it out at:

http://www.webmastersean.com/2012/11/datatables-classic-asp-server-side.html

Upvotes: 1

Tchami
Tchami

Reputation: 4787

While I agree with the others that displaying 148.000 rows on one page is madness, I just wanted to point your attention to how you should output the data.

Since you didn't specify any code, I'm assuming you're simply looping through your recordset to display the records, like so:

Do While Not RS.EOF
//Output
RS.MoveNext
Loop

This is a really inefficient way of working with data in classic ASP, since each time you're reading a field (i.e. RS("Name")) you're making a request to the database, and the same goes for each MoveNext and each EOF test. Instead you should use GetRows, to get all your elements from the recordset into an array, and then you can go ahead and close the connection to your database.

There's a good article on how to work with GetRows here

Except from the article:

MyArray = rsMyRecordSet.GetRows()

//Close DB connection

Ubound(MyArray,1) 'Returns the Number of Columns
Ubound(MyArray,2) 'Returns the Number of Rows
For lnLoopCounter = 0 To Ubound(MyArray,2)
    Response.Write MyArray(0, lnLoopCounter) _
      & ", " _
      & MyArray(1, lnLoopCounter) _
      & "<BR>" & vbNewLine
Next

Upvotes: 4

Related Questions