Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    128

    Performance tests on parsing XML data vs. a properly designed DB?

    Greetings. Using SQL2K8, SP1.

    I've got a group of developers that want to use an XML data type column to store multiple zip codes, then parse out the column to query specfic zip codes if needed. This would be as opposed to normalizing the DB properly.

    Now I'll be the first to say that at rare times, a proper design should be ditched for what will be faster, but my guess (could be wrong) is that this will slow things down. I'm certain it would slow things down with older data types, but I've honestly never encountered this request with the XML data type.

    Has anyone compared these various types of scenarios, or have any experience that would suggest this idea is either good or bad? I'm open to this way of doing things, provided it won't make my life miserable in the future due to poor performance.

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    You can't index the XML, so parsing it isn't even relevant. Think how long it will take to tablescan instead of index seek when you need to know how many customers are in a given zip code.

    The developers may force you to use XML, but I wouldn't go there voluntarily.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2004
    Posts
    128
    Thanks Pat.

    I will likely attempt to avoid this. However, this arguement is not valid as the XML data type in SQL2K8 does in fact allow indexing. I would still think the parse itself would slow things down, if not disregard the index entirely, but on "the surface" indexes are allowed.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    The XML datatype does in fact allow the creation of indexes, that wasn't my point... When you search a table using a simple column to find out who lives in zip code 90201 you can use an index to find the appropriate row. When you do the same search for an XML column, you must retrieve and parse every row. On a million row table, a simple column takes about 700 mlliseconds. The same serach against XML encoded, multi-valued data would be measured in minutes.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Nov 2004
    Posts
    128
    This was my thinking as well, thanks for confirming!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •