sql-server - 为什么在使用 XQuery 时执行计划中有排序

Question

我在 SQL Server 中有一个包含 10,000,000 行的表

CREATE TABLE [dbo].[tmpTable](
    [EventId] [int] NULL,
    [Data] [nvarchar](max) NULL
) 
CREATE NONCLUSTERED INDEX [ci] ON [dbo].[tmpTable]
(
    [EventId] ASC
)
INCLUDE ([Data])

数据列是 XML

当我使用 SQL 时，

    SELECT  EventId
  , CAST(Data AS xml ).value('(/d/nv/@v)[1]', 'uniqueidentifier') AS ID1
  , CAST(Data AS xml ).value('(/d/nv/@v)[2]', 'int') AS ID2
  , CAST(Data AS xml ).value('(/d/nv/@v)[3]', 'bigint') AS ID3
  , CAST(Data AS xml ).value('(/d/nv/@v)[4]', 'bit') AS ID4
  , CAST(Data AS xml ).value('(/d/nv/@v)[5]', 'nvarchar(100)') AS ID5
  , CAST(Data AS xml ).value('(/d/nv/@v)[6]', 'nvarchar(100)') AS ID6
  , CAST(Data AS xml ).value('(/d/nv/@v)[7]', 'bigint') AS ID7
  , CAST(Data AS xml ).value('(/d/nv/@v)[8]', 'int') AS ID8
    FROM tmpTable
    WHERE EventId = 100

执行计划中会有Sort？这会破坏性能。但是，如果我使用

Select Top 100

排序将消失。任何的想法？有什么办法可以去掉排序

执行计划:

|--Compute Scalar(DEFINE:([Expr1011]=[Expr1010], [Expr1022]=[Expr1021], [Expr1033]=[Expr1032], [Expr1044]=[Expr1043], [Expr1055]=[Expr1054], [Expr1066]=[Expr1065], [Expr1077]=[Expr1076], [Expr1088]=[Expr1087]))
       |--Parallelism(Gather Streams)
            |--Nested Loops(Inner Join, OUTER REFERENCES:([tmpTable].[dbo].[tmptmpTable].[Data]))
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([tmpTable].[dbo].[tmptmpTable].[Data]))
                 |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tmpTable].[dbo].[tmptmpTable].[Data]))
                 |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tmpTable].[dbo].[tmptmpTable].[Data]))
                 |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tmpTable].[dbo].[tmptmpTable].[Data]))
                 |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tmpTable].[dbo].[tmptmpTable].[Data]))
                 |    |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tmpTable].[dbo].[tmptmpTable].[Data]))
                 |    |    |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tmpTable].[dbo].[tmptmpTable].[Data]))
                 |    |    |    |    |    |    |    |--Sort(ORDER BY:([tmpTable].[dbo].[tmptmpTable].[Data] ASC))
                 |    |    |    |    |    |    |    |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([tmpTable].[dbo].[tmptmpTable].[Data]))
                 |    |    |    |    |    |    |    |         |--Table Scan(OBJECT:([tmpTable].[dbo].[tmptmpTable]), WHERE:([tmpTable].[dbo].[tmptmpTable].[EventId]=(100)))

示例数据

<d>
  <nv n="MediaDesc" v="79cc07e3-8d4a-4c8a-bc9f-3fcba485532b" />
  <nv n="ChannelNumber" v="116" />
  <nv n="Duration" v="61773" />
  <nv n="IsTunedToService" v="True" />
  <nv n="StreamSelection" v="FULLSCREEN_SECONDARY" />
  <nv n="ChannelType" v="LiveTVMediaChannel" />
  <nv n="TuneID" v="634050840267464082" />
</d>

Answer 1

如果将列的数据类型更改为 XML 而不是转换为 XML，您将获得更好的性能。

转换为 XML 是一项开销很大的操作。 SQL Server 尽最大努力尽快为您提供所需的结果，在这种情况下，它看起来像是使用表假脱机来完成工作。假脱机在您提供的计划中不可见，但我相信它在嵌套循环连接内的每个分支中都存在。

一些代码可以重现您所看到的内容。

create table T(S nvarchar(max) null);

go

insert into T(S) 
select top(50) '<N>1</N><N>2</N>'
from sys.columns;

insert into T(S) 
select top(50) '<N>3</N><N>4</N>'
from sys.columns;

您的查询:

select cast(T.S as xml).value('(N/text())[2]', 'int')
from T;

查询计划:

lazy spool 缓存最后生成的结果集，只要外部的值相同，spool 就可以返回缓存的结果集(倒带)。但是当当前输入值不同于之前的输入值时，假脱机必须为新值生成结果(重新绑定(bind))。优化器添加排序运算符以最小化重新绑定(bind)的次数。

您可以使用跟踪标志 (8690) 删除创建带有排序和假脱机的计划的优化。 Microsoft 没有记录它，所以不要在生产中使用它，但您可以在测试中使用它来查看优化对您的系统有什么影响。

select cast(T.S as xml).value('(N/text())[2]', 'int')
from T
option (querytraceon 8690);

有关 Lazy Spool 是什么及其工作原理的更多信息，请参阅 this answer作者:Paul White 在 SQLPerformance.com 上。