Post reply

How to manage huge table that contains xml

  • 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...?

  • April 14, 2009 at 3:41 am

    #977095

    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"

  • April 14, 2009 at 1:02 pm

    #977555

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


    * Noel

  • April 14, 2009 at 4:39 pm

    #977748

    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.

  • April 14, 2009 at 9:59 pm

    #977829

    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

  • April 15, 2009 at 12:06 am

    #977858

    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"

  • April 15, 2009 at 2:09 am

    #977891

    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

  • April 15, 2009 at 2:21 am

    #977897

    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"

  • April 15, 2009 at 2:35 am

    #977905

    Thank you guys.....

  • April 15, 2009 at 2:38 am

    #977906

    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

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

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