MS SQL XML - Poor performance of XPath queries

Author

Poor performance of XPath queries

Philip Lee

2005-07-06, 9:23 am

Hi,

we are using SqlXml inside a web service. The web service methods accept an
XPath query and return an string containing the xml result. The tables
being queried can contain 100,000+ rows and this leads to terrible
performance, since as far as I know there's no way of limiting the number of
rows returned by the underlying SQL query generated by SqlXml. We are
artificially limiting the number of rows returned as follows:

public string ExecuteQuery( string xPathQuery, int maxRows, string schema )
{
SqlXmlCommand cmd = new SqlXmlCommand( connection string )
cmd.RootTag = _rootTag;
cmd.CommandText = xPathQuery;
cmd.SchemaPath = schema;

using( Stream stream = cmd.ExecuteStream() )
{
XmlTextReader reader = new XmlTextReader(stream
);

StringBuilder build = new StringBuilder(10000)
;

try
{
builder.Append(_rootBegin);
int count = 0;
bool reading = true;
reader.MoveToContent();
while( reading && count < maxRows )
{
if (reader.Depth == 1 && reader.NodeType ==
XmlNodeType.Element)
{
count++;
builder.Append(reader.ReadOuterXml());
}
else
{
reading = reader.Read();
}
}
}
finally
{
reader.Close();
}

builder.Append(_rootEnd);
return builder.ToString();
}
}

When querying a table with 100000 rows for the top 100 rows, this takes
approx 10 seconds! Using standard ADO.NET takes 100ms.

What I would really like to be able to do is inject "TOP 'n'" into the SQL
generated by SqlXml. Are there any hooks to allow access to or modification
of the SQL?

Or alternatively is SQL2005 going to solve this problem?

Any information appreciated.

Phil Lee

Michael Rys [MSFT]

2005-07-13, 3:23 am

Can you add a filter that is backed by an index?

If not, you may want to send FOR XML queries instead. There you have more
control.

Best regards
Michael

"Philip Lee" < microsoftnp@*pjlcons
ultants*co*uk*> wrote in message
news:OOWsw5igFHA.2268@TK2MSFTNGP15.phx.gbl...
> Hi,
>
> we are using SqlXml inside a web service. The web service methods accept
> an XPath query and return an string containing the xml result. The tables
> being queried can contain 100,000+ rows and this leads to terrible
> performance, since as far as I know there's no way of limiting the number
> of rows returned by the underlying SQL query generated by SqlXml. We are
> artificially limiting the number of rows returned as follows:
>
> public string ExecuteQuery( string xPathQuery, int maxRows, string
> schema )
> {
> SqlXmlCommand cmd = new SqlXmlCommand( connection string )
> cmd.RootTag = _rootTag;
> cmd.CommandText = xPathQuery;
> cmd.SchemaPath = schema;
>
> using( Stream stream = cmd.ExecuteStream() )
> {
> XmlTextReader reader = new XmlTextReader(stream
);
>
> StringBuilder build = new StringBuilder(10000)
;
>
> try
> {
> builder.Append(_rootBegin);
> int count = 0;
> bool reading = true;
> reader.MoveToContent();
> while( reading && count < maxRows )
> {
> if (reader.Depth == 1 && reader.NodeType ==
> XmlNodeType.Element)
> {
> count++;
> builder.Append(reader.ReadOuterXml());
> }
> else
> {
> reading = reader.Read();
> }
> }
> }
> finally
> {
> reader.Close();
> }
>
> builder.Append(_rootEnd);
> return builder.ToString();
> }
> }
>
> When querying a table with 100000 rows for the top 100 rows, this takes
> approx 10 seconds! Using standard ADO.NET takes 100ms.
>
> What I would really like to be able to do is inject "TOP 'n'" into the SQL
> generated by SqlXml. Are there any hooks to allow access to or
> modification of the SQL?
>
> Or alternatively is SQL2005 going to solve this problem?
>
> Any information appreciated.
>
> Phil Lee
>
>
>

Bertan ARI [MSFT]

2005-07-13, 1:23 pm

If you are familiar with For XML Explicit, you may use FOR XML explicit
query. To start with, you can use the profiler to see the generated FOR XML
explicit query and modify the queries to get top rows.

--
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.

"Philip Lee" < microsoftnp@*pjlcons
ultants*co*uk*> wrote in message
news:OOWsw5igFHA.2268@TK2MSFTNGP15.phx.gbl...
> Hi,
>
> we are using SqlXml inside a web service. The web service methods accept
> an XPath query and return an string containing the xml result. The tables
> being queried can contain 100,000+ rows and this leads to terrible
> performance, since as far as I know there's no way of limiting the number
> of rows returned by the underlying SQL query generated by SqlXml. We are
> artificially limiting the number of rows returned as follows:
>
> public string ExecuteQuery( string xPathQuery, int maxRows, string
> schema )
> {
> SqlXmlCommand cmd = new SqlXmlCommand( connection string )
> cmd.RootTag = _rootTag;
> cmd.CommandText = xPathQuery;
> cmd.SchemaPath = schema;
>
> using( Stream stream = cmd.ExecuteStream() )
> {
> XmlTextReader reader = new XmlTextReader(stream
);
>
> StringBuilder build = new StringBuilder(10000)
;
>
> try
> {
> builder.Append(_rootBegin);
> int count = 0;
> bool reading = true;
> reader.MoveToContent();
> while( reading && count < maxRows )
> {
> if (reader.Depth == 1 && reader.NodeType ==
> XmlNodeType.Element)
> {
> count++;
> builder.Append(reader.ReadOuterXml());
> }
> else
> {
> reading = reader.Read();
> }
> }
> }
> finally
> {
> reader.Close();
> }
>
> builder.Append(_rootEnd);
> return builder.ToString();
> }
> }
>
> When querying a table with 100000 rows for the top 100 rows, this takes
> approx 10 seconds! Using standard ADO.NET takes 100ms.
>
> What I would really like to be able to do is inject "TOP 'n'" into the SQL
> generated by SqlXml. Are there any hooks to allow access to or
> modification of the SQL?
>
> Or alternatively is SQL2005 going to solve this problem?
>
> Any information appreciated.
>
> Phil Lee
>
>
>

Sponsored Links