Hello,
I am looking for a fast way to do the following:
Loop through a set of records ordered by the date they last logged on (LogOnDate). For each record in the loop I want to display the user’s city and state (which requires accessing a separate data base that associates zip codes with city and state).

The way I doing this currently is:
Do While Not oRSusers.EOF
…..
    oRSzipCodes.Find "ZIPCode = '" & oRSusers("ZipCode") & "'"
    Response.Write oRSzipCodes("City") & ", " & oRSzipCodes("State")
    oRSzipCodes.MoveFirst ‘THIS “RESETS” THE RS SO THE FIND WILL WORK ON THE NEXT LOOP AS FIND CAN ONLY SEARCH FORWARD
Loop

This works fine, but takes so long to execute (almost 1 second per record). I found I could speed it up greatly (so that 30 records displayed almost instantaneously) by ordering the records by ZipCode ASC and removing the “oRSzipCodes.MoveFirst”. This is great but I need to display the records by LogOnDate.

Any ideas how I can increase execution speed but still order the records properly?

Thanks, Mischa

What is the query that you use? Can you post that along with the table structures too. I hope a join would solve it for you. BTW, what is the database that you use in your case?

_________________________
- Vijay G
Strive for Perfection

oRSusers.Open "SELECT UserID, Photo1, UserName, DOB, MySex, ZipCode, YourAge, YourSex, FriendSex, EmailSex, TravelSex, HeadLine, DescribeYourSelf FROM UserData ORDER BY UserID ASC", oConn, adLockReadOnly, adLockReadOnly, adCmdText

oRSzipCodes.Open "SELECT ZIPCode, City, State FROM ZIPCodes WHERE CityType = 'D' ORDER BY ZIPCode ASC" , "DSN=zipcodesdsn ", adLockReadOnly, adLockReadOnly, adCmdText

Not sure what your asking for the “table structures”….

Yea, I think a join may just do it. I’ll try that and let you know.

Thanks, Mischa

Yes, you can use join to achieve that, thus avoid finding a match from within the loop for every record. That would be far better than your current approach.

I was asking about the schema of those tables in question, so that to better understand what you are trying to achieve, anyways no problem. The queries you posted were sufficient.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection