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

View products that this article applies to.

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.

Back to the top

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.

Back to the top

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.

Back to the top

STATUS

This behavior is by design.

Back to the top

MORE INFORMATION

Steps to reproduce the behavior

Paste the following code into a blank Notepad file and then save the file as c:\nonNested.xsl.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html"/>
<xsl:template match="/OrderData">
	<HTML>
	<BODY>
	<TABLE border="1">
	<xsl:for-each select = "Orders">
		<xsl:variable name="OrderID" select="OrderID"/>
		<TR>
			<TH>Order ID</TH>
			<TH>Customer ID</TH>
		</TR>
		<TR>
			<TD><xsl:value-of select="OrderID"/></TD>
			<TD><xsl:value-of select="CustomerID"/></TD>
		</TR>
		<TR>
			<TH>Product ID</TH>
			<TH>Unit Price</TH>		
			<TH>Quantity</TH>
		</TR>
		<xsl:for-each select = "following-sibling::OrderDetails[OrderID = $OrderID]">
			<TR>
				<TD><xsl:value-of select="ProductID"/></TD>
				<TD><xsl:value-of select="UnitPrice"/></TD>		
				<TD><xsl:value-of select="Quantity"/></TD>
			</TR>
		</xsl:for-each>
	</xsl:for-each>
	</TABLE>
	</BODY>
	</HTML>
</xsl:template>
</xsl:stylesheet>

Paste the following code into a blank Notepad file and then save it as c:\Nested.xsl.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html"/>
<xsl:template match="OrderData">
	<HTML>
	<BODY>
	<TABLE border="1">
	<xsl:for-each select = "Orders">
		<xsl:variable name="OrderID" select="OrderID"/>
		<TR>
			<TH>Order ID</TH>
			<TH>Customer ID</TH>
		</TR>
		<TR>
			<TD><xsl:value-of select="OrderID"/></TD>
			<TD><xsl:value-of select="CustomerID"/></TD>
		</TR>
		<TR>
			<TH>Product ID</TH>
			<TH>Unit Price</TH>		
			<TH>Quantity</TH>
		</TR>
		<xsl:for-each select = "OrderDetails">
			<TR>
				<TD><xsl:value-of select="ProductID"/></TD>
				<TD><xsl:value-of select="UnitPrice"/></TD>		
				<TD><xsl:value-of select="Quantity"/></TD>
			</TR>
		</xsl:for-each>
	</xsl:for-each>
	</TABLE>
	</BODY>
	</HTML>
</xsl:template>
</xsl:stylesheet>

Use either Visual Studio .NET or Visual Basic .NET to create a new Visual Basic .NET console application.

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 _
            ("Data Source=(local);Integrated security=SSPI;Initial catalog=Northwind;")
         'Select data from Orders table
         Dim daOrders As New SqlDataAdapter _
            ("Select OrderID,CustomerID from Orders", cnNwind)
         'Select data from [Order Details] table
         Dim daOrder_Details As New SqlDataAdapter _
            ("Select OrderID,ProductID,UnitPrice,Quantity from [Order Details] where OrderID=10248", cnNwind)

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

         'Create a data relation for the two tables
         Dim dr As New DataRelation("RelOrderDetail", ds.Tables("Orders").Columns("OrderID"), _
            ds.Tables("OrderDetails").Columns("OrderID"))
         '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("c:\nonNested.xsl") ' LINE2
         'xsldoc.Load("c:\Nested.xsl") ' LINE3

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

         'Transform the DataDocument
         startTime = System.DateTime.Now()
         xsldoc.Transform(xmldd, Nothing, strmResult)
         Console.Write("Time taken to execute XSLT = " _
            & 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

Compile and run the project.
Make a note of the value of the number of milliseconds taken for the XSL transformation (this value is written to the Console).
Now uncomment LINE1 and LINE3, and then comment LINE2.
Run the project again. The number of milliseconds taken for XSL transformation is written to the Console.
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.