How to manage huge table that contains xml

Question

Post reply

How to manage huge table that contains xml

ManoharV

SSCommitted

Points: 1730
More actions
April 14, 2009 at 3:08 am

#208321

HI.. I my database there is a table which stores xml related data...
If I simply put a select query for that, it takes minutes to retrieve all records..
I am using that table frequently... Is there anyway to retrieve it quickly.. Will table partition help in this case...?

Viewing 10 posts - 1 through 9 (of 9 total)

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

Put a clustered index on the table and the IDENTITY column.

Then create index over XML column.

N 56°04'39.16"
E 12°55'05.25"

noeld SSC Guru Points: 96590 More actions · Answer 2

Also retrieving "all" records is a bad design. You should think about retrieving "some" of them.

* Noel

maechismo_8514 SSCoach Points: 17510 More actions · Answer 3

manohar (4/14/2009)
If I simply put a select query for that, it takes minutes to retrieve all records..
I am using that table frequently... Is there anyway to retrieve it quickly.. Will table partition help in this case...?

No, before thinking of partitioning tables, as suggested above you should go for creating indexes. Clustered/non-clustered depends on how you query the table. But as a thumb of rule most of the tables should have one clustered index and then a non- clustered index to cover all your queries.

ManoharV SSCommitted Points: 1730 More actions · Answer 4

As per the table structure there are only two columns

QuestionID BIGINT (Primary key)

QuestionXML NTEXT

Will it be a problem if the datatype is NTEXT...

And also we wil store XML which has size over 5 kb...

Is it feasible to have index to such column...? And also table contains over 1 lakh records...

Thanks

Manohar

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

Yes, NTEXT will be a problem.

If you are storing XML fragments, use XML datatype!

N 56°04'39.16"
E 12°55'05.25"

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 6

manohar (4/14/2009)
As per the table structure there are only two columns
QuestionID BIGINT (Primary key)
QuestionXML NTEXT
Will it be a problem if the datatype is NTEXT...
And also we wil store XML which has size over 5 kb...
Is it feasible to have index to such column...? And also table contains over 1 lakh records...
Thanks
Manohar

- Avoid the use of "old" datatypes (n)text, image, ... with sql2005 / 2008.

- use the correct datatype ! In this case : use the XML datatype.

-- This way:

--- - content is checked for correct XML layout

--- - content can even be xml schema bound.

--- - column size up to 2GB

--- - you can even create XML indexes to support your XML driven queries.

Check books online for "Understanding XML in SQL Server "

Also, large columns may get shifted off row (like text datatype), so may require an extra IO !

I must check if XML columns start off row, like text columns do, or not.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

ChiragNS One Orange Chip Points: 26137 More actions · Answer 7

I have tables with xml columns and having lot of rows. As long as i retrieve only a few rows (as pointed out earlier) i dont have problems.

"Keep Trying"

ManoharV SSCommitted Points: 1730 More actions · Answer 8

ManoharV

SSCommitted

Points: 1730

April 15, 2009 at 2:35 am

#977905

Thank you guys.....

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 9

XML columns start "in row" ...

My little test: ( never mind the xml content 😉 )

-- my test database

Create database SSC_Test

go

use SSC_Test

go

Create table T_SSC (

IdNo int identity(1,1) not null primary key -- will be the clustering index -> physical order of rows chained in the table storage

, TsCreate datetime not null default getdate()

, TheXML XML not null

)

go

Set nocount on -- avoid x times (1 row(s) affected)

go

Insert into T_SSC ( TheXML )

values ( convert(xml, 'abc' ) );

go 10 -- repeat this batch 10 times

Set nocount off

go

Select *

from T_SSC ;

/* Prove XML columns start in row ! */

/* Check DMV for relocated rows */

Select * from sys.dm_db_index_physical_stats ( db_id('SSC_Test'), object_id('T_SSC'), null , null , null )

Update T_SSC

set TheXML = REPLICATE ('acb', 5000)

where IdNo = 8

Select * from sys.dm_db_index_physical_stats ( db_id('SSC_Test'), object_id('T_SSC'), null , null , null )

/* CLean up */

-- Drop table T_SSC ;

-- Drop database SSC_Test;

Edited : The xml content doesn't display at the website !

it should be : (I removed the &lt and the &gt )

in the insert statement = '&lt mydate &gt' + convert( char(23), getdate(), 121) + '&lt /mydate &gt'

In the update statement set TheXML = REPLICATE ('&lt a &gt def &lt /a &gt ', 5000)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me