MS SQL XML - Typed XML slows down query?!

Author

Typed XML slows down query?!

Martin Szugat

2006-01-04, 7:23 am

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/d...l/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

Bob Beauchemin

2006-01-05, 1:25 pm

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@discussions.microsoft.com> wrote in message
news:5A4E1F63-55EC-4F12-8F54- 903108126985@microso
ft.com...
>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/d...l/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

Martin Szugat

2006-01-05, 8:25 pm

Hi Bob!

Thanks for your reply!

I tried other XQueries like this one:

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

Same problem, but if change it to

for $keyword in /html/body//b
where contains(string($key
word), 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@discussions.microsoft.com> wrote in message
> news:5A4E1F63-55EC-4F12-8F54- 903108126985@microso
ft.com...
>
>
>

Michael Rys [MSFT]

2006-01-12, 8:24 pm

This may be a bug.

Martin, have you filed it yet?

Thanks
Michael

"Martin Szugat" < MartinSzugat@discuss
ions.microsoft.com> wrote in message
news:A280327D-0FF6-4E6E-8678- 92CA65BAFD87@microso
ft.com...[color=darkred]
> Hi Bob!
>
> Thanks for your reply!
>
> I tried other XQueries like this one:
>
> for $keyword in //b
> where contains(string($key
word), sql:variable("@keyword"))
> return $keyword
>
> Same problem, but if change it to
>
> for $keyword in /html/body//b
> where contains(string($key
word), sql:variable("@keyword"))
> return $keyword
>
> it's still slow, but it returns a result within time.
>
> Best regards
>
> Martin
>
> "Bob Beauchemin" wrote:
>

Martin Szugat

2006-01-12, 8:24 pm

Hi Michael,

Now I did:

http://lab.msdn.microsoft.com/Produ...DBK43857

Best regards

Martin

PS: Hope to see you this year at TechEd Europe again. I think it's Berlin
this time, during the WM.

"Michael Rys [MSFT]" wrote:

> This may be a bug.
>
> Martin, have you filed it yet?
>
> Thanks
> Michael
>
> "Martin Szugat" < MartinSzugat@discuss
ions.microsoft.com> wrote in message
> news:A280327D-0FF6-4E6E-8678- 92CA65BAFD87@microso
ft.com...
>
>
>

Michael Rys [MSFT]

2006-01-12, 8:24 pm

Thanks.

Michael

"Martin Szugat" < MartinSzugat@discuss
ions.microsoft.com> wrote in message
news:8B0C2C34-5BC2-4766-91D0- 2E40905D3DBD@microso
ft.com...[color=darkred]
> Hi Michael,
>
> Now I did:
>
> http://lab.msdn.microsoft.com/Produ...DBK43857

>
> Best regards
>
> Martin
>
> PS: Hope to see you this year at TechEd Europe again. I think it's Berlin
> this time, during the WM.
>
> "Michael Rys [MSFT]" wrote:
>

Michael Rys [MSFT]

2006-01-18, 1:23 pm

Just a quick update: We have looked into the repro and it appears that the
performance issue is during compilation of the query and not execution. This
means that once the query has been compiled, you should see much better
performance during subsequent executions as long as the query does not get
recompiled.

It has to do with the size and complexity of the schema impacting the static
type analysis.

We have encountered this issue previously and have implemented a fix for it
for the next service pack....

Best regards
Michael

"Martin Szugat" < MartinSzugat@discuss
ions.microsoft.com> wrote in message
news:8B0C2C34-5BC2-4766-91D0- 2E40905D3DBD@microso
ft.com...[color=darkred]
> Hi Michael,
>
> Now I did:
>
> http://lab.msdn.microsoft.com/Produ...DBK43857

>
> Best regards
>
> Martin
>
> PS: Hope to see you this year at TechEd Europe again. I think it's Berlin
> this time, during the WM.
>
> "Michael Rys [MSFT]" wrote:
>

Sponsored Links