Re: openxml question


"JRStern" <JRStern@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EB772BD8-9F8F-4791-A8AF-DB8A72BDB27F@xxxxxxxxxxxxxxxx


"Mike C#" wrote:

Indexing with at least a primary XML index pre-shreds the data, and
provides
a significant performance improvement in shredding--but of course you
can't
use an XML index on a variable. The secondary XML index doesn't seem to
make as much difference for simple shreds of fairly simple XML documents
though. Secondary XML indexes may improve performance for complex
documents and complex shredding operations, but I haven't tested it with
any
complicated paths or XML documents yet.

I tested it with my larger documents, the impact was minimal, and was both
plus or minus over different cases, about as close to net zero as you
could
come.

Anyway, when I'm shredding a 2mb, 2000 row document in two seconds, how
much
faster can it go? And I'm guessing most of that time is writing the table
vars, not the shredding itself anyway.

You probably can't get much better than that with that size of file. Even
if you loaded the data into an XML column in a table with a primary index it
would still need to shred and update the index, so it would probably end up
being a wash. You might be surprised, the shredding is a pretty intense
operation - check out the query plan, you'll probably see a pretty hefty
cost on a Table Valued Function - [XML Reader] in there.


.

Relevant Pages

  • Re: or operator
    ... Having the execution plan and DDL will definitely help understnad the issue. ... Optimizations for the XML Data Type in SQL Server 2005" ... Primary XML index and the Value XML index (Note that I ... CREATE TABLE itis ...
    (microsoft.public.sqlserver.xml)
  • Re: or operator
    ... Having the execution plan and DDL will definitely help understnad the issue. ... Optimizations for the XML Data Type in SQL Server 2005" ... Primary XML index and the Value XML index (Note that I ... CREATE TABLE itis ...
    (microsoft.public.sqlserver.xml)
  • Re: use XML to store custom metadata
    ... I made a simple table just to hold some xml documents. ... CREATE PRIMARY XML INDEX RootNamesXML_idx ON RootNames ... I filled the RootNames table with over 10,000 rows. ... Then I tried a queries equivalent to the xquery on the RootNames table: ...
    (microsoft.public.sqlserver.xml)
  • Re: use XML to store custom metadata
    ... I made a simple table just to hold some xml documents. ... CREATE PRIMARY XML INDEX RootNamesXML_idx ON RootNames ... I filled the RootNames table with over 10,000 rows. ... Then I tried a queries equivalent to the xquery on the RootNames table: ...
    (microsoft.public.sqlserver.xml)
  • Re: Typed XML slows down query?!
    ... > Hi Martin, ... >>I have a performance problem with typed XML and a simple query. ... >> CREATE PRIMARY XML INDEX idx_article ...
    (microsoft.public.sqlserver.xml)
Loading