問題描述
我有一個存儲過程，它使用 SELECT FOR XML PATH 語句將 XML 返回給調(diào)用者.隨著更多行被添加到查詢的主表中，我注意到此查詢的性能有所下降.
I have a stored procedure which returns XML to the caller using a SELECT FOR XML PATH statement. As more rows have been added to the main table in the query I have noticed that the performance of this query has degraded.
經(jīng)過調(diào)查，我發(fā)現(xiàn)在沒有 FOR XML 語句的情況下在 SQL management studio 中運行查詢所需的時間是 FOR XML 查詢所需時間的 1/3.由 FOR XML 調(diào)用的 XML 生成是否有很大的開銷，或者在使用 FOR XML 時有哪些應(yīng)該做和不應(yīng)該做的事情.
On investigation I found that running the query in SQL management studio without the FOR XML statement takes the 1/3 of the time the FOR XML query takes. Is the XML generation that is invoked by FOR XML that much of an overhead or are there some do's and don't s when using FOR XML.
下面是我的表定義和使用的返回 > 3000 行的查詢.已更改列名稱以保護(hù)無辜者.
Below is my table definition and the query used which returns > 3000 rows. The column names have been changed to protect the innocent.
歡迎任何建議.
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
推薦答案
只是為了確保您沒有將客戶端渲染時間計入等式，將結(jié)果分配給一個變量并查看執(zhí)行時間是否相同.這是我剛剛在服務(wù)器上運行的示例:
Just to make sure that you're not taking client rendering time into the equation, assign the result to a variable and see if the execution time is the same. Here's an example I just ran on my server:
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
這是結(jié)果(查看執(zhí)行時間):
And here are the results (looking at the execution times):
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 毫秒，而將其轉(zhuǎn)儲到我的屏幕需要 884 毫秒.這是一個很大的不同！
Putting it in a variable took 34+2=36 ms whereas dumping it to my screen took 884. That's quite a difference!
這篇關(guān)于SELECT FOR XML 查詢速度慢嗎?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！