Visiting ASP sites, forums and places like Expert Exchange, I see many examples of ASP code that will not result in good performance. I will make a list of tips on how to achieve good performance. These tips will include ASP (VBScript) coding techniques, SQL query/ADO/SQL Server optimization and alternative ways of doing things (XML, JavaScript on the client, DTS on SQL Server etc.). I am not a performance guru but experience and reading many articles with performance tips I have a good knowledge regarding this. If you have any better solutions than the tips here or if anything is not correct, please let me know.
First tip is the best way to get data from SQL Server. Most people use ADO Recordset and loop through it. This means you have a connection to the SQL Server open while executing the script. A much better way is to use the GetRows() function in ADO:
<pre name="code" class="vb">
Dim db, dbrs, sql, ConnString, users, user
ConnString = “DSN=YourDSN;”
sql = “SELECT UserId, Username FROM Users ORDER BY Username;”
’ GET DATA
set db = server.createobject(“ADODB.Connection”)
db.open ConnString
set dbrs = server.createObject(“ADODB.Recordset”)
dbrs.open sql, db
if NOT dbrs.eof then
users = dbrs.GetRows()
end if
db.close
set db = nothing
’ DISPLAY DATA
for user = lbound(users,2) to ubound(users,2)
response.write “UserId: “ & users(0,user)
response.write “Username: “ & users(1,user)
response.write vbNewLine
next
</pre>
From the ADO Documentation:
<blockquote>
Use the GetRows method to copy records from a Recordset into a two-dimensional array. The first subscript identifies the field and the second identifies the record number. The array variable is automatically dimensioned to the correct size when the GetRows method returns the data.
</blockquote>
Since it’s a two-dimensional array we need to get the lower and upper bound of the array using: lbound(users,2). Using those values we loop from the first item to the last item.
The current record is the user variable. To get the field we want we must get it according to the index entered in the SQL Query:
SELECT UserId, Username..
UserId: 0
UserName: 1
and so on.
This can be confusing if you do changes to the SQL Query - then you must go through your code and update the array index for the fields! A solution to that is to create costants for each field name:
Dim UserId
Const UserId = 0
Lessons learned:
<ol>
<li>Use ADO GetRows() to get data.</li>
<li>Initiate objects as late as possible and release objects as early as possible.</li>
<li>Use variable for SQL query. Easier for debugging.</li>
<li>Declare all variables at the top of the script/function/class.</li>
</ol>