Microsoft KB Archive/325693

= Slow performance issue occurs when you try to transform an ADO.NET DataSet with non-nested DataRelation objects =

Article ID: 325693

Article Last Modified on 12/3/2007

-

APPLIES TO


 * Microsoft .NET Framework Class Libraries 1.0
 * Microsoft .NET Framework Class Libraries 1.1
 * Microsoft .NET Framework 2.0
 * Microsoft ADO.NET 1.0
 * Microsoft ADO.NET 1.1
 * Microsoft ADO.NET 2.0

-



This article was previously published under Q325693



SYMPTOMS
Performance of your computer system slows when you try to transform the XML representation of a DataSet that has multiple related DataTable objects whose DataRelation objects are not nested to reflect a hierarchical structure.



CAUSE
Many XSL Transformation (XSLT) performance problems are caused by non-optimized code in the XSLT stylesheet. The non-optimized code might also be a result of poorly structured data in the source XML instance. In this case, the source XML is poorly structured.



RESOLUTION
To resolve this issue:
 * Set the Nested property of DataRelation to True.
 * Write code in the XSLT stylesheet that uses natural top down hierarchical XPath query expressions to locate and transform the data.



STATUS
This behavior is by design.



Steps to reproduce the behavior
  Paste the following code into a blank Notepad file and then save the file as c:\nonNested.xsl.         </xsl:template> </xsl:stylesheet> </li> <li> Paste the following code into a blank Notepad file and then save it as c:\Nested.xsl. <?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?> <xsl:stylesheet version=&quot;1.0&quot; xmlns:xsl=&quot;http://www.w3.org/1999/XSL/Transform&quot;> <xsl:output method=&quot;html&quot;/> <xsl:template match=&quot;OrderData&quot;>   </BODY> </HTML> </xsl:template> </xsl:stylesheet> </li> <li>Use either Visual Studio .NET or Visual Basic .NET to create a new Visual Basic .NET console application.</li> <li> Replace the code in Module1.vb with the code that follows.

To use this sample, you must have installed the sample Northwind database on the local SQL Server(or MSDE). Modify the connection string to suit your environment. Imports System.Data Imports System.Data.SqlClient Imports System.Xml Imports System.Xml.Xsl Imports System.IO

Module Module1

Sub Main

Try 'Connect to Northwind database; Modify the connection string to suit your environment Dim cnNwind As New SqlConnection _ (&quot;Data Source=(local);Integrated security=SSPI;Initial catalog=Northwind;&quot;) 'Select data from Orders table Dim daOrders As New SqlDataAdapter _ (&quot;Select OrderID,CustomerID from Orders&quot;, cnNwind) 'Select data from [Order Details] table Dim daOrder_Details As New SqlDataAdapter _ (&quot;Select OrderID,ProductID,UnitPrice,Quantity from [Order Details] where OrderID=10248&quot;, cnNwind)

'Fill the DataSet with Orders and [Order Details] data Dim ds As New DataSet(&quot;OrderData&quot;) daOrders.Fill(ds, &quot;Orders&quot;) daOrder_Details.Fill(ds, &quot;OrderDetails&quot;)

'Create a data relation for the two tables Dim dr As New DataRelation(&quot;RelOrderDetail&quot;, ds.Tables(&quot;Orders&quot;).Columns(&quot;OrderID&quot;), _           ds.Tables(&quot;OrderDetails&quot;).Columns(&quot;OrderID&quot;)) 'dr.Nested = True ' LINE1 ds.Relations.Add(dr)

'Synchronize the DataSet with XMLDataDocument Dim xmldd As New XmlDataDocument(ds)

'XslTransform objects to transform the DataSet Dim xsldoc As New XslTransform xsldoc.Load(&quot;c:\nonNested.xsl&quot;) ' LINE2 'xsldoc.Load(&quot;c:\Nested.xsl&quot;) ' LINE3

'Create a StreamWriter to write the xsl output Dim strmResult As New StreamWriter(&quot;result.xml&quot;) Dim startTime As DateTime

'Transform the DataDocument startTime = System.DateTime.Now xsldoc.Transform(xmldd, Nothing, strmResult) Console.Write(&quot;Time taken to execute XSLT = &quot; _           & Str(System.DateTime.Now.Subtract(startTime).TotalMilliseconds)) strmResult.Close

Catch sqlex As SqlException Console.WriteLine(sqlex.Message) Catch xmlex As XmlException Console.WriteLine(xmlex.Message) Catch ex As Exception Console.WriteLine(ex.Message) Finally Console.Read End Try

End Sub

End Module </li> <li>Compile and run the project.</li> <li>Make a note of the value of the number of milliseconds taken for the XSL transformation (this value is written to the Console).</li> <li>Now uncomment LINE1 and LINE3, and then comment LINE2.</li> <li>Run the project again. The number of milliseconds taken for XSL transformation is written to the Console.</li> <li>Compare the milliseconds that were taken for the transformations in steps 6 and 8.

In addition to taking less time, the XSL is small and simple for nested relations.</li></ol>

Keywords: kbtshoot kbprb KB325693

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.