我有一个存储过程，它使用 SELECT FOR XML PATH 语句将 XML 返回给调用者。随着越来越多的行被添加到查询的主表中，我注意到该查询的性能已经下降。

经过调查，我发现在没有 FOR XML 语句的情况下在 SQL Management Studio 中运行查询所花费的时间是 FOR XML 查询所用时间的 1/3。由 FOR XML 调用的 XML 生成是否有很大的开销，或者在使用 FOR XML 时是否有一些该做和不该做的事情。

下面是我的表定义和使用的返回 > 3000 行的查询。更改了列名以保护无辜者。

欢迎任何建议。

CREATE TABLE dbo.results
( 
colA  int NOT NULL, 
colB  varchar(20) NULL, 
colC varchar(30) NULL, 
colD varchar(100) NULL, 
colE char(3) NULL, 
colF int NULL, 
colG int NULL, 
colH datetime NULL, 
colJ int NULL, 
colK int NULL, 
colL int NULL, 
colM int NULL, 
colN int NULL, 
colO int NULL, 
colP int NULL, 
colQ int NULL, 
colR int NULL, 
colS int NULL, 
colT int NULL, 
colU int NULL, 
colV int NULL, 
colW int NULL, 
colX int NULL, 
colY datetime NULL, 
colZ int NULL, 
colA1 datetime NULL, 
colB1 int NULL, 
colC1 int NULL, 
colD1 int NULL, 
colE1 int NULL, 
colF1 int NULL, 
colG1 int NULL, 
colH1 int NULL, 
colI1 int NULL, 
colK1 int NULL, 
colL1 int NULL, 
colM1 int NULL, 
colN1 int NULL, 
colO1 int NULL, 
colP1 int NOT NULL, 
colQ1 int NOT NULL, 
colS1 int NULL, 
colT1 int NULL, 
colU1 int NULL, 
colV1 int NULL, 
colW1 int NULL, 
colX1 int NULL, 
colY1 int NULL, 
colZ1 datetime NULL 

CONSTRAINT results_pk PRIMARY KEY CLUSTERED 
( 
   colA ASC 
)
WITH (PAD_INDEX  = OFF, 
      STATISTICS_NORECOMPUTE  = OFF, 
      IGNORE_DUP_KEY = OFF, 
      ALLOW_ROW_LOCKS  = ON, 
      ALLOW_PAGE_LOCKS  = ON) 
 ON PRIMARY) 

查询:

select    colA  "@A", 
          colB "@B", 
          colC "@C", 
          colD "@D", 
          colE "@E", 
          colF "@F", 
          colG "@G",                      
          colH "@H",         
          colJ "@J", 
          colK "@K",            
          colL "@L", 
          colM "@M", 
          colO "@O", 
          colN "@N", 
          colP "@P", 
          colQ "@Q", 
          colR "@R", 
          colZ1 "@Z1", 
          colS "@S", 
          colT "@T", 
          colU "@U", 
          colV "@V", 
          colW "@W", 
          colX "@X", 
          colY "@Y", 
          colP1 "@P1", 
          colQ1 "@Q1", 
          colO1 "@O1" 
from result
order by colO desc , colC 
for xml PATH('item'), TYPE 

最佳答案

只是为了确保您没有将客户端呈现时间计入等式，将结果分配给一个变量并查看执行时间是否相同。这是我刚刚在我的服务器上运行的示例:

SET STATISTICS TIME ON
go

DECLARE @x XML
PRINT '------------'
SELECT @x =
(SELECT * FROM sys.[dm_exec_connections] AS dec
FOR XML PATH('connections'), TYPE)
PRINT '------------'

SELECT * FROM sys.[dm_exec_connections] AS dec
FOR XML PATH('connections'), TYPE

结果如下(查看执行时间):

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 87 ms.
------------

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 34 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.
------------

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 884 ms.

将它放入一个变量中花费了 34+2=36 毫秒，而将它转储到我的屏幕上花费了 884 毫秒。这是非常不同的!

关于sql-server - SELECT FOR XML 查询速度慢吗？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/6270857/