sql 2005 the best way to loop and fetch through a large numeber of records with asp

1 more point to add.
- Use GetRows() instead of looping through a recordset. This will help especially for large recordsets.Read http://www.w3schools.com/ado/met_rs_getrows.asp on GetRows()

Cheers
hongjun

And also, if you are doing paging in SQL 2005, read below
The new ROW_NUMBER() has improved efficiency
http://serena-yeoh.blogspot.com/2005/04/thelongawaitedfeature.html

hongjun

Hi guys

My question is,if I have to loop through sat 1,000,000 records, if I create a record set will it hold the recordset in memory (or try to) or is it ok to construct a large recordset to fetch data and the server will take care of the size issu.

Are you really going to retrieve 1 million records? Are you going to display them in 1 go?!!?!!
Then i suggest you re-design. Don't think it wise to retrieve such large records.

If you wish to do processing or number crunching on the data, I suggest you write some Stored Procedures and then do the processing in SQL Server instead of ASP. The stored procedure will return some values to ASP.


hongjun

BTW, If you want to loop, use GetRows

Dim aArray
Dim i

aArray = rs.GetRows
rs.Close
Set rs = Nothing

i = 0
' Loop thru array
Do While i <= UBound(aArray, 2)
      Response.Write "Field1 = " & aArray(0, I) & ", Field2 = " & aArray(1, I) & ", and so on..."
      Response.Write "<br>"

      i = i + 1
Loop

>>My question is,if I have to loop through sat 1,000,000 records...

I believe there is NO user will wait you to load 1 million records... and they will not read those records row by row...

So you may try to retrieve the records you needed only, perhaps you need "paging" in your page, that probably only display N records in your page instead.

II am trying to update 1,000,records.

They were added from an asp script.

I have to update some fields say set first_name to a specific value.

I just have to wait until all records are processed. Nobady has to wait to show them anywhere

Do I select record by record or a big chunk or somewhere intermmediate?

>>I am trying to update 1,000,records
>>I have to update some fields say set first_name to a specific value.

You can do that by using a "Update" sql statement, that will best suit your requirement, and that's the most faster way I can suggested for a mass update.

like:

...
SQLStr = "Update yourTable set FirstName = 'New Name... ' where yourCondition = True "
conn.execute SQLstr
...