|
|
|
SSC-Enthusiastic
 
Group: General Forum Members
Last Login: 2 days ago @ 4:15 AM
Points: 152,
Visits: 441
|
|
A query on table design:
We have a table in which a blob of data (xml file date) is stored which also included other columns with the keys extracted from the xml data. The size of the table has drastically grown with around 3 million rows and around 10GB of data. The queries on the table or purging the data or indexing the table itself is taking load of time and space.
Is there a better way to store the blob of data? Are there industry best practices?
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Yesterday @ 3:29 AM
Points: 258,
Visits: 820
|
|
See if this helps:
http://research.microsoft.com/apps/pubs/default.aspx?id=64525
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: 2 days ago @ 4:15 AM
Points: 152,
Visits: 441
|
|
Thanks for the reply posted. I have some questions in my mind and need some clarifications.
I appreciated if someone points to me to better direction.
The size of the table is increasing as the xml data is big which we can't help. But the question is:
1. Is it correct to store the xml data in the same table?
2. Do we have to move the data into some other table and use the reference?
3. Move the data into the file system and have reference to it in the DB?
4. or any other better solution?
Thanks,
-Win.
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Yesterday @ 3:29 AM
Points: 258,
Visits: 820
|
|
| Do you store that column as varchar(MAX), or as XML data type?
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: 2 days ago @ 4:15 AM
Points: 152,
Visits: 441
|
|
Hi,
We store in XML datatype. Any ideas and suggestions.
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: 2 days ago @ 4:15 AM
Points: 152,
Visits: 441
|
|
Please provide your thoughts and suggestions.
Fast help is appreciated.
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: 2 days ago @ 4:15 AM
Points: 152,
Visits: 441
|
|
Any faced the situation. ?
Any thoughts..
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Thursday, March 18, 2010 7:32 PM
Points: 249,
Visits: 244
|
|
I think you should Move the data into the file system and have reference to it in the DB
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Yesterday @ 3:29 AM
Points: 258,
Visits: 820
|
|
Do you index your XML data?
See if this helps
http://msdn.microsoft.com/en-us/library/ms345118%28SQL.90%29.aspx
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: 2 days ago @ 4:15 AM
Points: 152,
Visits: 441
|
|
I think you should Move the data into the file system and have reference to it in the DB
----------------------------------------------------------------------------------------------------
Thanks for the reply. This is what am looking for. Could you please post how can we do that successfully for the existing Prod DB.
Any suggestions are appreciated..........
How to store the XML files into file system and refer the same to DB....? help required.... as i have not done so far....
Thanks in Advance...
- Win.
|
|
|
|
