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

Article translations Article translations
Article ID: 325693 - View products that this article applies to.
This article was previously published under Q325693
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

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.

MORE INFORMATION

Steps to reproduce the behavior

  1. 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>
    					
  2. 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>
    					
  3. Use either Visual Studio .NET or Visual Basic .NET to create a new Visual Basic .NET console application.
  4. 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
    					
  5. Compile and run the project.
  6. Make a note of the value of the number of milliseconds taken for the XSL transformation (this value is written to the Console).
  7. Now uncomment LINE1 and LINE3, and then comment LINE2.
  8. Run the project again. The number of milliseconds taken for XSL transformation is written to the Console.
  9. 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.

Properties

Article ID: 325693 - Last Review: February 27, 2014 - Revision: 5.3
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
Keywords: 
kbnosurvey kbarchive kbtshoot kbprb KB325693

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com