Warning: Cannot modify header information - headers already sent by (output started at /home/dealkhus/cosmoread.com/wp-includes/formatting.php:5100) in /home/dealkhus/cosmoread.com/wp-content/themes/cosmoread/header.php on line 18

Warning: Cannot modify header information - headers already sent by (output started at /home/dealkhus/cosmoread.com/wp-includes/formatting.php:5100) in /home/dealkhus/cosmoread.com/wp-content/themes/cosmoread/header.php on line 19

Warning: Cannot modify header information - headers already sent by (output started at /home/dealkhus/cosmoread.com/wp-includes/formatting.php:5100) in /home/dealkhus/cosmoread.com/wp-content/themes/cosmoread/header.php on line 20

Warning: Cannot modify header information - headers already sent by (output started at /home/dealkhus/cosmoread.com/wp-includes/formatting.php:5100) in /home/dealkhus/cosmoread.com/wp-content/themes/cosmoread/header.php on line 21
Home » Education » ComboBox/ListBox Tuning

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.

Check Also

Trouble in Paradise: How Biotech is Fighting Tropical Diseases

Lommodo ligula eget dolor. Aenean massa. Cum sociis que penatibus et magnis dis parturient montes …