Pros and Cons of SQLXML

Roger Jennings and a reader acknowledge that using the SQLXML interface to provide HTTP access to SQL Server is not appropriate for high-performance enterprise solutions.

• By Readers of Visual Studio Magazine
• 04/01/2004

Letters to Visual Studio Magazine are welcome. Letters must include your name, address, and daytime phone number to be considered for publication. Letters might be edited for form, fit, and style. Please send them to Letters to the Editor, c/o Visual Studio Magazine, 2600 El Camino Real, Suite 300, San Mateo, CA 94403; fax them to 650-570-6307; or e-mail them to [email protected].

Pros and Cons of SQLXML
Thanks to Roger Jennings for a great article about the performance implications of various SQL Server access methods ["Optimize SQL Server Data Access," November 2003]. However, I'd like to point out a couple areas that I think he missed.

First, the option of using the SQLXML interface to provide HTTP access to SQL Server should not even be an option for secure, high-performance enterprise solutions. It's a nice feature for small shops and for testing things out, but I would never recommend it as part of a real solution.

Second, Mr. Jennings' conclusion that using a stored procedure for multirow inserts/updates performs more poorly than batched T-SQL statements is somewhat flawed, especially in the context of SQL Server 2000. The reason for the conclusion was that SQL Server has no facility to pass in an array of values as a parameter to a stored procedure, and therefore requires multiple stored procedure calls for multirow inserts/updates. In my opinion, arrays are not the solution to set-based operations, as they require a rather non-RDMS, row-by-row processing approach. Why wait for arrays in Yukon when SQL Server 2000 has the features to handle multirow parameters to stored procedures that are way better than arrays? (There are more exciting things about Yukon that I'm waiting for, such as being able to write stored procedures in C#.)

SQL Server has the ability not just to spit out XML (with the FOR XML clause), but to process in-coming XML documents, which I find to be even more powerful and useful than generating XML output. I've used this feature since I first got my hands on the SQL Server 2000 beta, and it has worked well for me in production on both client/server applications and high-volume Web sites.

Here's a typical way in which this method can be used to provide super-high-performance, multirow inserts and updates. The set of data rows in the client app (Web site, business-tier component, desktop UI, and so on) is packaged as an XML document in a string variable. This XML string is then passed as a parameter to a stored procedure. The stored procedure then uses the OpenXML() construct to view the XML document as a native SQL Server rowset (virtual table). I typically use this pseudo-coded approach to perform inserts and updates in a set-based manner, rather than process the rows one by one:

Insert into <table> select 
<values> from OpenXML(<xml doc 
handle>) <alias> where <id 
column from xml document is blank>
Update <table> set <column> 
= <xml document column> [, ...] 
from <table> join OpenXML(<xml 
doc handle>) <alias> on 
<table>.id = <alias>.id

This simple approach properly uses the facilities available now within SQL Server to handle multirow input to stored procedures, requires only one round trip to the client, has all the performance benefits of stored procedures and more, and performs multirow operations in the set-based manner that the relational model was designed for. I've heard so much chatter about SQL Server's FOR XML clause over the years, but little about its amazingly simple but powerful XML document processing abilities. What makes it even better is that it's a great fit with the great XML features of .NET. Want to move an entire, multitable DataSet to SQL Server for processing? Serialize it to an XML string, pass it into a stored procedure, and have at it with OpenXML.

David Alexis, Palisades Park, N.J.

I based my "Optimize SQL Server Data Access" article on Microsoft's "Designing Data Tier Components and Passing Data Through Tiers" white paper, which stresses use of basic ADO.NET features, such as parameterized stored procedures and T-SQL statements for data access layer components (DALCs). I added data retrieval by SQLXML 3.0 stored procedures with an XML Web services wrapper as a potential alternative to conventional T-SQL SELECT stored procedures.

I agree with Mr. Alexis that XML Web services generated by SQLXML 3.0 aren't well suited for "secure, high-performance enterprise solutions." This approach was presented solely for the purpose of comparing its performance with more traditional methods, and not as a recommendation. In fact, I recommended that readers avoid use of SQLXML 3.0 DiffGrams because of poor performance. I should have mentioned authentication and authorization issues as additional drawbacks.

I also agree that use of OpenXML is an elegant method for performing updates and inserts from XML documents passed as strings to a stored procedure. However, this approach seems to me to defeat the objective of a DALC layer between the business entity (BE) and database back end. In this case, the string passed to OpenXML would be a serialized BE object. Any change to the structure of the BE object would require altering the OpenXML stored procedure code. You could, of course, use XSLT within a DALC to transform the BE document as necessary, but this process might entail a performance hit. —R.J.

Kudos From a Satisfied Customer
I'm fairly new to the development community, and your magazine has given me a large amount of insight. I enjoy reading the articles on my train ride into and out of the office daily (sometimes reading them several times). I'm so hooked on learning more that I find myself checking the mail regularly for the newest addition and walking away dejected when it's not there.

My company is going to .NET shortly to replace a completely lacking system designed mainly in VBA years ago. Each month I find another article that has some pertinence to what we're doing or where we would like to go. Keep up the great work—it's helping one real-world developer!

I will admit at times I do put down VSM for a little bit of "This Old House." Now if only I could bring a piece of 2-by-4 to the office and pound a nail into it as a stress reliever, I would have the best of both construction worlds.

David A. Jacobus, Philadelphia