Increasing ConvertRStoXML() Performance By David O'Neill An Update is Available! Author David O'Neill has updated his improved ConvertRStoHTML() function to handle NULLs, to provide better performance, to provide more readable XML output, and to allow for conversion from XML to HTML. To learn more about this updated version be sure to read: Simplifying ConvertRStoXML() and Transforming With XSL. ANOTHER UPDATE! Carlos Baptiste has written an article titled Revisiting Converting a Recordset to XML that looks at using the MSXML DOMDocument object and XSL stylesheets to transform the Recordset's native XML output into a human-readable XML output with just a few lines of code. Introduction This article deals with some performance tuning based upon the undeniably cool ConvertRStoXML() function developed and described by Scott Mitchell in Creating a Function to Stream a Recordset to XML. This article will not cover the basics of XML and the principle and reasoning behind having such a function as these topics are covered nicely in the first article, so read it first! Upon first glance the original ConvertRStoXML() function is cool since it handily converts a recordset to XML. Second and third glances are much like the first glance since the function really is cool. But wait! This function has all the functionality we need but look at all those COM calls… Yikes! COM calls and object instantiations, although inherently neat and useful, are resource heavy, so lets use the XMLNode.CloneNode() method to copy our objects instead of creating new ones again and again. Also, lets see if we can use the GetRows() method of the ADO Recordset object since arrays are less resource intensive than Recordset objects. (For more information on the CloneNode() method check out the technical docs; for more information on GetRows(), check out this FAQ.) After testing, the XMLNode.CloneNode() and Recordset.GetRows() approach improved performance by about 33%. Tests were based on a bloated 20-column, 50000-row ADO Recordset to give a good sample. The Original Approach Consider the following code (from the original ConvertRStoXML() function). Assume that we have an ADO recordset (objRS), our MS XMLDOM object (objDom), and a root element (objRoot). The following code iterates through our recordset and builds the following node structure: Root -Row0 -Field0 (FieldName) -FieldValue = Value -Field… (FieldName) -FieldValue = Value -Row… -Field0 (FieldName) -FieldValue = Value -Field… (FieldName) -FieldValue = Value Do While Not objRS.EOF Set objRow = objDom.CreateElement(strRowNodeName) For Each objRSField in objRS.Fields Set objField = objDom.createElement("field") Set objcolName = objDom.createAttribute("name") objcolName.Text = objRSField.Name objField.SetAttributeNode(objColName) Set objFieldValue = objDom.createElement("value") objFieldValue.Text = objRSField.Value objField.appendChild objFieldValue objRow.appendChild objField Next objRoot.appendChild objRow objRS.MoveNext Loop Keeping with my 20-column 5000-row recordset, the objects instantiated for each row would be: 20 Field Nodes + 20 Field Name Attributes + 20 Field Value Nodes = 60 instantiations 60 calls x 5000 rows = a whole lotta object instantiations (COM calls) Considering that ASP uses late-binding (unlike compiled languages such as VB & C++) that's a lot for the system to be doing. A Quick Note on Late Binding VBScript is both uncompiled and uses late binding. Late binding (vs. Early binding) is when you declare a variable in ASP the VBScript engine cannot determine what sort of object reference the variable will contain, since all variables are of type Variant and can store any data type. As a result the code runs slower than code that would use an early-bound object variable in languages such as VB or C++, because the VBScript engine must determine at run time what this object is and how to use it. (For more information on how VBScript handles variables, be sure to read Bill Wilksinson's excellent article series, Variables and Values in VBScript!) Using XMLNode.CloneNode() to Reduce Object Instantiations The MS XMLDOM Node object exposes a method called CloneNode() which essentially makes a copy of the node and all its child nodes in new memory space. After the first row we know how many fields we're dealing with so therefore each Row Node will be exactly the same. It is important to note that we must clone the nodes instead of reusing the same node objects since when we go to append the fields to the rows and the rows to the root each field or row must have its own memory address. The XMLNode.AppendChild() method only appends the memory address of the child and not the child itself, therefore we must have a new memory address for each child. If you don't clone you'll end up with a tree full of nodes containing the most recent data you added! I will be coming back to a cloning example later once I tie in the next piece: using Recordset.GetRows(). In Part 2 we'll examine using GetRows() and look at the complete code and a live demo. Read Part 2!

Article Information
Article Title:	Increasing ConvertRStoXML() Performance
Article Author:	David O'Neill
Published Date:	Thursday, June 21, 2001
Article URL:	http://www.4GuysFromRolla.com/webtech/062101-1.shtml