table data store.

Question

Post reply

table data store.

- Win.

SSCertifiable

Points: 6790
More actions
February 8, 2010 at 10:44 pm

#141098

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?
Cheers,
- Win.
" Have a great day "

Viewing 13 posts - 1 through 12 (of 12 total)

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

dmoldovan SSCertifiable Points: 6334 More actions · Answer 1

See if this helps:

http://research.microsoft.com/apps/pubs/default.aspx?id=64525

- Win. SSCertifiable Points: 6790 More actions · Answer 2

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.

Cheers,
- Win.

" Have a great day "

dmoldovan SSCertifiable Points: 6334 More actions · Answer 3

Do you store that column as varchar(MAX), or as XML data type?

- Win. SSCertifiable Points: 6790 More actions · Answer 4

Hi,

We store in XML datatype. Any ideas and suggestions.

Cheers,
- Win.

" Have a great day "

- Win. SSCertifiable Points: 6790 More actions · Answer 5

Please provide your thoughts and suggestions.

Fast help is appreciated.

Cheers,
- Win.

" Have a great day "

- Win. SSCertifiable Points: 6790 More actions · Answer 6

Any faced the situation. ?

Any thoughts..

Cheers,
- Win.

" Have a great day "

jshailendra SSCrazy Points: 2260 More actions · Answer 7

I think you should Move the data into the file system and have reference to it in the DB

dmoldovan SSCertifiable Points: 6334 More actions · Answer 8

Do you index your XML data?

See if this helps

http://msdn.microsoft.com/en-us/library/ms345118%28SQL.90%29.aspx

- Win. SSCertifiable Points: 6790 More actions · Answer 9

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.

Cheers,
- Win.

" Have a great day "

- Win. SSCertifiable Points: 6790 More actions · Answer 10

suggestions required.

Please post your comments.

Cheers,
- Win.

" Have a great day "

SQL* SSCertifiable Points: 6638 More actions · Answer 11

hi,

use the sp_makewebtask , do google on this u can solve.:-)

🙂

- Win. SSCertifiable Points: 6790 More actions · Answer 12

Hope so, i would have googled correctly...

Still trying to...

How ever thanks for the reply. Any one who can suggest me on this..?

Cheers,
- Win.

" Have a great day "