SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Reducing Round Trips Part 4

By Andy Warren,

Over the past couple months I've posted three articles on how to reduce the number of round trips between server and client (Reducing Round Trips,  Reducing Round Trips - Part 2, and  Reducing Round Trips - Part 3). I'm going to finish up the series by looking at how XML provides yet another method of reducing round trips.

Really XML can be applied two ways. The first is to use FOR XML in your select. Just translating a result set to XML doesn't save anything of course, if anything it costs more to build on the server and definitely costs more bytes over the network. But if you're representing a hierarchy, then FOR XML begins to make a lot more sense. After all, what's better at describing a hierarchy? By hierarchy I mean almost any select that has one or more joins, a good example being joining order to order details. Even beyond that there is one other consideration, how is the client going to consume it? If you're sending XML back to a browser and are applying XSL to get the final look, that's killer. I'd say even if you're going to send it back to the client and use the DOM to manipulate it further that probably makes sense. Just don't send it to the client just to convert it back to a recordset! One other option to do pretty much the same thing without leaving ADO is to use a shaped recordset. We probably need more coverage of shaped recordsets here on the site, but for now take a look at Converting Shaped Recordsets into XML and Supplement to 'Converting Shaped Recordsets into XML, both by my good friend Jon Winer.

The other way XML can be used is via OpenXML. OpenXML isn't the easiest thing in the world to use (to me anyway) but I think you might this application worth while. We've discussed using FOR XML to return a hierarchy, but how to send a hierarchy? XML of course! Consider the following bit of XML:

<ORDER CustomerID=5>
    <ORDERDETAIL Quantity=1 ProductID=1707/>
    <ORDERDETAIL Quantity=4 ProductID=9201/>

For this example what I need to do is add one line to orders and add two lines to orderdetails, all wrapped in a transaction of course. To do this using "plain" TSQL I'd probably start the transaction, insert the order row and return the primary key, then execute one insert per order detail, then close the transaction. A "better" implementation might support passing all of the information to one stored proc and we would do basically the same thing server side. The drawback to passing all the information to one proc is that as you add more parameters, the coding in the proc gets uglier (though it's basically cut and paste). Not to mention that if you code to support 10 order details, invariably someone will end up needing 11.

So another way to send that info over in one chunk would be to put it all into a string, something you could parse server side, giving you only parameter you need to pass. The string has to be delimited somehow of course, and the code to pull it back apart isn't really fun to write either (in my opinion), AND it won't even be a set operation. XML gives us a robust method of sending over the order and an unlimited number of details in one parameter. Not only do we not have to reinvent a "file format" and write parsing code, it handles all the little things like the case when our data contains a delimiter. Of greater value is the fact that we can do a couple different selects against the XML and do everything we need to do as a set based operation.

That wraps up this series. I've tried to get you thinking about various techniques you can use in your applications to reduce the number of round trips. Use them when they make sense. Single user utilities can afford to be extravagant with resources, applications with hundreds or thousands of users can bring your network and your SQL server to it's knees if you use the same approach. Put the same amount of time into tuning the round trips as you do into tuning your queries and you'll wind up with a better application every time.

Total article views: 8955 | Views in the last 30 days: 1
Related Articles

Reducing Round Trips

One of the best ways to reduce the load on your server and increase application responsiveness is to...


Reducing Round Trips - Part 3

This week Andy continues his series on how to reduce the number of round trips to the server by look...


Reducing Round Trips - Part 2

Last week Andy started a discussion of the various ways you can reduce the number of round trips to ...


Converting Shaped Recordsets into XML

This article shows you how to take a shaped recordset and convert the results into XML all while pre...


Reducing Round Trips - Working with HTML Checkboxes

Reducing the amount of round trips between a server and client is something that can give you a grea...

performance tuning