PrometheusX
New Member
I've written a web application in ASP which reads 3600 rows from a MySQL database (v5.1.63) and outputs the data in an HTML table. I'm using a recordset to get the data and pagesize/cursorlocation to let the user step forward/backward between the pages. The database contains some 200.000 rows right now but is continuously growing.The page takes longer and longer to load (approx. 15-20 seconds now) and I would like to optimize it if possible.I would be very interested in getting tips on how to improve the performance.Here is the database structure:# Col Type Collation Attributes Null Default1 ID int(11) No None AUTO_INCREMENT2 mean varchar(5) utf8_general_ci No None 3 max varchar(5) utf8_general_ci No None 4 min varchar(5) utf8_general_ci No None 5 dt varchar(20) utf8_general_ci No None 6 dir varchar(2) utf8_general_ci No None 7 log text utf8_general_ci No None Here is the code I'm using:\[code\]' Opening the dbSet oConn = Server.CreateObject("ADODB.Connection")Set oRS = Server.CreateObject("ADODB.Recordset")oConn.ConnectionString ="DRIVER={MySQL};SERVER=<server>;DATABASE=<database>;UID=<uid>;PWD=<pwd>;"oConn.Open' Retrieve 3600 recordssSQL = "SELECT * FROM mytable ORDER BY id DESC"oRS.CursorLocation = adUseServeroRS.PageSize = 6*60oRS.Open sSQL, oConn, adOpenForwardOnly, adLockReadOnlynPageCount = oRS.PageCount...code to set the page selected by the user (nPage)oRS.AbsolutePage = nPageDo While Not (oRS.EOF Or oRS.AbsolutePage <> nPage) ... Response.Write("<td>" & oRS("dt") & "</td>") Response.Write("<td>" & oRS("mean") & "</td>") Response.Write("<td>" & oRS("min") & "</td>") Response.Write("<td>" & oRS("max") & "</td>") ... oRS.MoveNextLoopoRS.Close\[/code\]