Index xml in variable

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi, more newbie questions. When I first read over the SQLServer XML
documentation, I saw you could index XML. I thought this would be great if
you had a big single XML value that you were working on. I have a 2.4mb XML
value now, and yeah, it's running slow, several minutes to do a cross apply
returning about a thousand rows. So I thought, great time to index!

Only now it seems the indexing is for XML in tables, not in a variable.

Does it pay to write a single (2.4mb) value to a table with an index?

Basically I'm shredding the XML into tables, joining the top level elements
to lower level elements, pretty common stuff.

Funny thing is, on a very similar XML structure with the same kind of
processing we had concerns about scalability, so a few weeks ago I'd created
a 2.3mb test data, and it ran in about fifteen seconds. I'm really not sure
why this one is different and running about 10x slower. FWIW, this is on a
smallish workstation (laptop), and what I see is the CPU pegged while it
runs, little or no IO.

We don't have a lot of values this big, but we have some, and might have a
few up to about twice this size.

Thanks.

Josh

.

Relevant Pages

  • Re: Index xml in variable
    ... documentation, I saw you could index XML. ... great if you had a big single XML value that you were working on. ... have a 2.4mb XML value now, and yeah, it's running slow, several ...
    (microsoft.public.sqlserver.xml)
  • Re: Return different row sets using for xml?
    ... queries return the data to BizTalk as a single XML document. ... customers for xml explicit; ...
    (microsoft.public.sqlserver.xml)
  • Re: Index xml in variable
    ... apply" in the context of XML processing, that I'm going to post in another ... value now, and yeah, it's running slow, several minutes to do a cross ... Only now it seems the indexing is for XML in tables, ... to lower level elements, pretty common stuff. ...
    (microsoft.public.sqlserver.xml)
  • RE: Multiple DataSets in one XML file
    ... Unless you're taking some special care of XML structure, ... it's up to you to find the right data set start and invoke ReadXml ... Multiple DataSets in one XML file ... > It's very easy to DataSet.WriteXmlmultiple dataset to a single xml ...
    (microsoft.public.dotnet.framework.compactframework)