Post reply

Revamping a data storage model- would XML be a good idea?

  • February 11, 2011 at 10:29 am

    #1285804

    Are all of those tables defining essentially the same thing? Like all are different versions of name-and-address data, or something like that? Or do they define different things, like one is customers and another is orders and a third is pricing data?

    If they're all the same kind of thing, which is what I'm assuming from your description, then sparse columns will perform MUCH better than XML storage. I've used both, and they aren't even close to being comparable. XML also takes much more drive space, takes more bandwidth to pass to other servers/clients, etc. It's basically for infrequently accessed semi-structured data.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • February 11, 2011 at 10:43 am

    #1285819

    It's scientific data, so mostly things like p-values, and occasionally a 1-5 character string. Problem is that not all of the values originate from the same statistical workflow, which wasn't documented, so the end result is that data for each experiment was stored in a different table.

    Now that I think about it, since it is mostly just small floats and really short strings, thats some ginormous overhead for representing it in XML. Also having worked with it for a couple days now, it seems like a great set of features, but it's best applied in a small subset of applications. I guess I'll look into the sparse option again. It would be much easier to rewrite the existing queries then, without having to get CROSS APPLY in there, etc.

    Thanks

  • February 11, 2011 at 10:57 am

    #1285830

    That's pretty much what sparse columns were designed for. Should work out well. Or at least better than EAV or XML.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply