ASP & Mysql Speed

[PhillipH]

Hello

one of my games ive coded at http://www.gatesofatlantis.com uses the following asp code to query a mysql database and then print each line to the screen - rankings page (both asp page and mysql server on the same computer) :

strconn="DRIVER={MySQL ODBC 3.51 Driver};Server=servername;Database=databasename;Uid=username;Pwd=passwordname"
Set CONN = Server.CreateObject("ADODB.Connection")
CONN.Open strconn

myquery = "SELECT * FROM tablename ORDER by score DESC"
set query=Conn.execute(myquery)
do while not query.eof

ccharactername=query("charactername")
ckingdom=query("kingdomname")
crace=query("racename")
cscore=query("scorename")
cacres=query("maxacresname")
cworship=query("worshipname")
i=i+1
	
response.write ("<tr><td class='x'>" & i & "</td><td class='x'>" & ccharactername & "</td><td class='x'>" & ckingdom & "</td><td class='x'>" & crace & "</td><td class='x'>" & cworship & "</td><td class='x'>" & cscore & "</td></tr>")

set conn = nothing
query.movenext
loop

My question is about optimisation, although its not real slow it could be alot quicker, especially as ingame it has to read 1 record from a database on every page (as the values in the database change constantly). The code for reading 1 record from the database is :

strconn="DRIVER={MySQL ODBC 3.51 Driver};Server=servername;Database=datbasename;Uid=username;pwd=passwordname"
Set CONN = Server.CreateObject("ADODB.Connection")
CONN.Open strconn

myquery = "SELECT * FROM tablename WHERE password = '" & passwordname & "'"
set query=Conn.execute(myquery)

charactername=query("charactername")
kingdom=query("kingdomname")
password=query("passwordname")
email=query("emailname")
race=query("racename")
endurance=query("endurancename")

'it reads in about 200 field names into variables.

set conn = nothing

now as you can appreciate doing this to read 1 record on every page (or turn taken on the game) wont be fast.

can anyone optimise this or give me tips on how to optimise the above ?

thanks.