When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
Print this page.
Published: Thursday, June 21, 2001

Increasing ConvertRStoXML() Performance

By David O'Neill

  • Read Part 1

  • In Part 1 we looked at how the original CovertRStoXML() function behaved. We then looked at how to use the cloneNode() method of the XML DOM. In this part we'll examine using GetRows() and wrap up the article with an examination of the complete code along with a live demo!

    - continued -

    Using Recordset.GetRows() Instead of Iterating Through the Recordset
    Performance tuning discussions everywhere conclude that using the GetRows() method of the ADO Recordset object is a more processing-efficient means of accessing the data within that recordset. The ADO Recordset object is a pretty heavy object; it uses lots of memory and processing for what you get. The benefit is that it is easy to use and works very well. The downside is it is slow to work with large amounts of data. (To learn more about GetRows() be sure to check out this FAQ.)

    In the above code we were iterating through the recordset, accessing each of its fields on each row. It is much more efficient (although more complicated) to use GetRows() to retrieve a multi-dimensional array of our data then we can destroy the recordset. For illustration of what a multi-dimensional array looks like, I ran the following VB code and took a screenshot of a watch on the array variable.

    Dim rs As ADODB.Recordset
    Dim ary As Variant
    Set rs = New ADODB.Recordset
    rs.Open "Select Top 5 EmployeeID, LastName, FirstName" & _
         " From Employees", "Provider=SQLOLEDB.1;" & _
         "User ID=sa;Initial Catalog=Northwind;Data Source=.;", _
         adOpenForwardOnly, adLockReadOnly
    ary = rs.GetRows()
    Set rs = Nothing

    A graphical representation of a two-dimensional array.

    You can see that the first dimension contains the fields and the second dimension contains the data. We access the values almost the same way we would itterate through the rows then fields of a recordset. So, the finished code would be:

    'Instantiate the Microsoft XMLDOM.
    Set objXMLDom = Server.CreateObject("Microsoft.XMLDOM")
    objXMLDom.preserveWhiteSpace = True
    'Create the root element and append it to the XML document.
    Set objXMLRoot = objXMLDom.createElement(strTopLevelNodeName)
    objXMLDom.appendChild objXMLRoot
    'Create the Row and Field elements
    Set objXMLRow = objXMLDom.CreateElement(strRowNodeName)
    Set objXMLField = objXMLDom.createElement("field")
    'Build the Field element including its attribute and child
    Set objXMLFieldName = objXMLDom.createAttribute("name")
    Set objXMLFieldValue = objXMLDom.createElement("value")
    objXMLField.appendChild objXMLFieldValue
    'Build the template row by adding all the field
    'names from the Recordset
    For Each Field in objRS.Fields
      Call objXMLField.SetAttribute("name", Field.Name)
      objXMLRow.appendChild objXMLField
      'Copy the field to give it a new memory address (copy
      'all its children too)
      objXMLField = objXMLField.cloneNode(True)
    'Convert our Recordset to the multi-dimensional array
    'and calculate its bounds
    aryRows = objRS.getRows()
    intNumFields = UBound(aryRows)
    intNumRows = UBound(aryRows, 2)
    'Iterate through the array of data and build the Rows
    For intCurRowIdx = 0 To intNumRows
      'Retrieve all the Field nodes within the Row
      Set objXMLNodeList = objXMLRow.getElementsByTagName("field")
      'Add the data for the fields
      For intCurFieldIdx = 0 To intNumFields
        Set objXMLField = objXMLNodeList.item(intCurFieldIdx)
        Set objXMLFieldValue = objXMLField.FirstChild
        objXMLFieldValue.text = aryRows(intCurFieldIdx, intCurRowIdx)
      'Build the populated Row
      objXMLRoot.appendChild objXMLRow
      'Copy the current row to give it a new memory
      'address (copy children too)
      Set objXMLRow = objXMLRow.CloneNode(True)
    [View the live demo!]

    Well, that's about it. From testing these changes improved performance by over 30% on a large data set. Now, when dealing with fewer records the gain will not be as large since there is a certain amount of overhead associated with opening the data connection and whatnot. All in all, a fairly easy change that should make a sizeable improvement in the end. Be sure to check out the live demo!

    Happy Programming!

  • By David O'Neill


  • Read Creating a Function to Stream a Recordset to XML
  • View the live demo!

    About the Author
    David O'Neill is an MCSD and has been doing Web application development for the past five years.

  • ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article