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 ««123»»

Saving a Sales Order Part 1 Expand / Collapse
Author
Message
Posted Tuesday, March 27, 2007 7:53 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, June 22, 2014 6:53 PM
Points: 967, Visits: 388

I don't think the purpose of this particular use of XML is to make it meaningful.  It is used as a method to transport data. 

For the purposes of this demo, the author takes a clean approach and concisely shows how to accomplish the task.  I would object to taking this approach in the real world, however. 

It is a small point, but worthy of consideration IMHO: if the insert fails in such a way that the sproc aborts, you leave an open XML document with no handle available.  If this happens enough times you will exhaust the "MEMTOLEAVE" area and be forced to restart your SQL server.   I would load the XML into a temporary table, destroy the XML document, and then perform the insert, perhaps after validating the data.

This is a good article and it demonstrates a reasonable use of XML in the database. 

 

 

  

Post #354171
Posted Tuesday, March 27, 2007 8:16 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028
I think the article was clear and very to the point but besides the advantages of using this technique the drawbacks should also be pointed out.

I have had memory issues with openxml in the past even though it seems like a nice tool for the job. MSXML parser is an out-of-process memory intense component and you should be really careful on highly transactional environments.



* Noel
Post #354180
Posted Tuesday, March 27, 2007 8:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523

As I mentioned earlier, the last part of this 4 part article has an error handling example, where the XML handle is released if an error occurs.



.
Post #354181
Posted Tuesday, March 27, 2007 8:28 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, June 22, 2014 6:53 PM
Points: 967, Visits: 388

Hi jacob,

That's what I get for attempting comprehension before the coffee kicks in. 

 

Post #354188
Posted Tuesday, March 27, 2007 8:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523


.
Post #354193
Posted Tuesday, March 27, 2007 10:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 25, 2007 8:35 AM
Points: 3, Visits: 1

Your article is very interesting from the viewpoint of presenting a different way to deal with the classic order entry problem.  However, it would be useful if you would show some timings for the old way vs. the new way.  Specifically:

What is the elapsed time for a single order from a single client?  How does the time degrade as the number of users/orders increases?

What is the cost to the SQL Server for each method (CPU, Duration, Reads, Writes)?  How does that change as the number of transactions increases?

Again, the technique is interesting, but I think you should show how it compares to the "standard" method from a timing and resources point of view.

Post #354255
Posted Tuesday, March 27, 2007 10:42 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523

Hi Paul,

I will definitely collect some performance data and will post it soon.

Jacob



.
Post #354258
Posted Tuesday, March 27, 2007 11:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:07 AM
Points: 2,901, Visits: 1,805
The bit that concerns me is the BOL entry that warns that this takes 1/8th of the server memory. So a 16GB box is going to lose 2GB RAM just for opening a World document.

In SQL2000 I'm pretty sure that the XML parser is MSXML3 where as the most up-to-date version is MSXML6.0.

It's a shame because its an elegant solution let down by the underlying technology.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #354284
Posted Tuesday, March 27, 2007 11:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 4, 2007 1:00 PM
Points: 4, Visits: 1

This article is clean and clear. I hope there is a part two that will shed more light on related topics. This would be very helpful for a DBA greener like me. Thanks.

Amarado

Post #354290
Posted Tuesday, March 27, 2007 12:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 25, 2007 8:35 AM
Points: 3, Visits: 1

Amarado,

I found (by accident) that if you go to the top of this page and click on "Next Topic" it will take you to what appears to be all of the articles in this series.  Look quick before they change it!

Post #354303
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse