为什么VALUES(CONVERT(XML,'...'))比VALUES(@xml)慢得多？

Question

我想创建一个子查询，作为单列结果生成数字列表，类似于 MindLoggedOut此处所做的, 但不使用@x xml变量，以便可以将其附加到WHERE表达式作为纯字符串（子查询），而不使用sql参数。问题在于参数（或变量）的替换使查询运行慢了5000倍，我不明白为什么。是什么导致了这个巨大的差异？

/* Create a minimalistic xml like <b><a>78</a><a>91</a>...</b> */
DECLARE @p_str VARCHAR(MAX) =
    '78 91 01 12 34 56 78 91 01 12 34 56 78 91 01 12 34 56';
DECLARE @p_xml XML = CONVERT(XML,
  '<b><a>'+REPLACE(@p_str,' ','</a><a>')+'</a></b>'
);

SELECT a.value('(child::text())[1]','INT')
FROM (VALUES (@p_xml)) AS t(x)
CROSS APPLY x.nodes('//a') AS x(a);

这将每行返回一个数字，速度相当快（比我迄今使用的字符串分割方法快20倍，类似于这些similar to these）。我是通过SQL Server CPU时间来测量20倍的加速效果的，其中@p_str包含3000个数字。

现在，如果我将@p_xml的定义内联到查询中：

SELECT a.value('(child::text())[1]','INT')
FROM (VALUES (CONVERT(XML,
  '<b><a>'+REPLACE(@p_str,' ','</a><a>')+'</a></b>'
))) AS t(x)
CROSS APPLY x.nodes('//a') AS x(a);

当@p_str包含数千个数字时，它会变得慢5000倍。查看查询计划，我找不到原因。

第一个查询的计划（…VALUES(@p_xml)…）和第二个查询的计划（…VALUES(CONVERT(XML,'...'))…）

有人能解释一下吗？

更新

很明显，第一个查询的计划并没有包括@p_xml = CONVERT(XML, ...REPLACE(...)... )这个赋值语句的成本，但是这个成本并不是可以解释整个脚本执行时间（当@p_str很大时）46毫秒和234秒之间巨大差异的罪魁祸首。这种差异是系统性（而非随机性）的，并且实际上在SqlAzure（S1层）中观察到了。

此外，当我重写查询时：将CONVERT(XML,...)替换为用户定义的标量函数时：

SELECT a.value('(child::text())[1]','INT')
FROM (VALUES (dbo.MyConvertToXmlFunc(
  '<b><a>'+REPLACE(@p_str,' ','</a><a>')+'</a></b>'
))) AS t(x)
CROSS APPLY x.nodes('//a') AS x(a);

其中dbo.MyConvertToXmlFunc()是：

CREATE FUNCTION dbo.MyConvertToXmlFunc(@p_str NVARCHAR(MAX))
RETURNS XML BEGIN
  RETURN CONVERT(XML, @p_str);
END;

差异消失了（计划）。所以至少我有一个解决方法...但我想理解它。

- robert4

---

你能否发布两个查询的SQL执行计划的图片，以便我们看到它们之间的差异？ - John Odom

我怀疑这是因为第一个执行计划测量的内容较少；即@p_xml的赋值（从字符串到XML的相关转换）不会在执行计划中显示，因此你并没有进行类似于类比的比较。 - JohnLBevan

实际计划有什么不同吗？估计行数有什么不同吗？ - Nick.McDermaid

Answer 1

这基本上是与Paul White的这个答案中描述的问题相同。

我尝试使用一个长度为10,745个字符，包含3,582个项的字符串。

使用字符串文字的执行计划最终对每个项目执行两次字符串替换并将整个字符串转换为XML（总共7,164次）。

下面的跟踪中突出显示了有问题的sqltses.dll！CEsExec :: GeneralEval4调用。整个调用堆栈的CPU时间为22.38％（几乎将四核心中的一个核心占满）。 - 其中92％是用于这两个调用。

在每个调用中，sqltses.dll！ConvertFromStringTypesAndXmlToXml和sqltses.dll！BhReplaceBhStrStr的时间几乎相等。

我已经在下面的计划中使用了相同的颜色编码。

执行计划的底部分支针对字符串中的每个拆分项执行一次。

问题所在的表值函数位于右下角的open方法中。该函数的参数列表为：

Scalar Operator([Expr1000]),

Scalar Operator((7)),

Scalar Operator(XML Reader with XPath filter.[id]),

Scalar Operator(getdescendantlimit(XML Reader with XPath filter.[id]))

对于流聚合，问题出现在其getrow方法中。

[Expr1010] = Scalar Operator(MIN(
SELECT CASE
         WHEN [Expr1000] IS NULL
           THEN NULL
         ELSE
           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
       END 
))

这两个表达式都指向Expr1000（虽然流聚合仅用于检查它是否为NULL）。

这在右上方的常量扫描中定义如下。

(Scalar Operator(CONVERT(xml,'<b><a>'+replace([@p_str],' '
,CONVERT_IMPLICIT(varchar(max),'</a><a>',0))+'</a></b>',0)))

从跟踪记录中可以清楚地看出，问题与先前链接的答案相同，并且在慢速计划中得到了反复重新评估。当将昂贵的计算作为参数传递时，只会发生一次。

---

编辑：我刚意识到这实际上与Paul White 在这里所描述的计划和问题几乎完全相同- 我测试中唯一的区别是我发现在VARCHAR（MAX）情况下，字符串替换和XML转换都很糟糕 - 而对于字符串替换来说，在非max情况下超过了转换成本。

最大值

非最大值

在这个测试中，替换操作的CPU成本几乎是XML转换的两倍。它似乎是通过使用熟悉的TSQL函数CHARINDEX和STUFF的代码实现的，其中大量时间用于将字符串转换为Unicode。我认为我的结果与Paul报告的结果之间的差异在于排序规则（从Latin1_General_CS_AS切换到SQL_Latin1_General_CP1_CS_AS会显著降低字符串替换的成本）。

- Martin Smith

---

Paul White的博客文章不仅讨论了我正在尝试实现的相同示例/用例，而且他提出了比我发现的更好的解决方法。谢谢！ - robert4