June 25, 2007 at 12:11 pm
We are in the process of redesigning out entire site.
We would like to go with xml instead of detail tables, but I wanted to find out the performance hit involved with that.
I read that the XML datatype is really a blob, are there any potential issues that could hurt us down the road?
Thanks in advance.
Susan
June 26, 2007 at 2:45 am
I have not used XML in earnest but yes, I cannot see it as being the most performant thing out. Not in any way comparable to relational tables.
The main reason would be that it would have to deal with each row individually and not as a set. As mnost know, SQL is set based and not row based. Any row based operations run like a dog.
There is then the fact that it is stored off page and the disk IO would be very high.
I would stay away from creating a "details" table based on XML.
Do some tests though and let us know the results... Only way to know for sure (unless there is someone else who has felt the pain)
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 26, 2007 at 6:30 am
Could you add some background about why you're considering XML for the details? Might help us give you a better answer.
June 26, 2007 at 6:47 am
The short answer is that XML is going to be less-efficient for MSSQL than tables will be. The long answer is the obvious "it depends" answer that you always get. The new XML data type is pretty nice. When using strong typed XML, SQL stores it internally in (you guessed it) tables. It is technically BLOB data (VARCHAR(MAX)), but when it is typed, SQL indexes it so it can be accessed through XML queries in a reasonable manner.
It is, however, best practice to use tables in your database when appropriate. There are cases where XML makes a lot of sense - when you have multi-value fields of varying data types is a great example. Yes there are good ways to do this in a relational database, but XML is pretty well suited for it. It is not good practice to use XML to represent a 1 to many relationship that could just as easily be done with two or more tables. If you do this, you will find your application performance degrades quickly and you will make it very difficult for the standard reporting tools to access your data.
If you are planning on doing any reporting or analysis of the information, I would recommend using regular tables. If you have some complicated, varying data that you simply need to store for awhile and redisplay back to the user when they come back, XML is not always a horrible option. I would tend to think of it as an option that you have in your pocket if you really need it, but want to avoid in most cases.
June 29, 2007 at 4:37 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply