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


Reducing Round Trips Part 4


Reducing Round Trips Part 4

Author
Message
Andy Warren
Andy Warren
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: Moderators
Points: 24439 Visits: 2746
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

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
cooper
cooper
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 1
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



Andy Warren
Andy Warren
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: Moderators
Points: 24439 Visits: 2746
I probably should have included them, I may do a follow up that discusses them in this context. Thanks for the feedback!


Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Antares686
Antares686
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: Moderators
Points: 25654 Visits: 785
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)



dogeth
dogeth
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 1

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
DCPeterson
DCPeterson
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3361 Visits: 432

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



*****************/
Jonathan Tomkins
Jonathan Tomkins
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 29

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





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search