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

Gain Space Using XML data type Expand / Collapse
Author
Message
Posted Tuesday, August 4, 2009 8:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 10:36 AM
Points: 31,362, Visits: 15,823
It's an interesting look from a developer point of view. Compared to the power and speed of the relational engine, I think space savings are a minimal thing to worry about. Disk prices fall at a rate far beyond that of the improvements in CPU and memory when you compare price/performance.

I agree with many of the comments, that a well designed relational system is probably better, but this is an interesting solution and idea. If disk space matters a lot, perhaps it's worth considering, but I would recommend that you investigate some of the other suggestions first.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #764841
Posted Tuesday, August 4, 2009 8:37 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, December 15, 2014 9:36 AM
Points: 652, Visits: 1,901
I think the merit in this article lies more in the techniques that are covered, rather than in the premise that it ultimately failed to prove.

The article does show a couple of good xquery examples in context - how to select a value from a node and incorporate it in a resultset, and how to modify the value of a node.

SELECT Rainfall.value('(/RT/RN[@H=15]/@H)[1]','tinyint') AS Hour
,Rainfall.value('(/RT/RN[@H=15]/@V)[1]','numeric(10,8)') AS Rainfall

UPDATE TP SET Rainfall.modify('replace value of (/RT/RN[@H="20"]/@V)[1] with "8.12345678" ')

All in all, not a bad article, well written and clear explanations, even if in some respects fundamentally flawed!
Post #764862
Posted Tuesday, August 4, 2009 3:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 35,770, Visits: 32,436
Paul White (8/4/2009)
This script is closely based on that in the article.

I would strongly encourage the author of this article to try it out

Nice idea for an article, but size and speed are not XML strengths!

Paul


MythBuster extraordinaire... that script goes into my "Pork Chop Hall of Fame". Nice job, Paul.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #765157
Posted Tuesday, August 4, 2009 3:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 35,770, Visits: 32,436
Steve Jones - Editor (8/4/2009)
It's an interesting look from a developer point of view. Compared to the power and speed of the relational engine, I think space savings are a minimal thing to worry about.


Space savings are certainly important to me especially when it comes to backups and restores but according to Paul's script... what space savings?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #765158
Posted Tuesday, August 4, 2009 4:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 10:36 AM
Points: 31,362, Visits: 15,823
Didn't test it, and perhaps there are none. In any case, I think it's been a nice debate/discussion of the technique.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #765174
Posted Tuesday, August 4, 2009 7:04 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:47 AM
Points: 9,928, Visits: 11,205
Steve Jones - Editor (8/4/2009)
Didn't test it, and perhaps there are none. In any case, I think it's been a nice debate/discussion of the technique.

Hey Steve!

CTRL+C, CTRL+V, F5

(The test results from a run on my machine are also embedded in the comments).

Seriously though, XML is cool an' all - and I have been known to dabble some with XQuery, XPath and the rest, but it is easy to get carried away with it and apply XML to everything.

@G2: Good point. Sparse columns have some advantages in 2K8 that make them well worth a look too. The column_set column that gives you formatted XML based on just the columns with values in is very cool.

@Jeff: Thanks so much - have you noticed how many of my scripts have at least a passing resemblance to some you have posted in the past? Not entirely co-incidental




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #765191
Posted Tuesday, August 4, 2009 8:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 35,770, Visits: 32,436
Paul White (8/4/2009)
@Jeff: Thanks so much - have you noticed how many of my scripts have at least a passing resemblance to some you have posted in the past? Not entirely co-incidental


Heh... oboy have I noticed. Thank you for the high compliment. The embedded comments are the berries, too! If someone can't figure out what you've done by looking at your code, it's because they simply can't read. Nicely done.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #765206
Posted Wednesday, August 5, 2009 4:52 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 11:09 PM
Points: 162, Visits: 263
Thanks to all for the comments and suggestions. Really appreciate them.

Paul - Thanks for the script. I shall go through it during the weekend and let you know.
Post #765330
Posted Wednesday, August 5, 2009 5:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 22, 2014 6:20 AM
Points: 2, Visits: 18
The application of XML in an OLTP system is not very scalable. The goal of any OLTP system is to increate the TPS. XML is not built for speed. Also consider that most OLTP sytems are highly normalized. Good luck joining from XML columns out to other tables and retaining your performance.

I see XML fields more applicable in static data scenarios. Also don't forget that SQL Server provides primary and secondary XML index functionality. This can be used to increase query performance over your XML, but great care is needed in designing your XML schema.

Steve
Post #765344
Posted Wednesday, August 5, 2009 7:41 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 1:47 PM
Points: 478, Visits: 1,427
Why bother with the database at all then? Just store the XML directly on the disk?

Seriously though, in today's day and age physical space is not usually an issue.
And, if you are going to be querying the data often, you will get better performance from a relational model.

The example with Candidates and Tests hit close to home.
We have a similar scenario and the original decision (made years ago) was to store the data as XML, for some of the reasons mentioned in the article.
But -- guess what? -- querying the data became slower and slower...
Eventually we had to write an APP that breaks the XML back into relational tables.
Post #765444
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse