Global.ASA

Global.ASA 

This article discusses what, exactly, Global.asa is, and how you can use it to increase the power of your Active Server Pages Applications What is global.asa? You may have noticed that when you create a new web project with InterDev, a file is inserted into the root directory called global.asa. In this file, there are empty event handlers for events such as Application_OnStart, Application_OnEnd, Session_OnStart, and Session_OnEnd. Global.asa is an optional file where you can define event scripts, and create objects that have either Session or Application scope. There can be at most one global.asa file per web application, and it must exist in the root directory of the web application. As an Active Server Pages programmer, there are only two types of scope you need to know about: Session scope and Application scope. For an object declared in Session scope, each time a new user visits a page in the web application, the event Session_OnStart is fired, and the Global.asa function Session_OnStart is executed. The event Session_OnEnd fires when a set number of minutes have expired from when the user last accessed a page, or when the server calls the Abandon method. The default session timeout is 20 minutes, but can be changed through registry modification, or by setting the Timeout property of the Session object. For example, in the global.asa file’s Application_OnStart, you could write: 'Session will timeout in 5 minutes
Session.Timeout = 5To explicitly end a Session, you just need to call the Abandon method, like so: 'This will clear the session
Session.AbandonSession objects are maintained through the use of cookies, since each Session object is on a per user basis. This is the one of the major downsides of Session objects, that those who browse with cookies disabled do not “save” the Session-scoped objects you use. For example, if you have an ASP page that looks like this: <%
Session("Hello") = "Hello, world!"
Response.Write(Session("Hello"))
%>If the user has cookies enabled, he or she will see the words “Hello, world!” on their screen. Those who do not have cookies enabled will see nothing. Also, since all Session objects are created on a per visitor basis, and since they persist for usually 20 minutes after the last time a particular user visits a page, it is unwise to put any large objects in the Session, such as recordsets, arrays, etc. To learn more, visit this article, which deals with ASP optimization and responsible session-level programming. Application scoped objects are created when the first user hits a web page in the web application, and are destroyed when the server shuts down. When the application is initiated, the Application_OnStart event is fired; when the server shuts down, Application_OnEnd is fired. Global.asa, then, is a place for you to create any session or application level objects. Good things to put in your global.asa are database connection strings, counters, and small session-level variables. You want to stay away from putting large objects in session-level variables, such as
ADO objects. Here is an example of what a Global.asa might look like: <SCRIPT RUNAT=server LANGUAGE="VBScript">

sub Application_OnStart()

‘Creates an application level connection string to database
Application(“ConnectionString”) = “DSN=pubs”
Application(“UserID”) = “sa”
Application(“Password”) = “”

‘Used for counter
Application(“VisitorCount”) = 0
‘So we know as of what date the counter was started
Application(“CounterStart”) = Now

end sub

sub Session_OnStart

‘You need to lock the application for concurrency reasons
Application.Lock
Application(“VisitorCount”) = Application(“VisitorCount”) + 1
Application.UnLock

end sub

</SCRIPT> This global.asa file sets up an application-scoped counter, and increments it each time a new session is created (that is each time a new visitor stops by).

Record set and Cursor

Record set and Cursor 

When you explicitly create a recordset object in
ADO, you can specify what type of cursor you want it to employ. By explicitly creating a recordset, I mean that you issue the following command: 

Set rs = Server.CreateObject(“ADODB.Recordset”)You can always implicitly create a recordset object, and most often developers do just that. For example, if we were to run the following code: Dim rs, conn
Set conn = Server.CreateObject("ADODB.Connection")

'Open the connection using an Application-level
'connection string
conn.Open Application("Connection_String")

Set rs = conn.Execute("SELECT * FROM pubs") After the last line is executed, you have a bona fied recordset object in rs. It was created implicitly by
ADO / ASP, and has a forward-only cursor. When you explicitly create a recordset object, however, you can choose what type of cursor you wish to employ by using the following line of code: rs.CursorType = 'Enter cursor type here The cursor type can be one of four types:

• Forward-only
• Static
• Keyset
• Dynamic

A forward-only cursor is the cursor with the least amount of overhead, thus leading to the best performance. It is also the default cursor for a recordset. It’s only flaw is that it is uni-directional. You can only use commands which iterate through the recordset from front to end, such as MoveNext. You cannot use MovePrev, for example. If you try to issue a MovePrev command on a forward-only recordset you will get an error explaining that you cannot perform such an operation on a firehose cursor. (It is called a firehose cursor because like a hose, the stuff (records, in a recordset, water in a hose) is only going one way.) To use a forward-only cursor, you could type: rs.CursorType = adOpenForwardOnly Note: This article assumes you include adovbs.inc on each of your ASP pages. If you need the file it can be downloaded by clicking on the hyperlink. The next type of cursor is a static cursor. This allows you to move in any direction, but it doesn’t reflect any updates, deletions, or additions to the recordset while the user is viewing the recordset. It is as if a snapshot was taken of the data at the time it was requested. This recordset let’s you use the full plethora of recordset traversal commands, such as MoveFirst, MovePrev, MoveLast, RecordCount, and others. You can use a static cursor by typing: rs.CursorType = adOpenStatic Dynamic and Keyset cursors allow you to see any changes which are made. While this is nice in a VisualBASIC application, it really doesn’t apply to the web, since a request is made, and, like the static cursor, a snapshot of the information is brought back. There is no way to continuously update the data for the client without having the client refresh his screen (unless you delve into some of IE 4.x’s new data features). Since this is the case for the web, you should never use Keyset or Dynamic cursors, since they hog up more resources than Static or forward-only. If you need to traverse through your recordset or need a count of your recordset, use a static cursor, else use a forward-only cursor

Using a forward-only cursor when only a forward iteration through a recordset is needed will save system resources and decrease query time. For that reason, be sure to choose the right cursor when you create your recordsets!

Record set and Cursor

Record set and Cursor 

When you explicitly create a recordset object in
ADO, you can specify what type of cursor you want it to employ. By explicitly creating a recordset, I mean that you issue the following command:Set rs = Server.CreateObject(“ADODB.Recordset”)You can always implicitly create a recordset object, and most often developers do just that. For example, if we were to run the following code: Dim rs, conn
Set conn = Server.CreateObject("ADODB.Connection")

'Open the connection using an Application-level
'connection string
conn.Open Application("Connection_String")

Set rs = conn.Execute("SELECT * FROM pubs") After the last line is executed, you have a bona fied recordset object in rs. It was created implicitly by
ADO / ASP, and has a forward-only cursor. When you explicitly create a recordset object, however, you can choose what type of cursor you wish to employ by using the following line of code: rs.CursorType = 'Enter cursor type here The cursor type can be one of four types:

• Forward-only
• Static
• Keyset
• Dynamic

A forward-only cursor is the cursor with the least amount of overhead, thus leading to the best performance. It is also the default cursor for a recordset. It’s only flaw is that it is uni-directional. You can only use commands which iterate through the recordset from front to end, such as MoveNext. You cannot use MovePrev, for example. If you try to issue a MovePrev command on a forward-only recordset you will get an error explaining that you cannot perform such an operation on a firehose cursor. (It is called a firehose cursor because like a hose, the stuff (records, in a recordset, water in a hose) is only going one way.) To use a forward-only cursor, you could type: rs.CursorType = adOpenForwardOnly Note: This article assumes you include adovbs.inc on each of your ASP pages. If you need the file it can be downloaded by clicking on the hyperlink. The next type of cursor is a static cursor. This allows you to move in any direction, but it doesn’t reflect any updates, deletions, or additions to the recordset while the user is viewing the recordset. It is as if a snapshot was taken of the data at the time it was requested. This recordset let’s you use the full plethora of recordset traversal commands, such as MoveFirst, MovePrev, MoveLast, RecordCount, and others. You can use a static cursor by typing: rs.CursorType = adOpenStatic Dynamic and Keyset cursors allow you to see any changes which are made. While this is nice in a VisualBASIC application, it really doesn’t apply to the web, since a request is made, and, like the static cursor, a snapshot of the information is brought back. There is no way to continuously update the data for the client without having the client refresh his screen (unless you delve into some of IE 4.x’s new data features). Since this is the case for the web, you should never use Keyset or Dynamic cursors, since they hog up more resources than Static or forward-only. If you need to traverse through your recordset or need a count of your recordset, use a static cursor, else use a forward-only cursor

Using a forward-only cursor when only a forward iteration through a recordset is needed will save system resources and decrease query time. For that reason, be sure to choose the right cursor when you create your recordsets!

ComboBox/ListBox Tuning

ComboBox/ListBox Tuning

Improve the performance of loading a ComboBox/ListBox using ASP and SQL Server

To improve the performance of loading a listbox or combobox using ASP to a SQL Server Database, try shifting the processing to the SQL Server server and use some of the new tricks in ADO 2.5 and above (Version 2.5 ships with Windows 2000 and can be downloaded from Microsoft’s Download area)

Normally to populate a Combo/List box the following similiar code is used:

 <%

 Set cn = CreateObject("ADODB.Connection")
 Set rs = CreateObject("ADODB.Recordset")

 
 ' cn_string is an Application Level variable for storing the Connection         
 ' String to the db.
 
 cn.Open Application(cn_string) 

 ' If the connection is successful
 If cn.State > 0                         
     strSQL = "SELECT ID, NAME FROM tblEmployees ORDER BY NAME"
     Set rs = cn.Execute(strSQL)
     If rs.State > 0' The recordset opened
         Response.Write "<SELECT ID=cbo name=cbo>"
         Do While Not rs.EOF
             Response.Write "<OPTION ID=" & rs("ID") & ">" & _
               rs("Name") & "</OPTION>"
             rs.MoveNext
         Loop
         Response.Write "</SELECT>"
         rs.Close
     End If       
     Set rs = Nothing
     cn.Close
 End If
 Set cn = Nothing
 %>

This method above places the entire processing onto the IIS Server and requires several round trips to the server to populate the combo/list box.

To improve the performance of the list/combo box the following SQL and
ADO methods can be used:

 <%

 Set cn = CreateObject("ADODB.Connection")
 Set rs = CreateObject("ADODB.Recordset")
 cn.Open Application(cn_string) 
 If cn.State > 0          ' The connection is successful
     strSQL = "SELECT '<OPTION=' + Cast(ID as Varchar(10)) + '>' + NAME + '</OPTION>'"
     strSQL = strSQL & " FROM tblEmployees ORDER BY NAME"
     Set rs = cn.Execute(strSQL)
     If rs.State > 0       ' The recordset opened 
         Response.Write "<SELECT ID=cbo name=cbo>"
         If Not rs.EOF and Not rs.EOF Then
             Response.Write rs.GetString(2, -1)
         End If
         Response.Write "</SELECT>"
         rs.Close
     End If       
     Set rs = Nothing
     cn.Close
 End If
 Set cn = Nothing
 %>

The first thing we did was format the SQL string so that only a single column is being returned to the recordset, then we used the ADO method “GetString” to basically concatenate the entire result set being returned into one long string. This in effect writes out all OPTIONS for the combo/list box in one call to the database vice on each move next. Performance is greatly improved.

One project I used this on to populate a 4000+ item listbox would load in 45 seconds using the first method above and in 3 seconds using the recommended method above. A 1500% INCREASE IN PERFORMANCE!

Note: To use this method with SQL Server, you are building this as a string and thus all numeric fields contained with the string you are creating must be converted to strings. In SQL Server the CAST method is the easiest way to do this.

OK, now I hear the cynics out there saying, “This is great, except I need to set an item as default which may or may not be the first item in the combo/listbox”. This is where step two of shifting processing back to SQL Server (or any Database server) comes in.

 <%

 ' This is the ID Of the item that is to be the default selected item
 lngDefaultID = 123 

 Set cn = CreateObject("ADODB.Connection")
 Set rs = CreateObject("ADODB.Recordset")

 cn.Open Application(cn_string) 

 If cn.State > 0          ' The connection is successful

     strSQL = "SELECT '<OPTION ' +"
     strSQL = strSQL & "Case "
     strSQL = strSQL & "   WHEN ID = " & lngDefaultID & " THEN 'selected' "  
     strSQL = strSQL & "   ELSE '' "
     strSQL = strSQL & "END + ' value=' + CAST(ID AS VARCHAR(10)) + '>' + "
     strSQL = strSQL & "NAME +'</OPTION>' FROM tblEmployees ORDER BY NAME"
     Set rs = cn.Execute(strSQL)
     If rs.State > 0       ' The recordset opened
         Response.Write "<SELECT ID=cbo name=cbo>"
         If Not rs.EOF and Not rs.EOF Then
             Response.Write rs.GetString(2, -1)
         End If
         Response.Write "</SELECT>"
         rs.Close
     End If       
     Set rs = Nothing
     cn.Close
 End If
 Set cn = Nothing
 %>

As you can see above, we have used the T-SQL command “CASE” which tests for ID being equal to the value that is inserted into the string. When the ID is equal to the defaultID then it adds the word ’selected’ into that single OPTION line and all the rest are standard options. In Oracle the same thing can be accomplished with a DECODE command.