Increasing ConvertRStoXML() Performance

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!

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() rs.Close Set rs = Nothing

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.SetAttributeNode(objXMLFieldName) 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) Next '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) Next '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) Next

[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!