Inspired? No home

ASP Performance Best Practices #1

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:




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

From the ADO Documentation:

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.

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:

Use ADO GetRows() to get data.

Initiate objects as late as possible and release objects as early as possible.

Use variable for SQL query. Easier for debugging.

Declare all variables at the top of the script/function/class.

ASP Performance Best Practices #1

Related Posts