XQuery Plan Complexity

Question

I have a SQLCLR scalar function that will handle the XmlReader, which I need to drop on demand into the built-in result set. These XML objects are generated on demand, so I cannot use the XML index. The resulting datasets will accept more than 100 columns. Consider this code example:

CREATE XML SCHEMA COLLECTION RAB AS ' <xsd:schema xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified"> <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" /> <xsd:element name="r" type="r"/> <xsd:complexType name="r"> <xsd:attribute name="a" type="sqltypes:int" use="required"/> <xsd:attribute name="b" type="sqltypes:int" use="required"/> <xsd:attribute name="c" type="sqltypes:int" use="required"/> </xsd:complexType> </xsd:schema>'; GO DECLARE @D TABLE(x XML(DOCUMENT RAB) NOT NULL); INSERT INTO @D VALUES ('<ra="3" b="4" c="34"/>'), ('<ra="5" b="6" c="56"/>'), ('<ra="7" b="8" c="78"/>') SELECT x.value('/r/@a', 'int') a, x.value('/r/@b', 'int') b, x.value('/r/@c', 'int') c FROM @da 

This fills the typed XML column in a table variable with some XML values ​​and breaks the attributes into separate columns. The execution plan for this seems too messy:

  |--Compute Scalar(DEFINE:([Expr1009]=[Expr1008], [Expr1016]=[Expr1015], [Expr1023]=[Expr1022])) |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[x])) |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[x])) | |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[x])) | | |--Table Scan(OBJECT:(@d AS [a])) | | |--Stream Aggregate(DEFINE:([Expr1008]=MIN([Expr1024]))) | | |--Compute Scalar(DEFINE:([Expr1024]=CASE WHEN datalength(CONVERT_IMPLICIT(sql_variant,CONVERT_IMPLICIT(nvarchar(64),xsd_cast_to_maybe_large(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader wi | | |--Table-valued function | |--Stream Aggregate(DEFINE:([Expr1015]=MIN([Expr1025]))) | |--Compute Scalar(DEFINE:([Expr1025]=CASE WHEN datalength(CONVERT_IMPLICIT(sql_variant,CONVERT_IMPLICIT(nvarchar(64),xsd_cast_to_maybe_large(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XP | |--Table-valued function |--Stream Aggregate(DEFINE:([Expr1022]=MIN([Expr1026]))) |--Compute Scalar(DEFINE:([Expr1026]=CASE WHEN datalength(CONVERT_IMPLICIT(sql_variant,CONVERT_IMPLICIT(nvarchar(64),xsd_cast_to_maybe_large(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath f |--Table-valued function 

He got a nested loop for each column! The query plan will be too complicated if I join several of these tables with 100 columns each. Also, I do not understand the purpose of these StreamAggregate . The content will look like this:

 MIN( CASE WHEN @d.[x] as [a].[x] IS NULL THEN NULL ELSE CASE WHEN datalength(CONVERT_IMPLICIT(sql_variant, CONVERT_IMPLICIT(nvarchar(64),xsd_cast_to_maybe_large(xrpf.[value],xrpf.[lvalue],xrpf.[lvaluebin],xrpf.[tid],(15),(7)) ,0),0))>=(128) THEN CONVERT_IMPLICIT(int, CASE WHEN datalength(xsd_cast_to_maybe_large(xrpf.[value],xrpf.[lvalue],xrpf.[lvaluebin],xrpf.[tid],(15),(7)))<(128) THEN NULL ELSE xsd_cast_to_maybe_large(xrpf.[value],xrpf.[lvalue],xrpf.[lvaluebin],xrpf.[tid],(15),(7)) END,0) ELSE CONVERT_IMPLICIT(int, CONVERT_IMPLICIT(sql_variant,CONVERT_IMPLICIT(nvarchar(64),xsd_cast_to_maybe_large(xrpf.[value],xrpf.[lvalue],xrpf.[lvaluebin],xrpf.[tid],(15),(7)),0),0),0) END END) 

Ugh! I thought using a typed XML group with sqltype would have to avoid conversion?

Either I overestimate how effective it will be, or I am doing something wrong. My question is, how can I fix this, so I don't have additional query plan operators added for each column, and ideally avoid conversions, or should I give up and find a non-xpath way to do this?

Literature:

sqlTypes http://msdn.microsoft.com/en-us/library/ee320775%28v=sql.105%29.aspx

XML Data Type Methods http://technet.microsoft.com/en-us/library/ms190798%28v=sql.105%29.aspx

Answer 1

There are some puzzles in the query plan that need to be sorted first. What does a computational scalar do and why does a stream aggregate exist.

The table table function returns a node table of shredded XML, one row for each shredded row. When you use typed XML, these columns are lvalue, lvaluebin, and tid. These columns are used in a computational scalar to calculate the actual value. The code there looks a little strange, and I can’t say that I understand why it is what it is, but the bottom line is that the xsd_cast_to_maybe_large function returns a value, and there is a code that handles the case when the value is equal and greater than 128 bytes.

 CASE WHEN datalength( CONVERT_IMPLICIT(sql_variant, CONVERT_IMPLICIT(nvarchar(64), xsd_cast_to_maybe_large(XML Reader with XPath filter.[value], XML Reader with XPath filter.[lvalue], XML Reader with XPath filter.[lvaluebin], XML Reader with XPath filter.[tid],(15),(5),(0)),0),0))>=(128) THEN CONVERT_IMPLICIT(int,CASE WHEN datalength(xsd_cast_to_maybe_large(XML Reader with XPath filter.[value], XML Reader with XPath filter.[lvalue], XML Reader with XPath filter.[lvaluebin], XML Reader with XPath filter.[tid],(15),(5),(0)))<(128) THEN NULL ELSE xsd_cast_to_maybe_large(XML Reader with XPath filter.[value], XML Reader with XPath filter.[lvalue], XML Reader with XPath filter.[lvaluebin], XML Reader with XPath filter.[tid],(15),(5),(0)) END,0) ELSE CONVERT_IMPLICIT(int,CONVERT_IMPLICIT(sql_variant, CONVERT_IMPLICIT(nvarchar(64), xsd_cast_to_maybe_large(XML Reader with XPath filter.[value], XML Reader with XPath filter.[lvalue], XML Reader with XPath filter.[lvaluebin], XML Reader with XPath filter.[tid],(15),(5),(0)),0),0),0) END 

The same computational scalar for untyped XML is much simpler and more understandable.

 CASE WHEN datalength(XML Reader with XPath filter.[value])>=(128) THEN CONVERT_IMPLICIT(int,XML Reader with XPath filter.[lvalue],0) ELSE CONVERT_IMPLICIT(int,XML Reader with XPath filter.[value],0) END 

If in value from more than value selection from lvalue else is extracted from value . In the case of untyped XML, the returned node table only displays the column identifiers, value, and lvalue.

When you use typed XML, the node's value store is optimized based on the data type specified in the schema. It looks like it can either appear in the value, lvalue or lvaluebin in the node table, depending on what type of value it and xsd_cast_to_maybe_large are to help figure it out.

The stream aggregate performs min () on the return values ​​from the calculation scalar. We know, and SQL Server (at least sometimes) knows that someday only one row will be returned from the function associated with the table when you specify XPath in the value () function. The parser ensures that we build XPath correctly, but when the query optimizer looks at the evaluated lines, it will see 200 lines. The base grade for the table grade feature that parses XML is 10,000 rows, and then some adjustments are made using the XPath used. In this case, it ends in 200 rows, where there is only one. Pure speculation on my part is that the flow aggregate must take care of this mismatch. It will never aggregate anything, it sends only one row, which is returned, but it affects the power estimate for the whole branch and ensures that the optimizer uses 1 row as an estimate for this branch. This, of course, is really important when the optimizer chooses combining strategies, etc.

So what about 100 attributes? Yes, there will be 100 branches if you use the value function 100 times. But there are several optimizations here. I created a test setup to see which form and request form would be the fastest, using 100 attributes over 10 lines.

The winner was to use untyped XML and not use the nodes() function to clone r .

 select X.value('(/r/@a1)[1]', 'int') as a1, X.value('(/r/@a2)[1]', 'int') as a2, X.value('(/r/@a3)[1]', 'int') as a3 from @T 

There is also a way to avoid 100 branches with a hinge, but depending on what your actual request looks like, it might not be possible. The data type coming out of the turn must be the same. Of course, you can extract them as a string and convert them to the corresponding list in the column list. It also requires your table to have a primary / unique key.

 select a1, a2, a3 from ( select T.ID, -- primary key of @T AXvalue('local-name(.)', 'nvarchar(50)') as Name, AXvalue('.', 'int') as Value from @T as T cross apply TXnodes('/r/@*') as A(X) ) as T pivot(min(T.Value) for Name in (a1, a2, a3)) as P 

Query schema for master query, 10 rows of 100 attributes:

Below are the results and the test setup I used. I tested 100 attributes and 10 rows and all int attributes.

Result:

 Test Duration (ms) -------------------------------------------------- ------------- untyped XML value('/r[1]/@a') 195 untyped XML value('(/r/@a)[1]') 108 untyped XML value('@a') cross apply nodes('/r') 131 untyped XML value('@a') cross apply nodes('/r[1]') 127 typed XML value('/r/@a') 185 typed XML value('(/r/@a)[1]') 148 typed XML value('@a') cross apply nodes('/r') 176 untyped XML pivot 34 typed XML pivot 52 

the code:

 drop type dbo.TRABType drop type dbo.TType; drop xml schema collection dbo.RAB; go declare @NumAtt int = 100; declare @Attribs nvarchar(max); with xmlnamespaces('http://www.w3.org/2001/XMLSchema' as xsd) select @Attribs = ( select top(@NumAtt) 'a'+cast(row_number() over(order by 1/0) as varchar(11)) as '@name', 'sqltypes:int' as '@type', 'required' as '@use' from sys.columns for xml path('xsd:attribute') ) --CREATE XML SCHEMA COLLECTION RAB AS declare @Schema nvarchar(max) = ' <xsd:schema xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified"> <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" /> <xsd:element name="r" type="r"/> <xsd:complexType name="r">[ATTRIBS]</xsd:complexType> </xsd:schema>'; set @Schema = replace(@Schema, '[ATTRIBS]', @Attribs) create xml schema collection RAB as @Schema go create type dbo.TType as table ( ID int identity primary key, X xml not null ); go create type dbo.TRABType as table ( ID int identity primary key, X xml(document rab) not null ); go declare @NumAtt int = 100; declare @NumRows int = 10; declare @X nvarchar(max); declare @C nvarchar(max); declare @M nvarchar(max); declare @S1 nvarchar(max); declare @S2 nvarchar(max); declare @S3 nvarchar(max); declare @S4 nvarchar(max); declare @S5 nvarchar(max); declare @S6 nvarchar(max); declare @S7 nvarchar(max); declare @S8 nvarchar(max); declare @S9 nvarchar(max); set @X = N'<r '+ ( select top(@NumAtt) 'a'+cast(row_number() over(order by 1/0) as varchar(11))+'="'+cast(row_number() over(order by 1/0) as varchar(11))+'" ' from sys.columns for xml path('') )+ '/>'; set @C = stuff(( select top(@NumAtt) ',a'+cast(row_number() over(order by 1/0) as varchar(11)) from sys.columns for xml path('') ), 1, 1, '') set @M = stuff(( select top(@NumAtt) ',MAX(CASE WHEN name = ''a'+cast(row_number() over(order by 1/0) as varchar(11))+''' THEN val END)' from sys.columns for xml path('') ), 1, 1, '') declare @T dbo.TType; insert into @T(X) select top(@NumRows) @X from sys.columns; declare @TRAB dbo.TRABType; insert into @TRAB(X) select top(@NumRows) @X from sys.columns; -- value('/r[1]/@a') set @S1 = N' select T.ID'+ ( select top(@NumAtt) ', TXvalue(''/r[1]/@a'+cast(row_number() over(order by 1/0) as varchar(11))+''', ''int'')' from sys.columns for xml path('') )+ ' from @T as T option (maxdop 1)'; -- value('(/r/@a)[1]') set @S2 = N' select T.ID'+ ( select top(@NumAtt) ', TXvalue(''(/r/@a'+cast(row_number() over(order by 1/0) as varchar(11))+')[1]'', ''int'')' from sys.columns for xml path('') )+ ' from @T as T option (maxdop 1)'; -- value('@a') cross apply nodes('/r') set @S3 = N' select T.ID'+ ( select top(@NumAtt) ', T2.X.value(''@a'+cast(row_number() over(order by 1/0) as varchar(11))+''', ''int'')' from sys.columns for xml path('') )+ ' from @T as T cross apply TXnodes(''/r'') as T2(X) option (maxdop 1)'; -- value('@a') cross apply nodes('/r[1]') set @S4 = N' select T.ID'+ ( select top(@NumAtt) ', T2.X.value(''@a'+cast(row_number() over(order by 1/0) as varchar(11))+''', ''int'')' from sys.columns for xml path('') )+ ' from @T as T cross apply TXnodes(''/r[1]'') as T2(X) option (maxdop 1)'; -- value('/r/@a') typed XML set @S5 = N' select T.ID'+ ( select top(@NumAtt) ', TXvalue(''/r/@a'+cast(row_number() over(order by 1/0) as varchar(11))+''', ''int'')' from sys.columns for xml path('') )+ ' from @TRAB as T option (maxdop 1)'; -- value('(/r/@a)[1]') set @S6 = N' select T.ID'+ ( select top(@NumAtt) ', TXvalue(''(/r/@a'+cast(row_number() over(order by 1/0) as varchar(11))+')[1]'', ''int'')' from sys.columns for xml path('') )+ ' from @TRAB as T option (maxdop 1)'; -- value('@a') cross apply nodes('/r') typed XML set @S7 = N' select T.ID'+ ( select top(@NumAtt) ', T2.X.value(''@a'+cast(row_number() over(order by 1/0) as varchar(11))+''', ''int'')' from sys.columns for xml path('') )+ ' from @TRAB as T cross apply TXnodes(''/r'') as T2(X) option (maxdop 1)'; -- pivot set @S8 = N' select ID, ' +@C +' from ( select T.ID, AXvalue(''local-name(.)'', ''nvarchar(50)'') as Name, AXvalue(''.'', ''int'') as Value from @T as T cross apply TXnodes(''/r/@*'') as A(X) ) as T pivot(min(T.Value) for Name in (' +@C +')) as P option (maxdop 1)'; -- typed pivot set @S9 = N' select ID, ' +@C +' from ( select T.ID, AXvalue(''local-name(.)'', ''nvarchar(50)'') as Name, cast(cast(AXquery(''string(.)'') as varchar(11)) as int) as Value from @TRAB as T cross apply TXnodes(''/r/@*'') as A(X) ) as T pivot(min(T.Value) for Name in (' +@C +')) as P option (maxdop 1)'; exec sp_executesql @S1, N'@T dbo.TType readonly', @T; exec sp_executesql @S2, N'@T dbo.TType readonly', @T; exec sp_executesql @S3, N'@T dbo.TType readonly', @T; exec sp_executesql @S4, N'@T dbo.TType readonly', @T; exec sp_executesql @S5, N'@TRAB dbo.TRABType readonly', @TRAB; exec sp_executesql @S6, N'@TRAB dbo.TRABType readonly', @TRAB; exec sp_executesql @S7, N'@TRAB dbo.TRABType readonly', @TRAB; exec sp_executesql @S8, N'@T dbo.TType readonly', @T; exec sp_executesql @S9, N'@TRAB dbo.TRABType readonly', @TRAB;