XML AUTO function vs. T-SQL commands
As a DBA, I tend to look at performance issues and therefore would like to make sure that performance will not be affected by the use of XML (Extensible Markup Language). In this first article of a series, I'll show performance implications by comparing the XML AUTO function with the standard T-SQL commands. In my testing process I will cover only a small and basic portion of the XML AUTO functionality. It is always recommended you test the XML functions that you intend to use, in your own environment.
Testing environment description
For my testing, I've created a table containing 10,000 rows, as described below:
[IMAGE]
The table contains rows as follows:
select * from Employes
[IMAGE]
FOR XML Auto with large amounts of data
I used the following queries on my table, starting with a standard T-SQL query and then adding more XML structures to the comm...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
and each time:
SELECT * FROM Employes
Go
SELECT * FROM Employes FOR XML AUTO
go
SELECT * FROM Employes FOR XML AUTO, TYPE
go
SELECT * FROM Employes FOR XML AUTO, TYPE, ELEMENTS
go
SELECT * FROM Employes FOR XML AUTO, TYPE, ELEMENTS,
ROOT
go
I monitored these commands in SQL Profiler.
I figured out that the Duration column in the SQL Profiler is not accurate. The executed XML command was already shown in the Profiler, while the Query Analyzer still processed the results. So I will ignore this column, and look at the other resources: CPU and I/O. Here are the monitoring results showing three executions of the five commands:
[IMAGE]
It seem to be consistent that the T-SQL query is performing better than the rest. The XML query FOR XML AUTO is using more than eight times more CPU but the same amount of I/O. The complex XML commands are issuing more than 80 times (!) more reads than the T-SQL command, also many writes and above six times the CPU.
When analyzing the Statistics I/O issued by the commands:
It is obvious the more complex XML commands are creating a Worktable. It also shows that every command is doing more work than the previous one.
FOR XML Auto with smaller amounts of data
I will do the same test using the same table, but reading less data.
First, I will create an index on the ID column:
>
create unique clustered index UQ_Employes on Employes (id)
Then I will query my table with a WHERE clause, querying 100 rows and then 1000 rows.
100 rows:
SELECT * FROM Employes where id between 5000 and
5100
go
SELECT * FROM Employes where id between 5000 and
5100
FOR XML AUTO
go
SELECT * FROM Employes where id between 5000 and
5100
FOR XML AUTO, TYPE
go
SELECT * FROM Employes where id between 5000 and
5100
FOR XML AUTO, TYPE, ELEMENTS
go
SELECT * FROM Employes where id between 5000 and
5100
FOR XML AUTO, TYPE, ELEMENTS, ROOT
go
SQL Profiler shows no real difference between the commands:
[IMAGE]
Also, no worktable is created and all the commands have the same amount of work:
Execution plan also shows same amount of work:
[IMAGE]
1000 rows:
SELECT * FROM Employes where id between 5000 and
6000
go
SELECT * FROM Employes where id between 5000 and
6000
FOR XML AUTO
go
SELECT * FROM Employes where id between 5000 and
6000
FOR XML AUTO, TYPE
go
SELECT * FROM Employes where id between 5000 and
6000
FOR XML AUTO, TYPE, ELEMENTS
go
SELECT * FROM Employes where id between 5000 and
6000
FOR XML AUTO, TYPE, ELEMENTS, ROOT
go
SQL Profiler:
[IMAGE]
Statistics I/O:
Execution plan:
[IMAGE]
Using a Worktable – when?
Where is the limit for the optimizer to use a Worktable?
It depends on the amount of optimizer work related to the memory and data size that needs to be used for the XML parsing. The returned XML in my queries is stored in one large XML variable in the memory. The above limit is not a definite number. In the SQL Server Developer Center it says: "Variables and parameters of type XML can be up to 2 GB. They use main memory as storage as long as the values are small. However, large values are stored in tempdb."
Conclusion
XML data and functions may use many more resources than the standard T-SQL. Therefore, if the queries involve large amounts of data or the XML function is more complex, consider using standard T-SQL in the database level, instead. It is always recommended to test your XML performance in order to make sure the usage of XML in the database will not degrade application performance.
Learn about XML in SQL Server 2005
If you are not familiar with the XML functionality in SQL Server 2005, here are some useful links:
ABOUT THE AUTHOR:
Michelle Gutzait works as a senior database consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the last 10 years, she has worked exclusively with SQL Server. Her skills include SQL Server infrastructure design, database design, performance tuning, security, high availability, VLDBs, replication, T-SQL/packages coding, and more.
Copyright 2007 TechTarget
