Reducing Round Trips Part 4
-
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
-
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
-
I probably should have included them, I may do a follow up that discusses them in this context. Thanks for the feedback!
Andy
-
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)
-
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.
Cheers,
Kevin -
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.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
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.Ta
Jon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply