Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Saving a Sales Order Part 1


Saving a Sales Order Part 1

Author
Message
Jeff Gray
Jeff Gray
SSC Eights!
SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)

Group: General Forum Members
Points: 971 Visits: 389

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. 

 

 

  


noeld
noeld
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6318 Visits: 2048
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
jacob sebastian
jacob sebastian
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 2523

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.



.
Jeff Gray
Jeff Gray
SSC Eights!
SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)

Group: General Forum Members
Points: 971 Visits: 389

Hi jacob,

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

 


jacob sebastian
jacob sebastian
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 2523
:-)

.
Paul Lach-293939
Paul Lach-293939
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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.


jacob sebastian
jacob sebastian
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 2523

Hi Paul,

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

Jacob



.
David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3623 Visits: 3110
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
Amara Mustapha
Amara Mustapha
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

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


Paul Lach-293939
Paul Lach-293939
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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!


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