Whether ’tis nobler in the CPU to suffer the memory consumption and poor performance of outrageous XMLDOMs, Or to take arms against a sea of XML shredding, and by examining them, remove them.
OK, that’s not quite how that goes, but you get the idea. I’m sure most of you deal with XML in some form or fashion and Microsoft has made it easy to deal with and shread XML. Our web services inhale XML, our databases shred it and then our web services exhale XML. But wait, you said "our databases shread it". Yes I did. Many places feel that since their data is already in XML it’s more performant to keep it in XML and only shread it as needed; which is subsequently in the database. So is it really more performant? Some will disagree but the real answer is no. SQL Server 2005 will deal with XML easily and without complaint, but there is no free lunch here! You’re building overhead into your process by relying on the dB to do your shreading. Sure you can do it, but the XML DOM is not going to be as fast (yet) as the query engine.
In one midsized app, looking over some performance analysis revealed that 85% of the time from request to response was IN THE DATABASE! That’s not even near sane. Any complex processing should be in your tiers. That gives you an easy path to scale. Databases perform best with simple data types. You could index on an element in an XML column, but why in Gate’s name would you???? You could keep everything in BLOBs too, but why would you?
So, come on people. Think! There are times when you have no choice but to deal with XML in the database. But for heaven’s sake, if it is a high maintenance column or you have to do more than just store and retrieve it, think of an alternative! Don’t kill the entire performance of your system just because you think you can move all your code from the middle tier into the database.
OK, I know I’m going to get flamed and I don’t know everything. So if you have a high TPS system shreading, storing and maintaining XML the database, I’d love to hear about it.
John Howell 