Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Performance problems with XQuery

16 views
Skip to first unread message

CD

unread,
Apr 25, 2008, 3:17:51 AM4/25/08
to
Hello All,

I need some help improving the performance of queries that mix TSQL
with XQuery.

Some of our tables are defined like:

create table A (
id uniqueidentifier primary key,
Name nvarchar(260),
Type nvarchar(260),
Description nvarchar(2000),
Data xml NULL,
Created datetime,
Modified datetime
)

The Data xml column is contain a list of key-value pairs. For
example,

<Top>
<Props>
<Prop>
<N> Prop1 </N>
<V> Value1 </V>
<Prop>
<Prop>
<N> Prop2 </N>
<V> Value2 </V>
<Prop>
<Prop>
<N> Prop3 </N>
<V> Value3 </V>
<Prop>
</Props>
</Top>

I need to perform queries such as

WITH XMLNAMESPACES(DEFAULT 'http://some.namespace.com/')
SELECT
A.id,
A.Name,
A.Type,
A.Data.value( 'string((/Top/Props/Prop[N="Prop1"][1]/V)[1])',
'nvarchar(260)') As P1,
A.Data.value( 'string((/Top/Props/Prop[N="Prop2"][1]/V)[1])',
'nvarchar(260)') As P2,
A.Data.value( 'string((/Top/Props/Prop[N="Prop2"][1]/V)[1])',
'nvarchar(260)') As P3,
FROM A
WHERE A.Data.value('string((/Top/Props/Prop[N="Prop1"][1]/V)[1])',
'nvarchar(260)') like '%ABC%'

but I am experiencing long response times as the number of elements on
the table A increases and the number of <Props> elements in the xml
contained in A.Data increases. I have added all xml indices to the
table (primary) and secondary (path, value, property) but still the
performance is not what we would like.

I would like to know from you what I can realistically expect in terms
of performance and what I can do to achieve the best performance in
this scenario.

Your help is greatly appreciated.

Regards
CD

dave ballantyne

unread,
Apr 25, 2008, 6:20:06 AM4/25/08
to
Ive never worked with XQuery / XML indexes but im guessing that this
like statement is the route of your problem

like '%ABC%'

This would cause a table scan in a normal query , so i see no reason as
to why it shouldnt in and XQuery.

Maybe you need full text indexes ?
I have no idea if they work within XML though

http://www.developer.com/db/article.php/3446891

Dave

ML

unread,
Apr 25, 2008, 8:21:06 AM4/25/08
to
As Dave already mentioned the problem may be the table scan needed to resolve
the restriction using the LIKE operator.

I've written a few articles on XML in SQL Server on my blog:
http://milambda.blogspot.com/search?q=xml

You can start here:
http://milambda.blogspot.com/2006/11/sql-server-2005-xml-methods-part-one.html
...or go straight to the exist() XML method:
http://milambda.blogspot.com/2007/07/sql-server-2005-xml-methods-part-four.html

Note, that XML is case-sensitive and so are all the XPath functions.

You might benefit from full-text indexing the XML column, as Dave also
suggested.


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

CD

unread,
Apr 26, 2008, 1:37:47 AM4/26/08
to
Thank you for the information and links.

ML, thank you for the wealth of information in your blogs. Based on
your article "SQL Server 2005 XML Methods, Part Four, The Existential
Quandary", I changed the where clause of the query above to use the
exists() function (Yes, I am aware that the comparison is case
sensitive). I also made sure that all the xml indices in the columns
are in place (primary and secondary).

To my surprise, however, the performance was as bad as before. I
analyzed the query plan and noticed that the properties index being
used, instead of the value index, which was the one I was expecting to
be used based on your example in the article.

I then reproduced the experiment of your article in the AdventureWorks
database and ran the query Q1, making sure that all indices for the
xml columns had bees created. Curiously, the query plans were very
different, and again the secondary index for value was not used.
Instead, the one for property was used.

Just for your reference, the query that I ran was

select ANV.AttributeId --
with(index(pxx_AttributesNameValue_Attribute)) --
with(,index(vxx_AttributesNameValue_Attribute))
from dbo.AttributesNameValue ANV
where (ANV.Attribute.exist(
'/attributeCollection/attribute[data(name) = "middleName"]
/value[data(.) = sql:variable("@middleName")]'
) = 1)

The query plan is shown below

Do you have any insight on what I might be missing?

Kind regards
CD

=== QUERY PLAN START
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/
showplan" Version="1.0" Build="9.00.3042.00">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="declare @elementName
sysname&#xD;&#xA;declare @middleName varchar(8)&#xD;&#xA;&#xD;&#xA;set
@elementName = 'middleName'&#xD;" StatementId="1" StatementCompId="1"
StatementType="ASSIGN" />
<StmtSimple StatementText="&#xA;set @middleName =
'Z.'&#xD;&#xA;&#xD;&#xA;&#xD;&#xA;&#xD;" StatementId="2"
StatementCompId="2" StatementType="ASSIGN" />
<StmtSimple StatementText="&#xA;select ANV.AttributeId --
with(index(pxx_AttributesNameValue_Attribute)) --
with(,index(vxx_AttributesNameValue_Attribute))&#xD;&#xA; from
dbo.AttributesNameValue ANV&#xD;&#xA; where
(ANV.Attribute.exist(&#xD;&#xA; '/attributeCollection/
attribute[data(name) = &quot;middleName&quot;]&#xD;&#xA; /
value[data(.) = sql:variable(&quot;@middleName&quot;)]'&#xD;&#xA; )
= 1)&#xD;&#xA;" StatementId="3" StatementCompId="3"
StatementType="SELECT" StatementSubTreeCost="46.096"
StatementEstRows="17974.8" StatementOptmLevel="FULL">
<StatementSetOptions QUOTED_IDENTIFIER="false"
ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false"
ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" /
>
<QueryPlan CachedPlanSize="66" CompileTime="138"
CompileCPU="138" CompileMemory="968">
<RelOp NodeId="0" PhysicalOp="Parallelism"
LogicalOp="Gather Streams" EstimateRows="17974.8" EstimateIO="0"
EstimateCPU="0.0453379" AvgRowSize="11"
EstimatedTotalSubtreeCost="46.096" Parallel="1" EstimateRebinds="0"
EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]" Table="[AttributesNameValue]" Alias="[ANV]"
Column="AttributeId" />
</OutputList>
<Parallelism>
<RelOp NodeId="1" PhysicalOp="Nested Loops"
LogicalOp="Left Semi Join" EstimateRows="17974.8" EstimateIO="0"
EstimateCPU="0.0417415" AvgRowSize="11"
EstimatedTotalSubtreeCost="46.0507" Parallel="1" EstimateRebinds="0"
EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]" Table="[AttributesNameValue]" Alias="[ANV]"
Column="AttributeId" />
</OutputList>
<NestedLoops Optimized="0">
<OuterReferences>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]" Table="[AttributesNameValue]" Alias="[ANV]"
Column="AttributeId" />
</OuterReferences>
<RelOp NodeId="2" PhysicalOp="Clustered Index
Scan" LogicalOp="Clustered Index Scan" EstimateRows="19972"
EstimateIO="1.25794" EstimateCPU="0.0110631" AvgRowSize="11"
EstimatedTotalSubtreeCost="1.269" Parallel="1" EstimateRebinds="0"
EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]" Table="[AttributesNameValue]" Alias="[ANV]"
Column="AttributeId" />
</OutputList>
<IndexScan Ordered="0" ForcedIndex="0"
NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference
Database="[AdventureWorks]" Schema="[dbo]"
Table="[AttributesNameValue]" Alias="[ANV]" Column="AttributeId" />
</DefinedValue>
</DefinedValues>
<Object Database="[AdventureWorks]"
Schema="[dbo]" Table="[AttributesNameValue]"
Index="[pk_AttributesNameValue_AttributeId]" Alias="[ANV]" />
</IndexScan>
</RelOp>
<RelOp NodeId="3" PhysicalOp="Nested Loops"
LogicalOp="Left Semi Join" EstimateRows="1" EstimateIO="0"
EstimateCPU="4.18e-006" AvgRowSize="9"
EstimatedTotalSubtreeCost="44.7399" Parallel="1"
EstimateRebinds="19971" EstimateRewinds="0">
<OutputList />
<NestedLoops Optimized="0">
<OuterReferences>
<ColumnReference Database="[AdventureWorks]"
Schema="[sys]" Table="[xml_index_nodes_2091154495_32000]"
Alias="[attribute:1]" Column="id" />
</OuterReferences>
<RelOp NodeId="4" PhysicalOp="Nested Loops"
LogicalOp="Left Semi Join" EstimateRows="1" EstimateIO="0"
EstimateCPU="4.18e-006" AvgRowSize="14"
EstimatedTotalSubtreeCost="31.7678" Parallel="1"
EstimateRebinds="19971" EstimateRewinds="0">
<OutputList>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[attribute:1]"
Column="id" />
</OutputList>
<NestedLoops Optimized="0">
<OuterReferences>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[value:4]"
Column="id" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[value:4]"
Column="pk1" />
</OuterReferences>
<RelOp NodeId="6" PhysicalOp="Nested
Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0"
EstimateCPU="9.31299e-006" AvgRowSize="23"
EstimatedTotalSubtreeCost="24.5263" Parallel="1"
EstimateRebinds="19971" EstimateRewinds="0">
<OutputList>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[attribute:1]"
Column="id" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[value:4]"
Column="id" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[value:4]"
Column="pk1" />
</OutputList>
<NestedLoops Optimized="1">
<OuterReferences>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[value:4]"
Column="id" />
<ColumnReference Column="Expr1057" /
>
</OuterReferences>
<RelOp NodeId="8" PhysicalOp="Compute
Scalar" LogicalOp="Compute Scalar" EstimateRows="2.22799"
EstimateIO="0" EstimateCPU="2.22799e-007" AvgRowSize="470"
EstimatedTotalSubtreeCost="8.54208" Parallel="1"
EstimateRebinds="19971" EstimateRewinds="0">
<OutputList>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[value:4]"
Column="id" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[value:4]"
Column="pk1" />
<ColumnReference
Column="Expr1057" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference
Column="Expr1057" />
<ScalarOperator
ScalarString="getancestor([AdventureWorks].[sys].
[xml_index_nodes_2091154495_32000].[id] as [value:4].[id],(1))">
<Intrinsic
FunctionName="getancestor">
<ScalarOperator>
<Identifier>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[value:4]"
Column="id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" /
>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp NodeId="9"
PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="2.22799"
EstimateIO="0.003125" EstimateCPU="0.000159451" AvgRowSize="18"
EstimatedTotalSubtreeCost="8.53763" Parallel="1"
EstimateRebinds="19971" EstimateRewinds="0">
<OutputList>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[value:4]"
Column="id" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[value:4]"
Column="pk1" />
</OutputList>
<IndexScan Ordered="1"
ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[value:4]"
Column="id" />
</DefinedValue>
<DefinedValue>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[value:4]"
Column="pk1" />
</DefinedValue>
</DefinedValues>
<Object
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]"
Index="[rxx_AttributesNameValue_Attribute]" Alias="[value:4]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[value:4]"
Column="pk1" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[value:4]"
Column="hid" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator
ScalarString="[AdventureWorks].[dbo].[AttributesNameValue].
[AttributeId] as [ANV].[AttributeId]">
<Identifier>
<ColumnReference
Database="[AdventureWorks]" Schema="[dbo]"
Table="[AttributesNameValue]" Alias="[ANV]" Column="AttributeId" />
</Identifier>
</ScalarOperator>
<ScalarOperator
ScalarString="'Ā€'">
<Const ConstValue="'Ä
€Â€À€'" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp NodeId="13"
PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek"
EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581"
AvgRowSize="21" EstimatedTotalSubtreeCost="15.7413" Parallel="1"
EstimateRebinds="44496.4" EstimateRewinds="0">
<OutputList>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[attribute:1]"
Column="id" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[attribute:1]"
Column="hid" />
</OutputList>
<IndexScan Ordered="1"
ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[attribute:1]"
Column="id" />
</DefinedValue>
<DefinedValue>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[attribute:1]"
Column="hid" />
</DefinedValue>
</DefinedValues>
<Object
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]"
Index="[pxx_AttributesNameValue_Attribute]" Alias="[attribute:1]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[attribute:1]"
Column="pk1" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[attribute:1]"
Column="id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator
ScalarString="[AdventureWorks].[dbo].[AttributesNameValue].
[AttributeId] as [ANV].[AttributeId]">
<Identifier>
<ColumnReference
Database="[AdventureWorks]" Schema="[dbo]"
Table="[AttributesNameValue]" Alias="[ANV]" Column="AttributeId" />
</Identifier>
</ScalarOperator>
<ScalarOperator
ScalarString="[Expr1057]">
<Identifier>
<ColumnReference
Column="Expr1057" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
<Predicate>
<ScalarOperator
ScalarString="[AdventureWorks].[sys].
[xml_index_nodes_2091154495_32000].[id] as [attribute:1].
[id]&lt;=[AdventureWorks].[sys].[xml_index_nodes_2091154495_32000].
[id] as [value:4].[id] AND [AdventureWorks].[sys].
[xml_index_nodes_2091154495_32000].[hid] as [attribute:1].[hid]='€'
AND getdescendantlimit([AdventureWorks].[sys].
[xml_index_nodes_2091154495_32000].[id] as [attribute:1].[id])&gt;
[AdventureWorks].[sys].[xml_index_nodes_2091154495_32000].[id] as
[value:4].[id]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="LE">
<ScalarOperator>
<Identifier>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[attribute:1]"
Column="id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[value:4]"
Column="id" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[attribute:1]"
Column="hid" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'€€'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Intrinsic
FunctionName="getdescendantlimit">
<ScalarOperator>
<Identifier>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[attribute:1]"
Column="id" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[value:4]"
Column="id" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp NodeId="15" PhysicalOp="Filter"
LogicalOp="Filter" EstimateRows="1" EstimateIO="0"
EstimateCPU="1.78416e-006" AvgRowSize="9"
EstimatedTotalSubtreeCost="7.15602" Parallel="1"
EstimateRebinds="19971" EstimateRewinds="0">
<OutputList />
<Filter StartupExpression="0">
<RelOp NodeId="16" PhysicalOp="UDX"
LogicalOp="UDX" EstimateRows="1.512" EstimateIO="0"
EstimateCPU="1.512e-006" AvgRowSize="4019"
EstimatedTotalSubtreeCost="7.12039" Parallel="1"
EstimateRebinds="19971" EstimateRewinds="0">
<OutputList>
<ColumnReference
Column="Expr1049" />
</OutputList>
<MemoryFractions Input="0"
Output="0" />
<Extension UDXName="XQUERY DATA">
<DefinedValues>
<DefinedValue>
<ColumnReference
Column="Expr1047" />
</DefinedValue>
<DefinedValue>
<ColumnReference
Column="Expr1048" />
</DefinedValue>
<DefinedValue>
<ColumnReference
Column="Expr1049" />
</DefinedValue>
<DefinedValue>
<ColumnReference
Column="Expr1050" />
</DefinedValue>
<DefinedValue>
<ColumnReference
Column="Expr1051" />
</DefinedValue>
</DefinedValues>
<UsedUDXColumns>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="id" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="nid" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="tid" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="value" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="lvalue" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="lvaluebin" />
<ColumnReference
Column="Expr1041" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[value:4]"
Column="pk1" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="id" />
</UsedUDXColumns>
<RelOp NodeId="17"
PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar"
EstimateRows="1.89" EstimateIO="0" EstimateCPU="1.89e-007"
AvgRowSize="12512" EstimatedTotalSubtreeCost="7.09019" Parallel="1"
EstimateRebinds="19971" EstimateRewinds="0">
<OutputList>
<ColumnReference
Column="Expr1041" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="id" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="nid" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="tid" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="value" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="lvalue" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="lvaluebin" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference
Column="Expr1041" />
<ScalarOperator
ScalarString="0x58">
<Const
ConstValue="0x58" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp NodeId="18"
PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek"
EstimateRows="1.89" EstimateIO="0.003125" EstimateCPU="0.000159079"
AvgRowSize="12060" EstimatedTotalSubtreeCost="7.08641" Parallel="1"
EstimateRebinds="19971" EstimateRewinds="0">
<OutputList>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="id" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="nid" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="tid" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="value" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="lvalue" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="lvaluebin" />
</OutputList>
<IndexScan Ordered="1"
ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="id" />
</DefinedValue>
<DefinedValue>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="nid" />
</DefinedValue>
<DefinedValue>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="tid" />
</DefinedValue>
<DefinedValue>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="value" />
</DefinedValue>
<DefinedValue>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="lvalue" />
</DefinedValue>
<DefinedValue>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="lvaluebin" />
</DefinedValue>
</DefinedValues>
<Object
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]"
Index="[pxx_AttributesNameValue_Attribute]" Alias="[Desc:5]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="pk1" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator
ScalarString="[AdventureWorks].[dbo].[AttributesNameValue].
[AttributeId] as [ANV].[AttributeId]">
<Identifier>

<ColumnReference Database="[AdventureWorks]" Schema="[dbo]"
Table="[AttributesNameValue]" Alias="[ANV]" Column="AttributeId" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
<StartRange
ScanType="GE">
<RangeColumns>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator
ScalarString="[AdventureWorks].[sys].
[xml_index_nodes_2091154495_32000].[id] as [value:4].[id]">
<Identifier>

<ColumnReference Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[value:4]"
Column="id" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</StartRange>
<EndRange
ScanType="LT">
<RangeColumns>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:5]"
Column="id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator
ScalarString="getdescendantlimit([AdventureWorks].[sys].
[xml_index_nodes_2091154495_32000].[id] as [value:4].[id])">
<Intrinsic
FunctionName="getdescendantlimit">

<ScalarOperator>
<Identifier>

<ColumnReference Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[value:4]"
Column="id" />
</
Identifier>
</
ScalarOperator>
</Intrinsic>
</ScalarOperator>
</RangeExpressions>
</EndRange>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
</Extension>
</RelOp>
<Predicate>
<ScalarOperator
ScalarString="[Expr1049]=CONVERT_IMPLICIT(sql_variant,CONVERT_IMPLICIT(nvarchar(64),
[@middleName],0),0) AND CONVERT_IMPLICIT(nvarchar(64),[@middleName],0)
IS NOT NULL">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference
Column="Expr1049" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Convert
DataType="sql_variant" Style="0" Implicit="1">
<ScalarOperator>
<Convert
DataType="nvarchar" Length="128" Style="0" Implicit="1">
<ScalarOperator>
<Identifier>
<ColumnReference
Column="@middleName" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</Convert>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Convert
DataType="nvarchar" Length="128" Style="0" Implicit="1">
<ScalarOperator>
<Identifier>
<ColumnReference
Column="@middleName" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" /
>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</Filter>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp NodeId="23" PhysicalOp="Filter"
LogicalOp="Filter" EstimateRows="1" EstimateIO="0"
EstimateCPU="8.7696e-007" AvgRowSize="9"
EstimatedTotalSubtreeCost="12.8886" Parallel="1"
EstimateRebinds="19971" EstimateRewinds="0">
<OutputList />
<Filter StartupExpression="0">
<RelOp NodeId="24" PhysicalOp="UDX"
LogicalOp="UDX" EstimateRows="1.10889" EstimateIO="0"
EstimateCPU="1.512e-006" AvgRowSize="4019"
EstimatedTotalSubtreeCost="12.8758" Parallel="1"
EstimateRebinds="19971" EstimateRewinds="0">
<OutputList>
<ColumnReference Column="Expr1019" />
</OutputList>
<MemoryFractions Input="0" Output="0" />
<Extension UDXName="XQUERY DATA">
<DefinedValues>
<DefinedValue>
<ColumnReference
Column="Expr1017" />
</DefinedValue>
<DefinedValue>
<ColumnReference
Column="Expr1018" />
</DefinedValue>
<DefinedValue>
<ColumnReference
Column="Expr1019" />
</DefinedValue>
<DefinedValue>
<ColumnReference
Column="Expr1020" />
</DefinedValue>
<DefinedValue>
<ColumnReference
Column="Expr1021" />
</DefinedValue>
</DefinedValues>
<UsedUDXColumns>
<ColumnReference Column="Expr1013" /
>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="nid" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="tid" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="value" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="lvalue" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="lvaluebin" />
<ColumnReference Column="Expr1010" /
>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="pk1" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="id" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="id" />
</UsedUDXColumns>
<RelOp NodeId="25" PhysicalOp="Compute
Scalar" LogicalOp="Compute Scalar" EstimateRows="1.10889"
EstimateIO="0" EstimateCPU="1.89e-007" AvgRowSize="12974"
EstimatedTotalSubtreeCost="12.6789" Parallel="1"
EstimateRebinds="19971" EstimateRewinds="0">
<OutputList>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="id" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="pk1" />
<ColumnReference
Column="Expr1010" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="id" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="nid" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="tid" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="value" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="lvalue" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="lvaluebin" />
<ColumnReference
Column="Expr1013" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference
Column="Expr1010" />
<ScalarOperator
ScalarString="0x58">
<Const ConstValue="0x58" />
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference
Column="Expr1013" />
<ScalarOperator
ScalarString="manufacturesubnodeordpath([Expr1059],[AdventureWorks].
[sys].[xml_index_nodes_2091154495_32000].[id] as [name:2].[id],
[AdventureWorks].[sys].[xml_index_nodes_2091154495_32000].[id] as
[Desc:3].[id])">
<Intrinsic
FunctionName="manufacturesubnodeordpath">
<ScalarOperator>
<Identifier>
<ColumnReference
Column="Expr1059" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="id" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp NodeId="26"
PhysicalOp="Nested Loops" LogicalOp="Inner Join"
EstimateRows="1.10889" EstimateIO="0" EstimateCPU="7.9002e-006"
AvgRowSize="12522" EstimatedTotalSubtreeCost="12.6766" Parallel="1"
EstimateRebinds="19971" EstimateRewinds="0">
<OutputList>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="id" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="pk1" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="id" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="nid" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="tid" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="value" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="lvalue" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="lvaluebin" />
<ColumnReference
Column="Expr1059" />
</OutputList>
<NestedLoops Optimized="0">
<OuterReferences>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="id" />
<ColumnReference
Column="Expr1060" />
</OuterReferences>
<RelOp NodeId="27"
PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar"
EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="922"
EstimatedTotalSubtreeCost="7.12918" Parallel="1"
EstimateRebinds="19971" EstimateRewinds="0">
<OutputList>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="id" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="pk1" />
<ColumnReference
Column="Expr1059" />
<ColumnReference
Column="Expr1060" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference
Column="Expr1059" />
<ScalarOperator
ScalarString="manufactureconstructordpath(0x58,[AdventureWorks].[sys].
[xml_index_nodes_2091154495_32000].[id] as [name:2].[id],0x)">
<Intrinsic
FunctionName="manufactureconstructordpath">
<ScalarOperator>
<Const
ConstValue="0x58" />
</ScalarOperator>
<ScalarOperator>
<Identifier>

<ColumnReference Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const
ConstValue="0x" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference
Column="Expr1060" />
<ScalarOperator
ScalarString="getdescendantlimit([AdventureWorks].[sys].
[xml_index_nodes_2091154495_32000].[id] as [name:2].[id])">
<Intrinsic
FunctionName="getdescendantlimit">
<ScalarOperator>
<Identifier>

<ColumnReference Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="id" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp NodeId="28"
PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek"
EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.000159079"
AvgRowSize="25" EstimatedTotalSubtreeCost="7.08641" Parallel="1"
EstimateRebinds="19971" EstimateRewinds="0">
<OutputList>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="id" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="hid" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="pk1" />
</OutputList>
<IndexScan Ordered="1"
ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="id" />
</DefinedValue>
<DefinedValue>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="hid" />
</DefinedValue>
<DefinedValue>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="pk1" />
</DefinedValue>
</DefinedValues>
<Object
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]"
Index="[pxx_AttributesNameValue_Attribute]" Alias="[name:2]" />
<SeekPredicates>
<SeekPredicate>
<Prefix
ScanType="EQ">
<RangeColumns>

<ColumnReference Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="pk1" />
</RangeColumns>

<RangeExpressions>

<ScalarOperator ScalarString="[AdventureWorks].[dbo].
[AttributesNameValue].[AttributeId] as [ANV].[AttributeId]">
<Identifier>

<ColumnReference Database="[AdventureWorks]" Schema="[dbo]"
Table="[AttributesNameValue]" Alias="[ANV]" Column="AttributeId" />
</
Identifier>
</
ScalarOperator>
</
RangeExpressions>
</Prefix>
<StartRange
ScanType="GE">
<RangeColumns>

<ColumnReference Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="id" />
</RangeColumns>

<RangeExpressions>

<ScalarOperator ScalarString="[AdventureWorks].[sys].
[xml_index_nodes_2091154495_32000].[id] as [attribute:1].[id]">
<Identifier>

<ColumnReference Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[attribute:1]"
Column="id" />
</
Identifier>
</
ScalarOperator>
</
RangeExpressions>
</StartRange>
<EndRange
ScanType="LT">
<RangeColumns>

<ColumnReference Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="id" />
</RangeColumns>

<RangeExpressions>

<ScalarOperator ScalarString="getdescendantlimit([AdventureWorks].
[sys].[xml_index_nodes_2091154495_32000].[id] as [attribute:1].[id])">
<Intrinsic
FunctionName="getdescendantlimit">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[attribute:1]"
Column="id" />
</
Identifier>
</
ScalarOperator>
</Intrinsic>
</
ScalarOperator>
</
RangeExpressions>
</EndRange>
</SeekPredicate>
</SeekPredicates>
<Predicate>
<ScalarOperator
ScalarString="[AdventureWorks].[sys].
[xml_index_nodes_2091154495_32000].[hid] as [name:2].[hid]=''
AND [AdventureWorks].[sys].[xml_index_nodes_2091154495_32000].[id] as
[attribute:1].[id]=getancestor([AdventureWorks].[sys].
[xml_index_nodes_2091154495_32000].[id] as [name:2].[id],(1))">
<Logical
Operation="AND">
<ScalarOperator>
<Compare
CompareOp="EQ">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="hid" />
</
Identifier>
</
ScalarOperator>

<ScalarOperator>
<Const
ConstValue="''" />
</
ScalarOperator>
</Compare>
</
ScalarOperator>
<ScalarOperator>
<Compare
CompareOp="EQ">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[attribute:1]"
Column="id" />
</
Identifier>
</
ScalarOperator>

<ScalarOperator>
<Intrinsic
FunctionName="getancestor">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="id" />
</
Identifier>
</
ScalarOperator>

<ScalarOperator>
<Const
ConstValue="(1)" />
</
ScalarOperator>
</
Intrinsic>
</
ScalarOperator>
</Compare>
</
ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp NodeId="34"
PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek"
EstimateRows="1.10889" EstimateIO="0.003125" EstimateCPU="0.000159079"
AvgRowSize="12060" EstimatedTotalSubtreeCost="5.45489" Parallel="1"
EstimateRebinds="19971" EstimateRewinds="0">
<OutputList>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="id" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="nid" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="tid" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="value" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="lvalue" />
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="lvaluebin" />
</OutputList>
<IndexScan Ordered="1"
ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="id" />
</DefinedValue>
<DefinedValue>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="nid" />
</DefinedValue>
<DefinedValue>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="tid" />
</DefinedValue>
<DefinedValue>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="value" />
</DefinedValue>
<DefinedValue>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="lvalue" />
</DefinedValue>
<DefinedValue>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="lvaluebin" />
</DefinedValue>
</DefinedValues>
<Object
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]"
Index="[pxx_AttributesNameValue_Attribute]" Alias="[Desc:3]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="pk1" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator
ScalarString="[AdventureWorks].[dbo].[AttributesNameValue].
[AttributeId] as [ANV].[AttributeId]">
<Identifier>

<ColumnReference Database="[AdventureWorks]" Schema="[dbo]"
Table="[AttributesNameValue]" Alias="[ANV]" Column="AttributeId" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
<StartRange
ScanType="GE">
<RangeColumns>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator
ScalarString="[AdventureWorks].[sys].
[xml_index_nodes_2091154495_32000].[id] as [name:2].[id]">
<Identifier>

<ColumnReference Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[name:2]"
Column="id" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</StartRange>
<EndRange
ScanType="LT">
<RangeColumns>
<ColumnReference
Database="[AdventureWorks]" Schema="[sys]"
Table="[xml_index_nodes_2091154495_32000]" Alias="[Desc:3]"
Column="id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator
ScalarString="[Expr1060]">
<Identifier>

<ColumnReference Column="Expr1060" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</EndRange>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</ComputeScalar>
</RelOp>
</Extension>
</RelOp>
<Predicate>
<ScalarOperator
ScalarString="[Expr1019]=CONVERT_IMPLICIT(sql_variant,N'middleName',
0)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference
Column="Expr1019" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Convert DataType="sql_variant"
Style="0" Implicit="1">
<ScalarOperator>
<Const
ConstValue="N'middleName'" />
</ScalarOperator>
</Convert>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</Filter>
</RelOp>
</NestedLoops>
</RelOp>
</NestedLoops>
</RelOp>
</Parallelism>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
=== QUERY PLAN END


ML

unread,
Apr 26, 2008, 7:02:00 AM4/26/08
to
Yeah, well... Sadly, this is pretty much it. :) The way the optimizer chooses
XML indexes is beyond my comprehension. Of course, XML index hints are not
supported, so we can do nothing more but to trust the optimizer.

In your example, are you using XML Schema Collections? The chances of the
optimizer finding a good execution plan are much better with typed XML. In
fact, I believe the property index is used simply because the optimizer lacks
information on XML node types.

The EAV approach is ofter used in situations where the range of attributes
is not known in advance or when a unified model is used to store entities
with a fixed set of common attributes and a variable set of additional
attributes. In the latter case one could try using indexed views (one IV per
entity type) and reference those in queries rather than searchign through XML
instances at retrieval. Have you considered that option? Does it apply to
your case at all?

CD

unread,
Apr 26, 2008, 2:55:03 PM4/26/08
to
Hello ML,

I appreciate your comments.

I agree with you that the creation of the query plan is a bit of black
magic to me.
However, when I inspected the the query plan for Q1 of your article it
actually
made sense, while the query plan that was generated in my system
didn't.
I wonder if it has to do with the lack of schema as you mentioned
(I thought the examples you had in your article were schema-less. Do
you have a link
in your articles to the schema you are binding to the columns of the
AttributesNameValue
and AttributesTyped tables?). Also, the machine that I am using has a
dual processor
and I believe that also affects the query
plans.

In my example, however, I am using a schema collection. Therefore I do
not understand why
I do not experience a performance gain when moving the comparison from
the TSQL world as in

WHERE A.Data.value('string((/Top/Props/Prop[N="Prop1"][1]/V)[1])',

'nvarchar(260)') = 'ABC'

to the Xquery world like in

WHERE A.Data.exist('(/Top/Props/Prop[N="Prop1"][string(V) = "ABC"])' =
1

With respect to your last point: can I promote the properties on which
I want to search
from the xml to a relational entity (table or view) and perform my
searches there? In my case,
the xml in the table has a similar schema to the key-value list that
you use in your article. I
already tried to promote the properties to a indexed view and it is
blazing fast. The problem is
that our system is dynamic and the user is able to add properties
dynamically, which result
in the creation of new key-value elements in the xml. As a result, the
view would have to be
dropped and re-created. This is problematic because, as the view is
recreated, the indices are rebuilt.
Since we have a lot of data, the process takes a long time.

Any suggestions?

Kind regards
CD

On Apr 26, 4:02 am, ML <M...@discussions.microsoft.com> wrote:
> Yeah, well... Sadly, this is pretty much it. :) The way the optimizer choosesXMLindexes is beyond my comprehension. Of course,XMLindex hints are not


> supported, so we can do nothing more but to trust the optimizer.
>

> In your example, are you usingXMLSchema Collections? The chances of the
> optimizer finding a good execution plan are much better with typedXML. In


> fact, I believe the property index is used simply because the optimizer lacks

> information onXMLnode types.

Mike C#

unread,
Apr 27, 2008, 11:05:54 AM4/27/08
to
BTW, I would think that you would only need the primary XML index and a PATH
secondary XML index. Every additional secondary XML index just takes up more
storage space without really adding anything to what you're trying to
accomplish here.

As the others mentioned you're performing a table scan because of your LIKE
clause. Can you use LIKE clauses like 'ABC%' and slap an index on that
column? Or use full-text indexing as the others suggested? Your
performance is going to continue to degrade as you add rows to the table
because of the table scans required to fulfill that LIKE clause.

"CD" <crb...@yahoo.com> wrote in message
news:b46f6d2b-e687-4285...@w5g2000prd.googlegroups.com...

Mike C#

unread,
Apr 27, 2008, 4:00:55 PM4/27/08
to
BTW, the XQuery string() function is pretty costly. You might try seeing if
you can change this to use CROSS APPLY and maybe the exists() function and
see if that improves performance to some degree. I played around with it a
little bit, but don't have time to dive into it right now. Here's an
example of what I came up with. Not a terrible execution plan, but I don't
have a large amount of data to test with here either. I'm not 100% on the
performance implications of XQuery contains() versus SQL LIKE either.

SELECT
A.id,
A.Name,
A.Type,
N.value('(.[N=" Prop1 "]/V)[1]', 'nvarchar(260)') AS P1,
N.value('(.[N=" Prop2 "]/V)[1]', 'nvarchar(260)') AS P2,
N.value('(.[N=" Prop3 "]/V)[1]', 'nvarchar(260)') AS P3
FROM A
CROSS APPLY A.Data.nodes('(/Top/Props/Prop)') T(N)
WHERE N.exist('N[contains(., "Prop")]') = 1


"Mike C#" <x...@xyz.com> wrote in message
news:Or%23YzgHq...@TK2MSFTNGP06.phx.gbl...

ML

unread,
May 5, 2008, 4:23:00 AM5/5/08
to
> I do not experience a performance gain when moving the comparison from
> the TSQL world as in
>
> WHERE A.Data.value('string((/Top/Props/Prop[N="Prop1"][1]/V)[1])',
> 'nvarchar(260)') = 'ABC'
>
> to the Xquery world like in
>
> WHERE A.Data.exist('(/Top/Props/Prop[N="Prop1"][string(V) = "ABC"])' = 1

How has the execution plan changed after that modification? Is the value XML
index being used? Try dropping the property XML index and see if there are
any changes.


> With respect to your last point: can I promote the properties on which
> I want to search
> from the xml to a relational entity (table or view) and perform my
> searches there? In my case,
> the xml in the table has a similar schema to the key-value list that
> you use in your article. I
> already tried to promote the properties to a indexed view and it is
> blazing fast. The problem is
> that our system is dynamic and the user is able to add properties
> dynamically, which result
> in the creation of new key-value elements in the xml. As a result, the
> view would have to be
> dropped and re-created. This is problematic because, as the view is
> recreated, the indices are rebuilt.
> Since we have a lot of data, the process takes a long time.
>
> Any suggestions?

If your schema must stay open - at least in part - then the performance
issues with querying XML (or querying a relational EAV model) cannot be
avoided completely.

How often do your users add new attributes to the model? Have you considered
designing a pre-defined set of attributes (enforced by XML Schema) that the
users can choose from rather than adding them sporadically? A good practice
is to provide a fixed attribute set that covers all (or most) cases which
might leave only a few additional attributes that would need to be managed in
XML.

0 new messages