USEFUL SITES :
Write for Us
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] Updated 1-10-2006
*****
The OPENXML function in SQL Server 2000 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] See related article Updated 1-10-2006
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. Click here to see an article on how to do this. [2000, 2005] Updated 1-10-2006
SQL Server 2000 offers three types of FOR XML queries. They include RAW, AUTO, and EXPLICIT. Each have 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] Updated 1-10-2006
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] Updated 1-10-2005
If you have installed SQLXML 2.0 on your SQL Server 2000 server, you can take advantage of a new feature that can significantly affect SQL Server's performance. SQLXML 2.0 supports what is called the Client Side for XML. What this feature does is to move the conversion from the SQL rowsets to XML at the client, or middle-tier, instead of on the SQL Server. This can significantly reduce the load on SQL Server, helping to boost its performance. Of course, the conversion still has to be done somewhere, and resources on the client or middle-tier must be used to do this. This performance-enhancing feature is especially useful to those whose SQL Server is already operating at full capacity and the workload need to be off-loaded to other servers. [2000] Added 5-9-2002
To take full advantage of SQL Server 2000's XML capabilities, you will need to download the SQLXML 2.0 (XML for SQL Server 2000) from Microsoft's website and install in on your current SQL Server 2000 server(s). This is because SQL Server 2000's native XML support is limited. By installing SQLXML 2.0, you get these new XML features:
[2000] Added 5-9-2002
Next Page>>
