Re: Typed XML slows down query?!

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Bob!

Thanks for your reply!

I tried other XQueries like this one:

for $keyword in //b
where contains(string($keyword), sql:variable("@keyword"))
return $keyword

Same problem, but if change it to

for $keyword in /html/body//b
where contains(string($keyword), sql:variable("@keyword"))
return $keyword

it's still slow, but it returns a result within time.

Best regards

Martin

"Bob Beauchemin" wrote:

> Hi Martin,
>
> I can reproduce your symptoms with a single extremely simple XHTML document.
> It appears to have something to do with the XQuery in the exist method of
> the where clause (specifically the //title part, changing to
> /html/head/title works). Even if I simplify the query, if I have //title, I
> can't even get an estimated query plan. I'd file this as a specific bug
> rather than a general behavior. Even without any indexes I get the same
> symptoms if I'm using the schema.
>
> Cheers,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
>
> "Martin Szugat" <Martin Szugat@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:5A4E1F63-55EC-4F12-8F54-903108126985@xxxxxxxxxxxxxxxx
> >I have a performance problem with typed XML and a simple query. I used the
> > XHTML schema from Visual Studio 2005 (\xml\schemas\xhtml.xsd) to create a
> > schema collection called CmsSchemas within SQL Server 2005. Next I created
> > the table:
> >
> > CREATE TABLE articles(
> > id UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY DEFAULT NEWID(),
> > article XML(DOCUMENT CmsSchemas) NOT NULL,
> > date DATETIME NOT NULL DEFAULT GETDATE()
> > )
> >
> > Finally, I created two indices:
> >
> > CREATE PRIMARY XML INDEX idx_article
> > ON articles(article)
> >
> > CREATE XML INDEX idx_article_value
> > ON articles(article)
> > USING XML INDEX idx_article
> > FOR VALUE
> >
> > I filled the table with two simple and small XHTML documents and then I
> > tried the following query:
> >
> > SELECT article.value('declare default element namespace
> > "http://www.w3.org/1999/xhtml";;(/html/head/title)[1]', 'nvarchar(max)') AS
> > title,
> > article.query('declare default element namespace
> > "http://www.w3.org/1999/xhtml";/html/body/child::node()') AS content
> > FROM articles
> > WHERE (article.exist('
> > declare default element namespace
> > "http://www.w3.org/1999/xhtml";//title[contains(.,"blabla")]') = 1)
> >
> > Interestingly, this query resulted in a timeout exception, but when I
> > changed the column article into an untyped xml column, the query
> > immediately
> > returns a result set. However, e.g. the article on
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5xml.asp
> > says that "Typed XML yields better performance with value-based queries
> > (that
> > is, where the search value is more selective than, say, the path in which
> > the
> > value occurs) by avoiding run-time value conversions so that index seeks
> > into
> > the XML indexes become possible."
> >
> > So I am a little bit confused. Using typed XML the query performance
> > should
> > be increased, but in fact it is decreased (dramatically).
> >
> > Any explanations or even suggestions how to solve this problem? Thanks in
> > advance for any helpful reply!
> >
> > Best regards
> >
> > Martin
>
>
>
.

Relevant Pages

  • Re: Typed XML slows down query?!
    ... I can reproduce your symptoms with a single extremely simple XHTML document. ... >I have a performance problem with typed XML and a simple query. ... > CREATE PRIMARY XML INDEX idx_article ...
    (microsoft.public.sqlserver.xml)
  • Re: DISCOVER_XML_METADATA
    ... Small XML is not problem. ... and then run following for each database: ... unprocessed cubes and it doesn't show processing status information ... A DBSCHEMA_CATALOGS query will get you a list of all the databases ...
    (microsoft.public.sqlserver.olap)
  • RE: Query producing XML appears to be cached
    ... By default the XMLDataSource control always caches its data, ... I have an app that executed a sql server query that produces an XML ...
    (microsoft.public.dotnet.framework.aspnet)
  • setting dataset datarelation from database
    ... I want to set my dataset's datarelations based on the relationships ... I had a query I ... switched to using 'for xml explicit, ... If I run the query in sql query analyzer, I see the schema ...
    (microsoft.public.dotnet.framework.adonet)
  • Query producing XML appears to be cached
    ... I have an app that executed a sql server query that produces an XML ... actually executing. ...
    (microsoft.public.dotnet.framework.aspnet)