SQLServerCentral / Discuss Content Posted by Andy Warren / Article Discussions / Article Discussions by Author / Reducing Round Trips Part 4 / Latest Posts

RE: Reducing Round Trips Part 4

Jonathan Tomkins — Tue, 26 Apr 2005 03:04:00 GMT

We used OpenXML in some components for a (small/mid sized?? - maybe 35K hits/day) web site generation app to pass small xml docs to some stored procs to vary the behaviour of what was being returned - one particular case replaced several recursive hits from the app to get out some hierarchical info in different ways (get children, get ancestors, get decendents, get grandchildren etc) - the queries were still quick and sped things up considerably in that scenario as well as providing us with some really flexible procs that we could reuse a lot ( less work! ).

I also tried using it for multiple insert/updates (throw an xml doc to the db, load into declared table and do joins/let it decide whether to do inserts or updates - I don't mean bulk bulk updates to the db here since I'm sure there's better ways to handle that, but editing multiple records in the app and passing it all along at once seemed quite nice since you didn't have to really worry about whether you were inserting or updating - just send the info. This was only a small app though, so if anyone has done anything bigger like that I'd be interested to know how it went.

Jon

RE: Reducing Round Trips Part 4

DCPeterson — Fri, 23 Apr 2004 15:19:00 GMT

We started developing a system along these lines of thought about a year ago, and despite my dislike of XML I initially thought that it might actually work?!?!?!

Sure enough it worked beautifully in a limited testing environment. Then reality of production loads hit us straight between the eyes!

Due to the bloated nature of XML the cost of sending and parsing the XML messages FAR outweighed the cost of a simple ADO call to the database, particularly if that call is to a well thought out stored procedure. It may be that in cases where you would have to do multiple INSERTS/UPDATES etc... the XML option might be a break-even proposition, but overall it was a dog and killed our limited network bandwidth to our remote branches.

Reducing round trips is all well and good, but if you replace 10 round trips with a single, inefficient round trip you are not buying yourself anything. You might be giving up the very efficiency you are after in exchange for nothing more than the latest industry fad.

RE: Reducing Round Trips Part 4

dogeth — Thu, 22 Apr 2004 23:19:00 GMT

I've used this method for a couple of years now and have found it very effective.Another advantage of this method is that it is possible to move the transactional boundary of a complicated update into the data layer, instead of having it in the application layer.

A disadvantage of this method is the extra work your SQL Server needs to do to parse XML and also dispose of the XML object after finishing with it. This is something that I haven't done a lot of testing with, but it could effect your scalability of your application.

RE: Reducing Round Trips Part 4

Antares686 — Tue, 23 Apr 2002 05:33:00 GMT

Nice ad simple, I have always been a proponent for XML and using to reduce access needs to the server."Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

RE: Reducing Round Trips Part 4

Andy Warren — Tue, 23 Apr 2002 05:28:00 GMT

I probably should have included them, I may do a follow up that discusses them in this context. Thanks for the feedback!Andy

RE: Reducing Round Trips Part 4

cooper — Tue, 23 Apr 2002 02:48:00 GMT

Take a look at updategrams which are introduced in Web Release 2 for SQL server.It offeres a standard method to intepret an XML document as series of data manipultion commands (insert,update,delete). It also supports aitonumber columns

Reducing Round Trips Part 4

Andy Warren — Sun, 14 Apr 2002 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/reducingroundtripspart4.asp>http://www.sqlservercentral.com/columnists/awarren/reducingroundtripspart4.asp