Published: Thursday, June 21, 2001
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!
