SQL Server XML Performance Tips

When using the FOR XML clause in your Transact-SQL applications, don’t include the XMLDATA option. The XMLDATA option returns additional XML schema data that generally is not needed. Because of this, using this option adds extra overhead to your server and network connection, reducing performance. [2000, 2005, 2008] Updated 1-29-2009

*****

The OPENXML function in SQL Server can be useful for processing multiple table inserts within a single database call, reducing overhead and boosting performance. The ability to map an XML document to a rowset representation of a specified portion of the XML document within a stored procedure can maximize the efficiency with which repetitive type inserts are accomplished. [2000, 2005, 2008] Updated 1-29-2009

*****

As you may know, it can be a performance drag on SQL Server and your web server if you SELECT the same data over and over from SQL Server in order to dynamically create Web pages. For example, say that you need to display some text on your web page that is stored in SQL Server, but that it only changes every couple of days. Currently, you are running a SELECT statement from a stored procedure to retrieve the text each time the page is dynamically created and displayed. This can significantly hurt performance, especially if you are getting many page views a second.

One way to help avoid this problem, and to boost SQL Server and your web server’s performance, is to use SQL Server and XML to periodically re-create static content (such as once an hour, once a day, etc.) instead of dynamically pulling the content each time it is needed from SQL Server, and storing this static content on the web server. This assumes, of course, that the data doesn’t change often, as in our example. This method still allows the page to be created dynamically, but when the text is needed to be inserted into the page, it is retrieved locally off the web server, not from SQL Server each time it is needed, reducing overhead and boosting performance. [2000, 2005, 2008] Updated 1-29-2009

*****

SQL Server offers four types of FOR XML queries. They include RAW, AUTO, PATH and EXPLICIT. Each has performance pros and cons.

The RAW type offers the best overall performance, especially if you will be moving a lot of data. The disadvantage of this is that not all XML-based applications are able to use the format the RAW type returns.

The AUTO type offers the next best overall performance, and many more XML-based applications are able to use the format returned, unless of course your application requires XML data in a predefined format.

If your application has to accept data in a predefined format, then you have to use the EXPLICIT type, which is generally the slowest performing option. [2000, 2005, 2008] Updated 1-29-2009

*****

If you need to use the EXPLICIT type of FOR XML query, in some cases you can boost performance if you replace the EXPLICIT FOR XML query with an XPath query instead. XPath queries are faster, and in most, but not all cases, can replace the functionality of a EXPLICIT FOR XML query. [2000, 2005, 2008] Updated 1-29-2009

*****

Continues…

Leave a comment

Your email address will not be published.