Geek asks and answers

Are SELECT FOR XML queries slow?

I have a stored procedure that returns XML to the caller using the SELECT FOR XML PATH statement. Since more rows were added to the main table in the query, I noticed that the performance of this query has deteriorated.

During the investigation, I found that executing a query in the SQL management studio without a FOR XML statement takes 1/3 of the time when it receives a FOR XML query. Whether the generation of XML caused by FOR XML is a significant part of the overhead, or there are some that do and do not use when using FOR XML.

Below is my table definition and the query used, which returns> 3000 rows. Column names have been changed to protect the innocent.

Any advice is appreciated.

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)

Query:

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
+3
2

, , , . , :

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. !

+2

, - . SQL , . XML . CLR SQL Server, .

, ; , , . ( 3k) XML? , ?

+1

More articles:


All Articles