Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reducing Round Trips Part 4 Expand / Collapse
Author
Message
Posted Sunday, April 14, 2002 12:00 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 9:01 AM
Points: 6,705, Visits: 1,680
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
Post #3578
Posted Tuesday, April 23, 2002 2:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 03, 2003 12:00 AM
Points: 2, 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




Post #31714
Posted Tuesday, April 23, 2002 5:28 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 9:01 AM
Points: 6,705, Visits: 1,680
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
Post #31715
Posted Tuesday, April 23, 2002 5:33 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Yesterday @ 3:54 PM
Points: 8,369, Visits: 733
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)



Post #31716
Posted Thursday, April 22, 2004 11:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 07, 2007 11:26 PM
Points: 146, 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
Post #112691
Posted Friday, April 23, 2004 3:19 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:40 AM
Points: 1,035, Visits: 408

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



*****************/
Post #112877
Posted Tuesday, April 26, 2005 3:04 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, December 20, 2012 12:35 AM
Points: 88, 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

 

 




Post #177373
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse