Explicit CASTS in SQL genrated by SQLXML

I am using SQLXML to retrieve my xml data from SQL Server db. As it turns ou
t
the performance on some of the queries is really poor.
I traced the issue down to the fact that the select statement genrated by
SQLXML explicitly casts the columns, which precludes SQL Server from using
the indexes.
I found an article on MSDN with the recommendation to explicitly specify the
sql datatype in the xml schema, but even this does not stop SQLXML from doin
g
explicit casts. Is there any way to prevent these explicit casts?You can't actually get rid of all of them. There are actually 2 sets of
casts that occur:
1- Cast DB types to XML types
2- Cast XML types to XPath types
The "datatype" annotation allows us to skip the first one, but the second
still occurs. It's necessary to make sure we follow XPath rules and data
model.
Irwin
"Michael Feingold" <Michael Feingold@.discussions.microsoft.com> wrote in
message news:444CEDB0-D7A7-47FC-BF03-1844FD54F497@.microsoft.com...
>I am using SQLXML to retrieve my xml data from SQL Server db. As it turns
>out
> the performance on some of the queries is really poor.
> I traced the issue down to the fact that the select statement genrated by
> SQLXML explicitly casts the columns, which precludes SQL Server from using
> the indexes.
> I found an article on MSDN with the recommendation to explicitly specify
> the
> sql datatype in the xml schema, but even this does not stop SQLXML from
> doing
> explicit casts. Is there any way to prevent these explicit casts?|||Does it mean that there is really no way to get a reasonable performance fro
m
SQLXML queries?
Here is what I am doing:
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:element name="document" sql:relation="USERS">
<xs:complexType>
<xs:attribute name="docID" type="xs:int" sql:field="user_id"
sql:datatype="int"/>
</xs:complexType>
</xs:element>
</xs:schema>
The xpath looks as follows:
/document[@.docID=<some value>]
Very simple and basic. But because of the casts even with this simple and
basic query indexes are ignored by SQL Server and performance I am getting i
s
unacceptable.
If there is no way around this, SQLXML is of no use for anything but very
small applications. In my case it takes 5-7 sec to locate a record (by ID) i
n
a table of 5,000,000 records.
I just can not believe this is true! or is it?
The most frustrating part is that from looking at the query I can tell that
there is no need for this CAST, but I have no way to remove it. Or is there?
Or might be there is a way to adjust SQL Server so that it will use indexes
even with the casts?
Please help!
"Irwin Dolobowsky [MS]" wrote:

> You can't actually get rid of all of them. There are actually 2 sets of
> casts that occur:
> 1- Cast DB types to XML types
> 2- Cast XML types to XPath types
> The "datatype" annotation allows us to skip the first one, but the second
> still occurs. It's necessary to make sure we follow XPath rules and data
> model.
> Irwin
>
> "Michael Feingold" <Michael Feingold@.discussions.microsoft.com> wrote in
> message news:444CEDB0-D7A7-47FC-BF03-1844FD54F497@.microsoft.com...
>
>|||The problem depends on your datatypes. Since XPath has double semantics and
not integer semantics, you may still get the conversion. OTOH, if your SQL
type is equivalent to the XPath type, you should get the semantics without
cast (assuming you are using the sql:datatype annotation).
There may be some optimizations that Irwin's team could look at, such that
certain integer ops that would not behave different from their float ops
would not perform the cast. I don't know whether they do that though...
Best regards
Michael
"Michael Feingold" <MichaelFeingold@.discussions.microsoft.com> wrote in
message news:3BF03EF9-59BA-4801-9E49-B0EB0D9FE56B@.microsoft.com...
> Does it mean that there is really no way to get a reasonable performance
> from
> SQLXML queries?
> Here is what I am doing:
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xs:element name="document" sql:relation="USERS">
> <xs:complexType>
> <xs:attribute name="docID" type="xs:int" sql:field="user_id"
> sql:datatype="int"/>
> </xs:complexType>
> </xs:element>
> </xs:schema>
> The xpath looks as follows:
> /document[@.docID=<some value>]
> Very simple and basic. But because of the casts even with this simple and
> basic query indexes are ignored by SQL Server and performance I am getting
> is
> unacceptable.
> If there is no way around this, SQLXML is of no use for anything but very
> small applications. In my case it takes 5-7 sec to locate a record (by ID)
> in
> a table of 5,000,000 records.
> I just can not believe this is true! or is it?
> The most frustrating part is that from looking at the query I can tell
> that
> there is no need for this CAST, but I have no way to remove it. Or is
> there?
> Or might be there is a way to adjust SQL Server so that it will use
> indexes
> even with the casts?
> Please help!
> "Irwin Dolobowsky [MS]" wrote:
>|||Guys, please, do not leave me on the cold.
I understand and appreciate theoretical considerations - preserving
sematics and such. But this "small side effect" kills XPATH queries as a
means to do anything real - no matter precise semantics or not.
I always respected Microsoft for your readiness to adust the pure theory to
address practical needs. Here is a practical need: Retrieve a record in a
reasonable time.
Apparently XPATH genrated queries just cannot do this. Period.
XPATH genrated queries will never use indexes and therefore cannot be used
against tables of any reasonable size.
I just do not believe that nobody in the entire world is not using XPATH
queries for anything practical - there's got to be a workaround either on
SQLXML side or on SQL Server side. I built the entire application on SQLXML
and XPATH queries and it is scheduled to go live in 2 ws. It looks like
I'm gonna need a lot of midnight oil.|||As I mentioned in the original post there is an article on MSDN describing
this very problem KB813955
(http://support.microsoft.com/defaul...kb;en-us;813955) and a
workaround.
This is just that the workaround does not work
"Michael Feingold" wrote:

> I am using SQLXML to retrieve my xml data from SQL Server db. As it turns
out
> the performance on some of the queries is really poor.
> I traced the issue down to the fact that the select statement genrated by
> SQLXML explicitly casts the columns, which precludes SQL Server from using
> the indexes.
> I found an article on MSDN with the recommendation to explicitly specify t
he
> sql datatype in the xml schema, but even this does not stop SQLXML from do
ing
> explicit casts. Is there any way to prevent these explicit casts?|||I understand your frustration. Note however, that if you can change your
relational table's column type to a float() (and you adjust your
annotation), your numeric comparison may be done with the index.
Otherwise (since we cannot just rearchitect this component in two ws
time, sorry), the best thing is to take the generated FOR XML EXPLICIT query
and parameterize it via a stored proc (removing the offending casts). That
is the best short-term workaround I can give you, if you cannot change
types.
Best regards
Michael
"Michael Feingold" <MichaelFeingold@.discussions.microsoft.com> wrote in
message news:62115EED-6D6B-463E-ADE7-E279E246A3C4@.microsoft.com...
> Guys, please, do not leave me on the cold.
> I understand and appreciate theoretical considerations - preserving
> sematics and such. But this "small side effect" kills XPATH queries as a
> means to do anything real - no matter precise semantics or not.
> I always respected Microsoft for your readiness to adust the pure theory
> to
> address practical needs. Here is a practical need: Retrieve a record in a
> reasonable time.
> Apparently XPATH genrated queries just cannot do this. Period.
> XPATH genrated queries will never use indexes and therefore cannot be used
> against tables of any reasonable size.
> I just do not believe that nobody in the entire world is not using XPATH
> queries for anything practical - there's got to be a workaround either on
> SQLXML side or on SQL Server side. I built the entire application on
> SQLXML
> and XPATH queries and it is scheduled to go live in 2 ws. It looks like
> I'm gonna need a lot of midnight oil.|||Michael,
can you try putting the number() function around your parameter? So the
XPath should appear as:
document[@.docID=number(<some value> )]
"Michael Feingold" <MichaelFeingold@.discussions.microsoft.com> wrote in
message news:3BF03EF9-59BA-4801-9E49-B0EB0D9FE56B@.microsoft.com...
> Does it mean that there is really no way to get a reasonable performance
> from
> SQLXML queries?
> Here is what I am doing:
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xs:element name="document" sql:relation="USERS">
> <xs:complexType>
> <xs:attribute name="docID" type="xs:int" sql:field="user_id"
> sql:datatype="int"/>
> </xs:complexType>
> </xs:element>
> </xs:schema>
> The xpath looks as follows:
> /document[@.docID=<some value>]
> Very simple and basic. But because of the casts even with this simple and
> basic query indexes are ignored by SQL Server and performance I am getting
> is
> unacceptable.
> If there is no way around this, SQLXML is of no use for anything but very
> small applications. In my case it takes 5-7 sec to locate a record (by ID)
> in
> a table of 5,000,000 records.
> I just can not believe this is true! or is it?
> The most frustrating part is that from looking at the query I can tell
> that
> there is no need for this CAST, but I have no way to remove it. Or is
> there?
> Or might be there is a way to adjust SQL Server so that it will use
> indexes
> even with the casts?
> Please help!
> "Irwin Dolobowsky [MS]" wrote:
>