By David O'Neill
|An Update is Available!|
Author David O'Neill has updated his improved |
This article deals with some performance tuning based upon the undeniably cool
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
After testing, the
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
objRS), our MS XMLDOM object (
objDom), and a root element (
The following code iterates through our recordset and builds the following node structure:
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!)|
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
In Part 2 we'll examine using
GetRows() and look at the
complete code and a live demo.