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:

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.