May 26, 2010 at 2:05 pm
I have a table say my table with 200,000+ rows. It has couple of fields defined with datatype XML say fieldxml
When i run a SQL query
select field1, fieldxml, filed2 from mytable(nolock)
it takes 15 min to return the results.
When I take out the xml field out the query as follows
select field1, filed2 from mytable(nolock)
it takes few seconds. I know this is because of the fieldxml which has the huge xml data.
My question is:
1. Is there a faster way to get the XMLs? I have worked with Oracle,DB2 which store the xml data as blob and have seen better perfomance than SQL server when dealing with XMLs. Obviously there is something I am missing that is causing the slow response. Can you tell how to address the issue?
May 26, 2010 at 4:34 pm
As usual, "it depends".
If you have large XML data stored in that column it'll take a while to get the data due to the sheer volume.
The question would be: What do you want to do with the xml data? Do you want o shred it and query based on the shredded result? If so, you should look into XQuery to shred the data.
But if you want to select some rows based on a certain WHERE condition and then work with the XML data you might be better off to use a subquery to reduce the amount of rows to use for shredding the xml.
It would help a lot if you could post some (fake) sample data together with your expected result. We might be able to help you getting a better performing query. Doing a SELECT without a WHERE condition on a 200K rows table holding XML data doesn't seem to be a valid business case... Maybe over-simplified the problem?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply