Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 325693 - Last Review: December 3, 2007 - Revision: 5.2
Slow performance issue occurs when you try to transform an ADO.NET DataSet with non-nested DataRelation objects
This article was previously published under Q325693
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
- 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="http://support.microsoft.com/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.
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
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please